Transparent Data Encryption (TDE) relays on the database kernel mechanism and does not require additional programming. The key management is performed automatically by the database. From an architectural point of view, it was designed to protect the data from physical theft and it does not provide data access protection. The encryption is performed at storage level, and the column decryption occurs at data access. Therefore, the data will be visible for anyone with select privileges on tables containing encrypted columns with TDE. Being a feature provided by Oracle Advanced Security (OAS), you must purchase the OAS pack license to use this capability.
In this recipe, we will encrypt the employees table's columns, salary and commission_pct, using various options available for TDE column encryption.
- As the
oracleuser, create a directory for the encryption wallet (be sure to secure the filesystem permissions as described in Chapter 1, Operating System Security):mkdir –p /security/wallets/tde chmod 600 /security/wallets/tde
- TDE encryption is performed using an external master key placed externally within an encryption wallet which is used to encrypt the table key, which in turn is used to encrypt and decrypt data in the table column. The encryption wallet location is defined within sqlnet.ora using
ENCRYPTION_WALLET_PARAMETER. Backup sqlnet.ora and add the path to directory created in the previous step toENCRYPTION_WALLET_LOCATIONparameter as follows:ENCRYPTION_WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = /security/wallets/tde) ) )
- Connect as
systemuser and create the encryption wallet by executing the following statement:SQL> conn system Enter password: Connected. SQL>SQL> alter system set encryption key identified by "UYio71+^ZaPO";
- Connect as the user
HRand modify theemployeestable'ssalaryandcommission_pctcolumns, and encrypt theemployeestable's fields by executing the following statements:SQL> conn HR Enter password: Connected. Table altered. SQL> alter table hr.employees modify (salary encrypt ); Table altered. SQL> alter table employees modify (commission_pct encrypt ); Table altered.
- The information related to the encrypted columns can be found in the
USER_ENCRYPTED_COLUMNSdictionary view at user-level and in theDBA_ENCRYPTED_COLUMNSsystem dictionary view at database-level:
- The default encryption algorithm is
AES192. If you want to change the encryption algorithm, for example toAES256, issue the following command:SQL> alter table hr.employees rekey using 'AES256'; Table altered.
- If you want to regenerate the table encryption key, issue the following command:
SQL> alter table hr.employees rekey; Table altered
- The default encryption mode is performed using salt. Salt is a cryptographic term used for a random string that is added to data before encryption and is used to prevent dictionary and pattern matching type attacks. To remove salt from encrypted columns execute the following:
SQL> alter table hr.employees modify (salary encrypt no salt ); Table altered. SQL> alter table hr.employees modify (commission_pct encrypt no salt); Table altered.
- To decrypt the columns, execute the following command:
SQL> alter table hr.employees modify (salary decrypt); Table altered. SQL> alter table hr.employees modify (commission_pct decrypt); Table altered. SQL>
The data is encrypted at storage level. This means that the transactions from redo logs, undo, and temp segments will contain these columns in encrypted format. The column data is encrypted also at buffer cache level being protected in this way against different memory read techniques. The columns' encryption keys are stored in the ENC$ dictionary table in encrypted form. The column-level keys are encrypted using the master key that has an external placement configured in sqlnet.ora, using the ENCRYPTION_WALLET_LOCATION or WALLET_LOCATION parameter. The master key value is generated randomly at its definition by TDE. Using the salt default option, the column will be prefixed with randomly generated strings. This method makes statistical attacks and hash matching difficult.
By default, the columns are encrypted using salt and MAC options. The default algorithm used is AES192 and the MAC is implemented using SHA1.
Information about encrypted columns can be found in the following dictionary views:
ALL_ENCRYPTED_COLUMNSUSER_ENCRYPTED_COLUMNSDBA_ENCRYPTED_COLUMNS
There are some limitations regarding column encryption, recommendations to be made, and some performance implications by using column encryption.
The following are performance implications caused by using the column encryption:
- The database performance is not affected until the encrypted data is accessed or modified. Oracle claims that column encryption and decryption will impose an approximate 5 percent performance penalty. This is a rogue approximation, the performance penalty depends on many factors such as how many encrypted columns are selected, type of joins, if sorting is performed or not against encrypted columns and more. To find out the exact performance penalty you should perform several extensive tests against the encrypted data.
- Storage overheads: The overhead will not be seen by using the dictionary views.
The following are the limitations caused by using the column encryption:
- The use of streams replication, materialized view logs, transportable tablespaces, logminer, exp/imp, and Oracle Audit Vault, if you use REDO COLLECTORS that are based on streams replication technology.
- You cannot encrypt indexed columns using the default salt option, and you cannot create indexes on columns encrypted with salt.
- You cannot encrypt foreign key indexes using TDE column encryption. If this is a necessity consider moving tables with foreign indexes to encrypted tablespaces with TDE.
Note
The datatypes that can be encrypted with TDE column encryption are:
BINARY_DOUBLEBINARY_FLOATCHARDATEINTERVAL DAY TO SECONDINTERVAL YEAR TO MONTHLOBs(Internal LOBs and SECUREFILE LOBs Only)NCHARNUMBERNVARCHAR2RAWTIMESTAMP(includesTIMESTAMP WITH TIME ZONEandTIMESTAMP WITH LOCAL TIME ZONE)VARCHAR2
Do not encrypt columns used in index range scans, the optimizer will not take into consideration the index anymore. The default MAC option will add an additional 20 bytes overhead per encrypted value. Also MAC induces performance overhead due to integrity checking performed at data access. Using NOMAC option will reduce space and performance penalties considerably. Also by using salt there will be an additional 16 bytes overhead per encrypted data. Consider using nosalt option to reduce storage space. The downside of suppressing MAC and salt is that you will end up with weaker security per encrypted column. To save space you can use the NOMAC option. After the columns are encrypted, there can remain portions of data in cleartext format that belonged to columns before encryption. Therefore, it is recommended to move the tables containing encrypted columns to other tablespaces.
Also, there could be situations when the unencrypted data chunks may remain in the swap area, and it is possible to be read by unauthorized users. A solution for this phenomenon may be to use a large page allocation for the database and sessions, or use encrypted swap filesystems. For example, eCryptfs provides encryption at filesystem-level for swap, and can be used on Linux.