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.
# 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)
(SOURCE =(METHOD = FILE)(METHOD_DATA =
(DIRECTORY = /media/sf_stuff/WALLET)))
SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE ‘/media/sf_stuff/WALLET/’ IDENTIFIED BY walletpass#123;
SQL> host ls /media/sf_stuff/WALLET/
Now open the keystore:
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY walletpass#123;
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:
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;