Today we are going to talk about Transparent Data Encryption (TDE) In Oracle 12c. First, have look at the introduction of TDE.  After the introduction, we have steps to configure TDE.

Transparent Data Encryption (TDE) was introduced in Oracle Database 10g Release 2 as an OUT-OF place mechanism to encrypt data at the storage (media) level. TDE enables the encryption of data at the storage level to prevent data tempering from outside of the database. Oracle database authentication, authorization, and auditing mechanism are enforced to secure data in the database. However, there was no mechanism (prior to Oracle 10g Release 2) defined in the database which can secure the Operating System data files where the data is physically stored. To protect these data files, Oracle introduced Transparent Data Encryption (TDE). TDE provides a mechanism to encrypt the data stored in the OS data files. To prevent, unauthorized decryption, TDE stores the encryption keys in a security module outside of the database called Wallet (Keystore in Oracle Database 12c)

New commands have been introduced in Oracle 12c for enabling Transparent data encryption.ADMINISTER KEY MANAGEMENT will replace the previous commands like ALTER SYSTEM SET ENCRYPTION WALLET and Wallet is known as keystore in 12c.

Let’s see how to configure TDE.
 
1. Create a wallet/keystore location.

mkdir -p /media/sf_stuff/WALLET

2. update the wallet/keystore location in sqlnet.ora. It should look like.

cat $ORACLE_HOME/network/admin/sqlnet.ora
# sqlnet.ora Network Configuration File: /home/oracle/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

ENCRYPTION_WALLET_LOCATION =
(SOURCE =(METHOD = FILE)(METHOD_DATA =
(DIRECTORY = /media/sf_stuff/WALLET)))

Create keystore:

SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE ‘/media/sf_stuff/WALLET/’ IDENTIFIED BY walletpass#123;

keystore altered.

SQL> host ls /media/sf_stuff/WALLET/
ewallet.p12

Now open the keystore:

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY walletpass#123;

keystore altered.

Now activate the key:

SQL> SET LINESIZE 100SQL> SET LINESIZE 100SELECT con_id, key_id FROM v$encryption_keys;SQL>  no rows selected SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY walletpass#123 WITH BACKUP; keystore altered. SQL> SET LINESIZE 100SELECT con_id, key_id FROM v$encryption_keys;SQL>      CON_ID KEY_ID———- —————————————————————————— 0 AS6cSkI4u09zv9+RRWMrX2QAAAAAAAAAAAAAAAAAAAAAAAAAAAAA  SQL> SET LINESIZE 200COLUMN wrl_parameter FORMAT A50SELECT * FROM v$encryption_wallet;SQL> SQL> WRL_TYPE      WRL_PARAMETER STATUS        WALLET_TYPE     WALLET_OR FULLY_BAC     CON_ID——————– ————————————————– —————————— ——————– ——— ——— ———-FILE      /media/sf_stuff/WALLET/ OPEN        PASSWORD     SINGLE    NO 0

 create a encrypted a tablespace 

SQL> CREATE TABLESPACE TEST_ENCRY SQL> CREATE TABLESPACE TEST_ENCRY datafile ‘/home/oracle/app/oracle/oradata/cdb1/testencry.dbf’ size 2G ENCRYPTION USING ‘AES256’DEFAULT STORAGE(ENCRYPT);  2    3    4   Tablespace created.  SQL> create table emp_ency(empno   Number(3),Name     varchar(10)) tablespace TEST_ENCRY;  2    3    4   Table created.   SQL> select tablespace_name,encrypted from dba_tablespaces where tablespace_name=’TEST_ENCRY’; TABLESPACE_NAME        ENC—————————— —TEST_ENCRY        YES

Create a table with encrypted column:

ENABLE AUTOLOGIN :
SQL> SELECT * FROM v$encryption_wallet;SQL> SELECT * FROM v$encryption_wallet; WRL_TYPE      WRL_PARAMETER STATUS        WALLET_TYPE     WALLET_OR FULLY_BAC     CON_ID——————– ————————————————– —————————— ——————– ——— ——— ———-FILE      /media/sf_stuff/WALLET/ OPEN        PASSWORD     SINGLE    NO 0 
Here the wallet_type is PASSWORD, i.e every time we restart the database, we need to open the key/wallet explicitly. To avoid this, we can enable auto login ,so that next time when db gets restart, it will open the wallet automatically.
SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE ‘/media/sf_stuff/WALLET/’ IDENTIFIED BY walletpass#123;SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE ‘/media/sf_stuff/WALLET/’ IDENTIFIED BY walletpass#123; keystore altered. SQL>  SELECT * FROM v$encryption_wallet; WRL_TYPE      WRL_PARAMETER STATUS        WALLET_TYPE     WALLET_OR FULLY_BAC     CON_ID——————– ————————————————– —————————— ——————– ——— ——— ———-FILE      /media/sf_stuff/WALLET/ OPEN        PASSWORD     SINGLE    NO 0 SQL> SQL> startup forceORACLE instance started. Total System Global Area  838860800 bytesFixed Size     2929936 bytesVariable Size   570428144 bytesDatabase Buffers   260046848 bytesRedo Buffers     5455872 bytesDatabase mounted.Database opened.SQL> SELECT * FROM v$encryption_wallet;  WRL_TYPE      WRL_PARAMETER STATUS        WALLET_TYPE     WALLET_OR FULLY_BAC     CON_ID——————– ————————————————– —————————— ——————– ——— ——— ———-FILE      /media/sf_stuff/WALLET/ OPEN        AUTOLOGIN     SINGLE    NO 0

we can see the wallet opened automatically and the wallet_type has been changed from PASSWORD TO AUTOLOGIN.

 

For multi-tenant database:
 

In a multi-tenant database (CDB), the Keystore has to be being created in the ROOT container (CDB$ROOT).
This single Keystore will be shared by all the associated PDBs as well as the CDB$ROOT container.

So for this, we need to use CONTAINER=ALL clause to open and activate the keystore in all pdbs.
 

ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY walletpass#123 CONTAINER=ALL;

ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY walletpass#123 WITH BACKUP CONTAINER=ALL;

Related dictionary tables for TDE:

—-What tables contain TDE encrypted columns?
sql> select table_name, column_name from dba_encrypted_columns;

——-What tables are stored in TDE encrypted tablespaces?
sql> select a.table_name, a.tablespace_name from dba_tables a, dba_tablespaces b
where a.tablespace_name = b.tablespace_name and b.encrypted = ‘YES’;

——-What indexes are stored in TDE encrypted tablespaces?
sql> select a.index_name, a.tablespace_name from dba_indexes a, dba_tablespaces b
where a.tablespace_name = b.tablespace_name and b.encrypted = ‘YES’ and index_name not like ‘SYS_IL%’;

——- getting key/wallet details:
SQL> SELECT * FROM v$encryption_wallet;

SQL> SELECT con_id, key_id FROM v$encryption_keys;

 

About The Author

Leave a Reply