- Can anyone who has been given authorization to the application decrypt data?
Yes, TDE is designed to provide customers the ability to transparently apply encryption within the database without impacting existing applications. Returning data in encrypted format would break most existing applications. TDE provides the benefit of encryption without the overhead associated with traditional database encryption solutions that typically require database triggers and views. Oracle Database Vault can be used to protect application data from the DBA and other powerful users as well as implementing robust controls on access to the database and application.
- What is the overhead associated with TDE?
TDE tablespace encryption (available with Oracle Database 11g) has no additional storage overhead; according to internal benchmarks and feedback from successful customers, the performance overhead is in the single digits.
Overhead associated with TDE column encryption falls into two areas: storage and performance.
- The performance overhead associated with encrypting or decrypting a common attribute such as a credit card number column with TDE column encryption is estimated to be around 5%. When an index is built on an encrypted column, the index will be built using the cipher text. If a TDE encrypted column is indexed and referenced in a SQL statement, Oracle will transparently encrypt the value used in the SQL statement with the table key and perform an index lookup using the cipher text. In real-world scenarios, only very few columns are encryption candidates, sometimes only 0.3% of all application columns; if all indexes are re-build the way they were originally designed before TDE column encryption was applied, customers reported a negligible performance impact. It is recommended that customers, who plan to use TDE column encryption, upgrade to Oracle Database 10gR2 10.2.0.4 (and apply patch 7639262) or Oracle Database 11gR1 11.1.0.7 (and apply patch 8421211), since both include changes that reduce the performance overhead.
- Storage overhead associated with TDE column encryption can be significant due to an additional 20 byte integrity check associated with each encrypted value. In addition, TDE column encryption will pad out encrypted values to the next 16 bytes (for AES; with 3DES168, to the next 8 bytes), so if a value required 9 bytes of storage, encrypting this value would require an additional 7 bytes of storage. Finally, if 'SALT' is specified on the encrypted value, an additional 16 bytes is required. In summary, encrypting a single column in a table will require between 1 and 52 bytes of additional storage per row. This number is important for storage planning, but the DBAs or developers don't have to expand the columns for TDE column encryption; the expansion is done transparently by TDE when a column is marked 'encrypted'.
Users can limit the amount of additional storage by choosing the 'no salt' option, and/or the new 'nomac' option (available from 10.2.0.4 and 11.1.0.7), which eliminates the additional CPU cycles and disk space needed for calculating and storing the 20 byte hash value for each encrypted field.
- What are the encryption algorithms that can be used with TDE?
TDE supports AES256, AES192 (default for TDE column encryption), AES128 (default for TDE tablespace encryption), and 3DES168.
- Is it possible to use 3rd party encryptions algorithms in place of the ones provided by TDE?
No, it is not possible to plug-in other encryption algorithms. Oracle provides encryption algorithms that are broadly accepted, and will add new algorithms as they become available.
- Can I use TDE column encryption on columns used in foreign key constraints?
TDE doesn't support encrypting columns with foreign key constraints. This is due to the fact that individual tables have their own unique encryption key. The following query lists all occurrences of RI (Referential Integrity) constraints in your database:
SQL> select A.owner, A.table_name, A.column_name, A.constraint_name
from dba_cons_columns A, dba_constraints B
where A.table_name = B.table_name and B.constraint_type = 'R';
- Can columns that are used for joins be encrypted?
Yes. Joining tables is transparent to users and applications, even if the columns for the join condition are encrypted.
- Can indexed columns be encrypted?
TDE tablespace encryption supports all indexes transparently.
For TDE column encryption, the index needs to be a normal B-tree index, used for equality searches. In case of a composite, function-based index, the encrypted column cannot be the one that was used for the function. When encrypting a column with an existing index, it is recommended to first extract the index definition with dbms_metadata.get_ddl, then drop the index, encrypt the column with the 'no salt' option, and re-build the index.
- What data types and data lengths does TDE support?
For TDE tablespace encryption, there are no limitations in terms of supported data types; the following data types can be encrypted using TDE column encryption:
varchar2 (< 3933 characters) nvarchar2 (< 1967 characters)
char (< 1933 characters) nchar (< 967 characters)
number raw
binary_float binary_double
timestamp date
SecureFile (11gR1 and later)
- Does the data stay encrypted on the network?
Data encrypted with TDE is decrypted when it is read back from database file. Thus if this data goes on the network, it is clear-text data. However, the data can be encrypted using Oracle's network encryption solution, which is included with TDE in the Oracle Advanced Security option. Oracle's network encryption solution encrypts all data traveling to and from a database over SQL*Net.
- Does the database memory (SGA) contain clear-text or encrypted data?
With TDE column encryption, encrypted data remains encrypted inside the SGA, but with TDE tablespace encryption, data is already decrypted in the SGA, which provides 100% transparency.
- How do I know which data to encrypt?
If you have to comply to the PCI-DSS standard, then credit card numbers (a.k.a. Primary Account Number, or PAN) need to be stored encrypted.
The need to comply to the almost ubiquitous Breach Notification Laws (for example CA SB 1386, and similar laws in 43+ more US states), adds first name, last name, driver license number and other PII to your list. In early 2008, CA AB 1298 added medical and health insurance information to PII data.
Additionally, your industry specific privacy and security standards may require encryption of certain stored assets, plus your own core business assets (such as research results in the pharmaceutical industry, results of oil field exploration, financial contracts, or the personal details of informants in law enforcement) may be worth encrypting to safeguard this information on the storage medium. In the health care industry, the privacy of patient data, health records and X-ray images is of the highest importance. Most X-ray images are stored following the DICOM standard, which intentionally includes PII information into the image meta data, making image and patient data readily available to an intruder if not properly protected through encryption.
- Where is the data that needs to be encrypted?
This is the most difficult task ahead of a security team or team of DBAs when using TDE column encryption:
If you run applications that were developed in-house, chances are you can locate tables with sensitive information by talking to your developers.
It is more difficult when you run packaged software applications. Since privacy and security requirements are different for each of the deployments of these applications, vendors themselves cannot readily determine what to encrypt. If PCI compliance is the goal, and the column names of the application tables are named similar to 'CREDIT_CARD' or 'ACCOUNT_NUMBER', they are easy to find using Oracle's rich metadata repository.
More complex is the hunt for sensitive data when column names are not descriptive about their content; the only method of finding sensitive content is the search for patterns: Social Security Numbers always look like 'aaa-bb-cccc', but Credit Card Numbers are less consistent: They have 13 or 16 digits, and are not always grouped by 4 digits.
If you need to encrypt columns that have characteristics which are not supported by TDE column encryption (in terms of indexes, data types, or foreign keys), or if it is not possible to locate columns that store sensitive data in application tables, TDE tablespace encryption is your best choice.
- With Oracle Database 11g, shall I use TDE column encryption or TDE tablespace encryption?
Use TDE tablespace encryption if any of the following is true:
- You cannot find all columns with sensitive content
- Data type and/or data length of sensitive column is not supported by TDE column encryption
- Sensitive column is used as foreign key
- Applications perform range scans over indexed, encrypted columns
- You need index types other than B-tree over encrypted columns
- How is TDE different from the encryption toolkit Oracle already provides?
Oracle introduced an encryption package ('dbms_obfuscation_toolkit') with Oracle8i. In Oracle 10g Release 1, the new 'dbms_crypto' package was introduced. These APIs can be used to manually encrypt data within the database. However, the application must manage the encryption keys and perform required encryption and decryption operations by calling the API.
As opposed to dbms_obfuscation_toolkit and dbms_crypto, both TDE column encryption (from 10gR2) and TDE tablespace encryption (from 11gR1) don't require changes to the application, are transparent to the end users, and provide automated key management.
- How is TDE licensed?
TDE is part of the Oracle Advanced Security Option, which also includes Network Encryption and Strong Authentication. It is available for the Oracle Enterprise Edition.
- What is the Oracle Wallet?
A wallet is an encrypted container that is used to store authentication and signing credentials, including passwords, the TDE master key, PKI private keys, certificates, and trusted certificates needed by SSL. With TDE, wallets are used on the server to protect the TDE master key. With the exception of Diffie-Hellman, Oracle requires entities that communicate over SSL to have a wallet containing an X.509 version 3 certificate, private key, and list of trusted certificates.
Oracle provides two different types of wallets: encryption wallet and auto-open wallet. The encryption wallet (filename 'ewallet.p12') is the one recommended for TDE. It needs to be opened manually after database startup and prior to TDE encrypted data being accessed. If the Wallet is not opened, the database will return an error when TDE protected data is queried. The auto-open wallet (filename 'cwallet.sso') opens automatically when a database is started; hence it can be used for unattended Data Guard (10gR2: physical standby only; 11gR1 and later: physical and logical standby) environments where encrypted columns are shipped to secondary sites. Do not delete the encryption wallet after creating an auto-open wallet, since otherwise master encryption key re-key operations fail.
Oracle Database 11gR2 introduced the 'local auto-open' wallet, which only opens automatically on the server it was created on.
- How is the TDE wallet protected?
Access to the wallet should be limited to the 'oracle' user, using proper directory and file level permissions. In addition, the password (that encrypts the wallet) should contain a minimum of 8 alphanumeric characters. Wallet passwords can be changed using Oracle Wallet Manager, or the 'orapki' utility. Changing the wallet password doesn't effect the TDE master key (they are independent). Even though the 'root' user has access to the wallet file, if she does not know the wallet password, she has no access to the master encryption key.
- Can I use Oracle Wallet Manager (OWM) to create the encryption wallet and master key for TDE?
If you create an encryption wallet with Oracle Wallet Manager, it does not contain the master key required by TDE. Only the SQL command:
SQL> alter system set encryption key identified by "wallet_password"
creates a wallet (if it doesn't already exist in the location specified in the local sqlnet.ora file) and adds the TDE master key to it.
In Oracle 11gR1, TDE and other security features have been migrated to Enterprise Manager Database Control, thus enabling the wallet and the master key to be generated using the Web-based GUI of Enterprise Manager.
Also new in 11gR1, the master encryption key for TDE column encryption can be created, re-keyed (rotated) and stored in an HSM device such that the master key never leaves the HSM device in clear text. Starting with Oracle Database 11gR1 11.1.0.7, the master key for TDE tablespace encryption can also be created and stored in an HSM device, but re-keying (or migrating the TDE tablespace encryption master key from wallet to HSM) is not supported.
New in Oracle 11gR2 is the 'unified master key', which is used for both TDE column and TDE tablespace encryption; this key can be created, stored and re-keyed (rotated) in the Oracle Wallet and an HSM.
The syntax to create a new key in an HSM device is:
SQL> alter system set encryption key identified by "HSM_authentication_string"
where 'HSM_authentication_string' can be 'user_ID:HSM_password', 'HSM_password' or a PIN, depending on the HSM vendor.
- Can I change the wallet password?
Yes, the wallet password can be changed with Oracle Wallet Manager (OWM). Changing the wallet password does not change the encryption master key — they are independent. In Oracle 11gR1 11.1.0.7, orapki has been enhanced to allow wallet password changes from the command line:
$ orapki wallet change_pwd -wallet <wallet_location>
- How do I create an auto-open wallet?
A password-protected, encrypted wallet for the TDE master key might not be the right solution when database availability needs to be maintained without human intervention ('lights-out' operation); an auto-open wallet does not require a wallet password after a database came up, so encrypted data is available to authorized users and applications.
An auto-open wallet ('cwallet.sso') needs to be created from an existing encryption wallet ('ewallet.p12'), so that the master key can be transferred to the new auto-open wallet.
You can either open the encryption wallet in Oracle Wallet Manager (OWM), check the 'Auto Login' check box, then select 'Save' to write the auto-open wallet to disk, or, using the command-line tool 'orapki':
$ orapki wallet create -wallet <wallet_location> -auto_login
The syntax to create a local auto-open wallet is:
$ orapki wallet create -wallet <wallet_location> -auto_login_local
In both cases (Oracle Wallet Manager and 'orapki') the user will be prompted for the wallet password. Keep the encryption wallet; it is required for master key re-key operations, and potentially contains a list of retired master keys.
- How do I prevent the Oracle TDE wallet from being backed up on the same tape as the RMAN database backups when using Oracle Secure Backup?
RMAN only adds database files, redo-logs etc. to the backup file, and thus there is no risk of the encryption wallet or the auto-open wallet becoming part of a database backup. Oracle Secure Backup (OSB) uses datasets to define which operating system files to add to a backup. OSB automatically excludes auto-open wallets ('cwallet.sso'). Encryption wallets ('ewallet.p12') are NOT automatically excluded; you need to use the exclude dataset statement to specify what files to skip during a backup:
exclude name *.p12
- Best practices for wallet backup
Backup the Oracle wallet right after creating it, and each time it's content changes, for example due to a master key re-key operation. Always store the wallet (encrypted or (local) auto-open) away from your database backups.
- Which packaged applications are certified with Transparent Data Encryption?
Table A: Applications certified with Transparent Data Encryption
TDE tablespace encryption
(Oracle Database 11.1.0.7) |
TDE column encryption
(Oracle Database 10.2.0.4 or 11.1.0.7) |
Oracle E-Business Suite: (Datasheet)
11.5.10 (Oracle metalink note 828223.1) and
12.0.4 (Oracle metalink note 828229.1) |
Oracle E-Business Suite 11.5.9 (Oracle metalink note 403294.1) |
| Oracle PeopleSoft Enterprise 8.48+ (Datasheet) |
Oracle PeopleSoft Enterprise 8.46+ (Datasheet) |
| Oracle Siebel CRM 8.0+ (Datasheet) |
Oracle Siebel CRM 7.7+ |
| Oracle JD Edwards EnterpriseOne (Datasheet) |
SAP 6.40 and 7.00 (SAP note 974876) |
- Can I encrypt data stored in Exadata storage cells?
Yes, with both Oracle 11gR1 and Oracle 11gR2, encryption of content stored in Exadata Storage is supported. In Oracle 11gR1 however, the pre-selection of the content can no longer be performed in the cells, because the content is encrypted and the master encryption key resides in database memory.
In Oracle 11gR2, the master encryption key is available to all Exadata storage cells, so that content can be first decrypted and then, pre-selection and column filtering takes place. Customers no longer have to choose between extreme performance and security.
- What is Oracle Secure Backup (OSB)?
Oracle Secure Backup provides an optimized, highly efficient tape backup solution for the Oracle Database. OSB can store data on tape in encrypted form, providing protection against theft of backup tapes.
- How does Oracle RMAN treat encrypted data?
Table B: Oracle Transparent Data Encryption and Oracle RMAN
| Application data |
Backup with compression |
Backup with encryption |
Backup with compression and encryption |
| Not encrypted |
Data compressed |
Data encrypted |
Data compressed first, then encrypted |
| Encrypted with TDE column encryption |
Data compressed; encrypted columns are treated as if they were not encrypted |
Data encrypted; double encryption of encrypted columns |
Data compressed first, then encrypted; encrypted columns are treated as if they were not encrypted; double encryption of encrypted columns |
| Encrypted with TDE tablespace encryption |
Encrypted blocks are decrypted, compressed, and re-encrypted; clear text blocks are compressed and encrypted (after compression, encrypted and clear text data cannot be distinguished) |
Encrypted blocks are passed through to the backup unchanged; clear text blocks are encrypted |
Encrypted blocks are decrypted, compressed, and re-encrypted; clear text blocks are compressed and encrypted |
- Can I encrypt backups sent to disk using Oracle Secure Backup?
No, however, Oracle RMAN can be used in conjunction with Oracle Advanced Security to encrypt database backups sent to disk. This requires a license of the Oracle Advanced Security Option.
- Do transportable tablespaces work with TDE tablespace encryption?
Yes, but it requires that the wallet containing the master key is copied to the secondary database. If the tablespace is moved and the master key is not available, the secondary database will return an error when the data in the tablespace is accessed.
- Does compression work with TDE?
Customers using TDE tablespace encryption get the full benefit of compression because compression is applied before the data blocks are encrypted. Customers using TDE column encryption will get the full benefit of compression only on table columns that are not encrypted. Individual table columns that are encrypted using TDE column encryption will have a much lower level of compression because the encryption takes place in the SQL layer before the advanced compression process.
- Does TDE work with Data Guard and Oracle Streams?
When TDE is used with Data Guard physical standby (10gR2 and later), encrypted data remains encrypted in the log files during shipping to the secondary database(s), so Oracle Network Encryption is optional. The master key needs to be present and open on any Physical Standby database site, whether just applying redo, open read only, open in Active Data Guard (read only and applying redo) and for role transition (switchover or failover).
When TDE is used with Data Guard logical standby (11gR1), the master key needs to be present and open at the secondary site for SQL Apply to decrypt the data that it reads from the log files. The same master encryption key can also used to optionally encrypt the incoming data while it is written to the Logical Standby database. Encrypted data remains encrypted in log files and during transit when the log files are shipped to the secondary database; Oracle Network Encryption is optional.
When TDE is used with Streams in 11gR1, data is transmitted between active databases in clear text to allow data transformation (character sets, database versions, platforms, etc.). When the receiving side cannot be reached and data needs to be stored temporarily, encrypted columns are stored encrypted on disk. Streams in database versions prior to 11gR1 treat encrypted columns as 'unsupported data types' and skip these tables.
- Are there database features that TDE column encryption does not work with?
TDE tablespace encryption encrypts all content stored in that tablespace and does not conflict with any other database feature. TDE column encryption encrypts and decrypts data transparently when data passes through the SQL layer. Some features of Oracle bypass the SQL layer, and hence cannot benefit from TDE column encryption:
- Materialized View Logs (supported from 11gR2)
- Sync. and async CDC (change data capture)
- Transportable Tablespaces
- LOBs (SecureFiles are supported from 11gR1)
- Streams (supported from 11gR1)
- Can I use SQL*Loader with direct path to load data into a table with encrypted columns?
Yes, you can. When the target table contains encrypted columns, the data will be encrypted upon loading the data. Here is a simple example on how to use SQL*Loader with direct path. Simply modify one column in ulcase6.sql from
sal number(7,2),
to
sal number(7,2) encrypt,
and use the correct syntax for SQL*Loader:
$ sqlldr USERID=scott/tiger CONTROL=ulcase6.ctl LOG=ulcase6.log DIRECT=TRUE
- Why can I sometimes still see clear-text data after encrypting sensitive information with TDE column encryption?
This is no different from finding the data still on the disk even after a table is dropped, or a file is deleted. During the lifetime of a table, data may become fragmented, re-arranged, sorted, copied and moved within the table space; this leaves 'ghost copies' of your data within the database file. When encrypting an existing column, only the most recent 'valid' copy is encrypted, leaving behind older clear-text versions in ghost copies. If the data file holding the table space is directly accessed bypassing the access controls of the database (for example with an hex - editor), old clear text values might be visible for some time, until those blocks are overwritten by the database. To minimize this risk, please follow these recommendations:
- Create a new tablespace in a new data file (CREATE TABLESPACE ... )
- Encrypt the clear text values in the original tablespace and data file (ALTER TABLE ... ENCRYPT )
- Repeat 2.) for all tables that contain encrypted columns
- Move all tables from the original tablespace into the new data file (ALTER TABLE ....MOVE... )
- Drop the original table space (DROP TABLESPACE ). Do not use the 'and datafiles' parameter; Oracle recommends to use stronger methods for OS level operations, see 6.)
- Use 'shred' or other OS commands for your platform to delete the old data file on the OS level.
The 6th step is recommended to lower the probability of being able to find ghost copies of the database file, generated by either the operating system, or storage firmware.
- How do I change (rotate, re-key) the encryption keys?
Table C: Encryption re-key capabilities (Oracle Wallet or HSM)
| Database Release |
TDE Column Encryption |
TDE Tablespace Encryption |
| Master encryption key |
Individual table keys |
Master encryption key |
Individual tablespace keys |
| 10gR2 |
Yes |
Yes |
n/a |
n/a |
| 11gR1 |
Yes |
Yes |
No(*) |
No(*) |
| 11gR2 |
Yes |
Yes |
Yes |
No(*) |
(*): Content can be moved from one encrypted tablespace to a new encrypted tablespace, where it is encrypted with a new tablespace key.
TDE column encryption uses a two tier key mechanism. When TDE is applied to an existing application table column, a new table key is created and stored in the Oracle data dictionary. The table keys are encrypted using the TDE master encryption key. The master encryption key is generated when TDE is initialized and stored outside the database in the Oracle Wallet or an HSM device (starting with Oracle 11gR1). Both the master key and table keys can be independently changed (rotated, re-keyed) based on company security policies. Oracle recommends backing up the wallet before and after each master key change.
Changing the wallet password does not re-key the TDE master encryption key.
- How do I quickly encrypt columns in very large tables with billions of rows?
Encrypting columns in an existing table is an 'update' operation and allows Read access, but no DML operations, on that table. With billions of rows, this window of limited availability can last several hours. But with Online Table Redefinition, a mature High-Availability feature of the Oracle Database, the table is locked in exclusive mode only during a very small window that is independent of the size of the table and complexity of the redefinition, and that is completely transparent to users and applications, without any data loss.
- How do I migrate the content from clear text application tablespaces to encrypted tablespaces?
- Extract the DDL that was used to create the original application tablespaces using 'dbms_metadata.get_ddl' and save the output as a SQL script.
- Add 'ENCRYPTION DEFAULT STORAGE(ENCRYPT)' to each 'create tablespace' statement (the SIZE parameter does not need to be changed, since TDE tablespace encryption does not increase storage requirements).
- Export either the whole database, or the schema that owns the application tablespaces, with Data Pump (expdp).
- Drop the application tablespaces 'with contents and datafiles'.
- Run the SQL script to create encrypted application tablespace with otherwise unchanged characteristics.
- Import the dumpfile with Data Pump (impdp).
- Can all master encryption keys be stored in an HSM device?
TDE column encryption: Oracle Database 11.1.0.6 and later allows full key management for TDE column encryption master encryption keys in HSM devices, incl. migrating a master encryption key from Oracle Wallet to HSM.
TDE tablespace encryption master encryption keys in Oracle 11gR1 11.1.0.7 can be created and stored, but not rotated (re-keyed), in HSM devices, which also implies that migration of the TDE tablespace encryption master key from Oracle Wallet to HSM is not possible. HSMs cannot be used for TDE tablespace encryption master keys in 11.1.0.6.
Full key management in HSM (and Oracle Wallet) for both TDE column encryption and TDE tablespace encryption is provided by the unified master encryption key in Oracle Database 11gR2; prior restrictions no longer apply.
Customers who are already using TDE on columns with 10gR2 and do not plan to use TDE tablespace encryption, can use the 'migrate' syntax of Oracle 11gR1 to transparently generate a new master key for their encrypted columns in an HSM device. The existing key in the software wallet is no longer needed, except for importing data from files or backups that were encrypted with old master key(s).
11.1.0.6: For customers who are already using TDE on columns with 10gR2, and want to store the master key for TDE column encryption in an HSM device and want to use TDE tablespace encryption in 11.1.0.6 at the same time, need to generate a new master key in their 11gR1 database prior to migrating. This updates the existing column master key, and generates a new tablespace master key. The next step is to migrate the column encryption key from wallet to HSM.
If customers plan to use TDE tablespace encryption, they need to maintain the software based TDE wallet, since it contains the master key for TDE tablespace encryption.
- How do I use both TDE column encryption with hardware keys and TDE tablespace encryption with software wallet keys at the same time?
Only the master key for TDE column encryption can be fully managed in an HSM device (create, store, rotate, destroy); With Oracle 11.1.0.7, the master key for tablespace encryption can be created and stored, but not rotated, in an HSM device. If you want to use both TDE column encryption (with an HSM-based master key) and TDE tablespace encryption (with a wallet-based master key), the command to open the HSM wallet:
SQL> alter system set encryption wallet open identified by "HSM_authentication_string"
needs to open both the HSM wallet and the software wallet.
Since both wallets need to be open, users can either generate an auto-open software wallet to use for TDE tablespace encryption (keep the encryption wallet; it is required for master key re-key operations later, and potentially contains a list of retired master keys), or the password for the software wallet can be changed to "user-ID:HSM_password", using Oracle Wallet Manager.
Additionally, the '(DIRECTORY=/....)' string in sqlnet.ora needs to point to the software wallet, even though 'METHOD=FILE' has been changed to 'METHOD=HSM'.
- Does the Master key leave the HSM device?
Never. In HSM mode, the database sends the table keys to the HSM device for decryption; communication between database and HSM device is always secure.
Bookmark
