12c: Using Transparent Data Encryption

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.

Before you can create and manage keystores and encryption keys, you must be granted the ADMINISTER KEY MANAGEMENT system privilege, which you can use for most operations when the database is open. If you must open the keystore at the mount stage, then you must be granted the SYSKM administrative privilege, which includes the ADMINISTER KEY MANAGEMENT system privilege and other necessary privileges.

Transparent Data Encryption (TDE) column encryption enables you to encrypt sensitive data stored in selected table columns.

TDE tablespace encryption enables you to encrypt all of the data stored in a tablespace.


Configuring a Software Keystore

A software keystore is a container that stores the Transparent Data Encryption master encryption key. Before you can configure the keystore, you first must define a location for it in the sqlnet.ora file. There is one keystore per database, and the database locates this keystore by checking the keystore location that you define in the sqlnet.ora file.

1- Set the Software Keystore Location in the sqlnet.ora File
In a multitenant environment, the keystore location is set for the entire multitenant container database (CDB), not for individual pluggable databases (PDBs). To find this location, you can query the WRL_PARAMETER column of the V$ENCRYPTION_WALLET view.

select * from V$ENCRYPTION_WALLET

WRL_TYPE	WRL_PARAMETER	STATUS	WALLET_TYPE	WALLET_ORDER	FULLY_BACKED_UP	CON_ID
FILE	C:\APP\IBUKHARY\ADMIN\ORCL\WALLET	NOT_AVAILABLE	UNKNOWN	SINGLE	UNDEFINED	0

If the keystore was not created in the default location, then the STATUS column of the V$ENCRYPTION_WALLET view isNOT_AVAILABLE.

ENCRYPTION_WALLET_LOCATION=
  (SOURCE=
   (METHOD=FILE)
    (METHOD_DATA=
     (DIRECTORY=C:\app\ibukhary\admin\orcl\WALLET)))

You can use ASM diskgroup also for the file location.

2- Create the Software Keystore

You can create password-based software keystores, auto-login software keystores, and local auto-login software keystores.

Creating a Password-Based Software Keystore

a) Connect to DB instance as a user who has been granted the ADMINISTER KEY MANAGEMENT or SYSKMprivilege.

C:\Users\ibukhary>sqlplus / as sysdba

b) Run the ADMINISTER KEY MANAGEMENT SQL statement to create the keystore.
SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE 'C:\app\ibukhary\admin\orcl\WALLET' IDENTIFIED BY w123456;
keystore altered.

After you run this statement, the ewallet.p12 file, which is the keystore, appears in the keystore location.
C:\app\ibukhary\admin\orcl\WALLET>dir
 Volume in drive C is Windows
 Volume Serial Number is F48F-1C01

 Directory of C:\app\ibukhary\admin\orcl\WALLET

05/24/2015  12:04 PM    



         .
05/24/2015  12:04 PM    



         ..
05/24/2015  12:04 PM             2,400 ewallet.p12
               1 File(s)          2,400 bytes

               2 Dir(s)  255,638,646,784 bytes free

select * from V$ENCRYPTION_WALLET

WRL_TYPE	WRL_PARAMETER	STATUS	WALLET_TYPE	WALLET_ORDER	FULLY_BACKED_UP	CON_ID
FILE	C:\APP\IBUKHARY\ADMIN\ORCL\WALLET	CLOSED	UNKNOWN	SINGLE	UNDEFINED	0

3- Open the Software Keystore

You must manually open a password-based software keystore before any TDE master encryption keys can be created or accessed in the keystore. You do not need to manually open auto-login or local auto-login software keystores. These keystore are automatically opened when it is required, that is, when an encryption operation must access the key. After you open a keystore, it remains open until you manually close it. Each time you restart a database instance, you must manually open the password keystore to reenable encryption and decryption operations.

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY w123456;
keystore altered.

If you get the error ORA-28367: wallet does not exist, logout from your session and connect again.

4- Set the Software TDE Master Encryption Key

Once the keystore is open, you can set a TDE master encryption key for it. The TDE master encryption key is stored in the keystore. It protects the TDE table keys and tablespace encryption keys. By default, the TDE master encryption key is a key that Transparent Data Encryption (TDE) generates. In a multitenant environment, you can create and manage the TDE master encryption key from either the root or the PDB.

SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY w123456 WITH BACKUP USING 'creditcardNum_key_backup' CONT

AINER=CURRENT;

keystore altered.

SQL> show con_name

CON_NAME

------------------------------

CDB$ROOT

Password is the mandatory keystore password that you created when you created the keystore. WITH BACKUP creates a backup of the keystore. You must use this option for password-based keystores. Optionally, you can use the USING clause to add a brief description of the backup.

5- Encrypt Data

You can encrypt individual columns or entire tablespaces. TDE column encryption encrypts and decrypts data at the SQL layer. Oracle Database utilities and features that bypass the SQL layer cannot use the services provided by TDE column encryption.

Do not use TDE column encryption with Range scan search through an index, Transportable tablespaces, Synchronous change data capture and Index types other than B-tree. You cannot use TDE column encryption to encrypt columns used in foreign key constraints or you could use the DBMS_CRYPTO PL/SQL package for their encryption needs.

Column Encrypt Example:

CREATE TABLE SYSTEM.TEST_TDE(ID NUMBER ENCRYPT NO SALT 'NOMAC',NAME VARCHAR2(20),CREDIT_CARD_NUM VARCHAR2(10) ENCRYPT USING '3DES168',ADDRESS VARCHAR2(10))

Note: 

Salt strengthen the security of encrypted data, It is a random string that is added to the data before it is encrypted, making it more difficult for attackers to steal the data by matching patterns of ciphertext to known ciphertext samples. You should NO SALT if you want to index the column.

NOMAC parameter is used to bypass integrity checks that TDE performs. This can save up to 20 bytes of disk space per encrypted value.

Tablespace Encrypt Example:

SQL> CREATE TABLESPACE ts_enc

  2    DATAFILE 'c:\temp\enc_df.dbf' SIZE 1M   ENCRYPTION USING 'AES256'   DEFAULT STORAGE (ENCRYPT);

Tablespace created.

Note:

You cannot change an existing tablespace to make it encrypted. You can, however, import data into an encrypted tablespace by using Oracle Data Pump. For security reasons, you cannot encrypt a tablespace with the NO SALT option. You can query the ENCRYPTED column of the DBA_TABLESPACES and USER_TABLESPACES data dictionary views to verify if a tablespace was encrypted.