- 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 Realms, Multi-factor authorization and Command rules that can be used 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?
Overhead associated with TDE falls into two areas: storage and performance.
- The performance overhead associated with encrypting or decrypting a common attribute such as credit card number is estimated to be around 5%. When an index is built on a column encrypted using TDE, 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 encrypted the value used in the SQL statement and perform an index lookup using the cipher text.
- Storage overhead associated with TDE can be significant due an additional 20 byte integrity check associated with each encrypted value. In addition, TDE will pad out encrypted values to 16 bytes, so if a credit card number required 9 bytes of storage, encrypting the credit card number would require an additional 7 bytes of storage. Finally, if salt is specified on the encrypted value, the salt will require an additional 16 bytes. In summary, encrypting a single column in a table will require between 33 and 48 bytes of additional storage per row.
Users can control the amount of additional storage by choosing the 'no salt' option, and/or the new 'nomac' option (available from 10.2.0.4.), which eliminates the additional CPU cycles and storage of the 20 byte hash value to each encrypted field.
Tablespace encryption (available with 11gR1) has no additional storage overhead; the performance overhead percentage is estimated to be in the low single digits.
- How is this different from the encryption Oracle already provides?
Oracle introduced an encryption API with Oracle8i called the dbms_obfuscation_toolkit. In Oracle 10g Release 1, a new API was introduced called DBMS_CRYPTO. 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 column-level TDE (from 10gR2) and the new tablespace encryption in 11gR1 don't require changes to the application, are transparent to the end users, and provide automated key management.
- What are the encryption algorithms that can be used with TDE?
TDE supports 3DES168, AES128 (default for tablespace encryption), AES192 (default for column-level TDE), and AES256
- 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.
- 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.
- Can I use Oracle Wallet Manager (OWM) to create the encryption wallet and master key for TDE?
No. 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 exist in the location specified in the local sqlnet.ora file) and adds a master key to it.
In Oracle 11gR1, TDE and other security features and options have been migrated to Enterprise Manager Database Control, hence the wallet and the master key can be generated using the Web-based GUI of Enterprise Manager.
Also with 11gR1, the master key for column-level TDE can be created and stored in an HSM device; the master key will never leave the HSM device in clear text.
In this case, the syntax to create a new key in an HSM device changes to:
SQL> alter system set [encryption] key identified by "user-ID:HSM_password"
where 'user-ID' and 'HSM_password' are the credentials the database uses to log into the HSM device.
- How do I create an auto-open wallet?
An auto-open wallet ('cwallet.sso') needs to be created from an existing encryption wallet ('ewallet.p12'), so that the master key is transferred to the new 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
In both cases the user will be prompted for the wallet password.
- What is the default location in which Oracle Wallet Manager looks for a wallet?
It's in
/etc/ORACLE/WALLETS/<Oracle software owner user name>
which usually translates to
/etc/ORACLE/WALLETS/oracle
Since '/etc' is owned by root, root needs to create the sub-directories and later change ownership to oracle:
cd /etc
chown -R oracle:oinstall ./ORACLE
- 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, so there is no risk that either the encryption wallet or the auto-open wallet are 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 automatically; you need to use the exclude dataset statement to specify what files to skip during a backup.
exclude name *.p12
Detailed instructions about the EXCLUDE keyword.
- Does the Master key leave the HSM device?
Never. In HSM mode, the database sends the column keys to the HSM device for decryption; communication between database and HSM device is always secure.
- How to use column-level TDE with HSM keys and tablespace encryption with software wallet keys at the same time?
In this case, the syntax to open the HSM wallet:
SQL> alter system set wallet open identified by "userID:password"
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 tablespace encryption (in this case, rename or delete the encryption wallet), or the password for the software wallet can be changed to "userID: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'.
- What is a Wallet?
A wallet is a container that is used to store authentication and signing credentials, including 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. In addition, TDE, 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, with the exception of Diffie-Hellman.
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: physical and logical standby) environments where encrypted columns are shipped to secondary sites.
- 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 based on PKCS#5) should contain a minimum of 8 alphanumeric characters. Wallet passwords can be changed using Oracle Wallet Manager. 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 keys.
With Oracle Database 11gR1, the master key for column-level TDE can be stored in an HSM device. In this case, the master key is not copied into database memory to encrypt and decrypt the table keys; rather the database knows from a configuration file how to send the table keys to the HSM device, where they are decrypted and returned to the database.
- 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 master key -- they are independent.
- Can I use TDE on columns used in foreign key constraints?
TDE doesn't support encrypting columns used in foreign key constraints. This is due to the fact that individual tables have their own unique encryption key.
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';
- What is Oracle Secure Backup (OSB)?
OSB 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.
- 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.
- 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.
- Does compression work with tablespace encryption?
Yes
- Do transportable tablespaces work with tablespace encryption?
Yes, the wallet containing the master key would need to be copied to the secondary database. If the tablespace is moved and the wallet is not present, the secondary database will return an error when the data in the tablespace is accessed.
- What data types does Transparent Data Encryption (TDE) support?
The following data types can be encrypted using column-level TDE:
varchar2 nvarchar2
number date
binary_float binary_double
timestamp raw
char nchar
SecureFile (from 11gR1)
For tablespace encryption, there are no limitations in terms of supported datatypes.
- Does TDE work with Data Guard?
Yes, TDE works with Data Guard with 10gR2 when it is configured in physical standby mode. Copying the wallet to the physical standby and making the master key available is only necessary when encrypted data is accessed on the secondary database (mounted read only, or after a failover), not for the processing of the incoming log files.
Oracle 11gR1 supports both physical and logical standby; with logical standby, the master key needs to be available to the secondary database, for it to read encrypted data from the incoming log files.
- Are there other database features that TDE does not work with?
Column-level TDE encrypts and decrypts data transparently when it passes through the SQL layer. Some features of Oracle bypass the SQL layer, and hence cannot leverage TDE:
Materialized View Logs
Streams (supported from 11gR1)
Sync. and async CDC (change data capture)
Transportable Tablespaces
LOBs (SecureFiles are supported from 11gR1)
Tablespace encryption encrypts all content stored in that tablespace.
- Does the data stay encrypted on the network?
Data encrypted with TDE will first be decrypted prior to leaving the database to the client. However, the data can be encrypted over the network using Oracle's network encryption solution, which is included with TDE in the Oracle Advanced Security option. Oracle's network encryption solution can encrypt all data traveling to and from a database over SQL*Net.
When column-level TDE is used with Data Guard physical standby (10gR2 and later), encrypted columns remain 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 on the secondary site only when it is either in Read Only mode or after a failover, but not for applying the redo.
When column-level 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 key is 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 column-level 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.
- Can I use SQL*Loader with direct path to load data into a table with encrypted columns?
Yes, you can. Without any changes to the SQL*Loader syntax, the .dat file or the .ctl file. 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 no salt,
and use the correct syntax for SQL*Loader:
sqlldr USERID=scott/tiger CONTROL=ulcase6.ctl LOG=ulcase6.log DIRECT=TRUE
- Looking at the database file after encrypting existing data in a column, I can sometimes still see some of the clear text values. Why?
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 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.
- Can the encryption keys be changed?
The keys for tablespace encryption cannot be changed; work around is moving the content from one encrypted tabelspace to a new encrypted tablespace
TDE 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 within the Oracle data dictionary using a Master Key. The master key is generated when TDE is initialized and stored outside the database in an Oracle Wallet or an HSM device (with Oracle 11gR1). Both the master key and column keys can be independently changed based on company security policies. Oracle recommends backing up the wallet before and after each key change (master or column key).
- How can I encrypt columns in very large tables (billions of rows)?
Encrypting columns in an existing table is an 'update' operation and allows only Read access, but no DML operations, on that table. With billions of rows, this window of limited availability can last several hours. Online table redefinition requires only a very little time window where the original table is locked; this time is independent of the amount of data encrypted or the complexity of the redefinition, and it's transparent to users and applications.
- Can all Master Keys be stored in an HSM device?
No. Currently, only the master keys for column-level TDE can be stored in an HSM device.
Customers who are already using TDE on columns with 10gR2 and do not plan to use tablespace encryption, can use the 'migrate' syntax 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).
For customers who are already using TDE on columns with 10gR2, and want to store the master key for column-level TDE in an HSM device and want to use tablespace encryption 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.
If customers plan to add tablespace encryption, they need to maintain the software based TDE wallet, since it contains the master key for tablespace encryption.
- Can I re-key the master key for encrypted tablespaces?
No; in the current release (11gR1), it is not possible to re-key the master key for tablespace encryption. The workaround is to create a new encrypted tablespace, export the whole database (or schema) with Data Pump (expdp), because exporting a tablespace only exports the tables; use Data Pump (impdp) to move the dump file into the new tablespace, and delete the old encrypted tablespace.
- How do I migrate the content from clear text application tablespaces to encrypted tablespaces?
Bookmark
