Transparent Data Encryption in Oracle
5 min readTransparent Data Encryption (TDE) enables you to encrypt sensitive data that you store in tables and tablespaces. After the data is encrypted, this data is transparently decrypted for authorized users or applications when they access this data. TDE helps protect data stored on media (also called data at rest) in the event that the storage media or data file is stolen. Oracle Database uses authentication, authorization, and auditing mechanisms to secure data in the database, but not in the operating system data files where data is stored. To protect these data files, Oracle Database provides Transparent Data Encryption (TDE). TDE encrypts sensitive data stored in data files. To prevent unauthorized decryption, TDE stores the encryption keys in a security module external to the database, called a keystore.
TDE is using a two tier encryption key architecture consisting of :
a master encryption key - this is the encryption key used to encrypt secondary keys used for column encryption and tablespace encryption.
one or more table and/or tablespace keys - these are the keys that are used to encrypt one or more specific columns or the keys used to encrypt tablespaces. There is only one table key regardless of the number of encrypted columns in a table and it will be stored in the data dictionary. The tablespace key is stored in the header of each datafile of the encrypted tablespace.
The table and tablespace keys are encrypted using the master key. The master key is stored in an external security module (ESM) that can be one of the following: an Oracle Wallet - a secure container outside of the database. It is encrypted with a password. a Hardware Security Module (HSM) - a device used to secure keys and perform cryptographic operations.
To start using TDE the following operations have to be performed:
1.) Make sure that the wallet location exists. If a non default wallet location must be used then specify it in the sqlnet.ora file :
ENCRYPTION_WALLET_LOCATION =
(SOURCE = (METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = /u01/app/oracle/orcl/wallet)
)
)
Note : The default encryption wallet location is $ORACLE_BASE/admin/<global_db_name>/wallet. If we want to let Oracle manage a wallet in the default location then there is no need to set the ENCRYPTION_WALLET_LOCATION parameter in sqlnet.ora.
It is important to check that the location specified in sqlnet.ora or the default location exists and can be read/written by the Oracle processes.
2.) Generate a master key : SQL> alter system set encryption key identified by "wallet_password" ; system altered
This command will do the following : A.) If there is no wallet currently in the wallet location then a new wallet with the password "wallet_password" will be generated. The password is enclosed in double quotes to preserve the case of the characters. If the double quotes are not used then the characters of the password will be all in upper case. This command will also cause the new wallet to be opened and ready for use. B.) A new master key will be generated and will be written to the wallet. This newly generated master key will become the active master key. The old master keys (if there were any) will still be kept in the wallet but they will not be active. They are kept there to be used when decrypting data that was previously encrypted using them .
To see the status of an wallet run the following query:
SQL> select * from v$encryption_wallet;
WRL_TYPE WRL_PARAMETER STATUS
----------- ------------------------------ -----------
file /u01/app/oracle/orcl/wallet OPEN
3.) Enable encryption for a column or for an entire tablespace: 3.1) Create a table by specifying the encrypt option: SQL> create table test(col1 number, col2 varchar2(100) encrypt using 'AES256' NO SALT) ; 3.2) Encrypt the column(s) of an existing table : SQL> alter table test modify( col2 encrypt SALT ) ; Note : If the table has many rows then this operation might take some time since all the values stored in col2 must be replaced by encrypted strings. If the access to the table during this operations is needed then useOnline Table Redefinition 3.3) Create an encrypted tablespace : The syntax is the same as creating a normal tablespace except for two clauses: We specify the encryption algorithm – in this case ‘AES256′. If we do not specify this, it will default to ‘AES128′. At the time of tablespace creation specify the encryption and default storage clause. Define the encryption algorithem as " using 'algorithm' " along with the encryption clause. We can use the following algorithms while creating an encrypted tablespace. AES128 AES192 AED256 3DES168 If we don't specify any algorithm with the encryption clause it will use AES128 as default. The DEFAULT STORAGE (ENCRYPT) clause. SQL> create tablespace encryptedtbs datafile '/u01/app/oracle/orcl/wallet/encryptedtbs01.dbf' size 100M encryption using 'AES256' default storage(encrypt) ; Note: An existing non encrypted tablespace cannot be encrypted. If we must encrypt the data from an entire tablespace then create a new encrypted tablespace and then move the data from the old tablespace to the new one TDE Master Key and Wallet Management .
The wallet is a critical component and should be backed up in a secure location (different to the location where the database backups are stored!). If the wallet containing the master keys is lost or if its password is forgotten then the encrypted data will not be accessible anymore. Make sure that the wallet is backed up in the following scenarios: Immediately after creating it. 1. When regenerating the master key 2. When backing up the database. Make sure that the wallet backup is not stored in the same location with the database backup 3. Before changing the wallet password We can identify encrypted tablespaces in the database by using the below query : SQL>SELECT ts.name, es.encryptedts, es.encryptionalg FROM v$tablespace ts INNER JOIN v$encrypted_tablespaces es ON es.ts# = ts.ts# ; The following are supported with encrypted tablespaces 1. Move table back and forth between encrypted tablespace and non-encrypted tablespace . 2. Datapump is supported to export/import encrypted content/tablespaces. 3. Transportable tablespace is supported using datapump. The following are not supported with encrypted tablespaces 1. Tablespace encryption cannot be used for SYSTEM, SYSAUX, UNDO and TEMP tablespaces . 2. Existing tablespace cannot be encrypted . 3. Traditional export/import utilities for encrypted content.
