Transparent Data Encryption (TDE) provides mechanism to encrypt the data stored in the OS data files. TDE enables the encryption of data at the storage level to prevent data tempering from outside of the database.
New commands has 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.
Lets 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 100
SELECT 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 100
SELECT con_id, key_id FROM v$encryption_keys;SQL>
CON_ID KEY_ID
———- ——————————————————————————
0 AS6cSkI4u09zv9+RRWMrX2QAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
SQL> SET LINESIZE 200
COLUMN wrl_parameter FORMAT A50
SELECT * 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
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:
SQL> CREATE TABLE employee ( first_name VARCHAR2(128), last_name VARCHAR2(128), empID NUMBER, salary NUMBER(6) ENCRYPT ); 2 3 4 5 6 Table created. SQL> SQL> select owner,table_name,column_name,encryption_alg from dba_encrypted_columns where table_name='EMPLOYEE'; OWNER TABLE_NAME COLUMN_NAME ENCRYPTION_A ---------- ------------ ------------ ------------ RAJ EMPLOYEE SALARY AES 192 bits key
ENABLE AUTOLOGIN :
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;
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 force
ORACLE instance started.
Total System Global Area 838860800 bytes
Fixed Size 2929936 bytes
Variable Size 570428144 bytes
Database Buffers 260046848 bytes
Redo Buffers 5455872 bytes
Database 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 be 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; NOTE: To create a keystore user should have either ADMINISTER KEY MANAGEMENT or SYSKM privilege.
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;
