Step by step TDE setup in oracle 12c RAC
Updated: Apr 30, 2022
What is TDE ?
https://www.step-by-step-dba.com/post/transparent-data-encryption-tde-in-oracle-12c
Step 1: Setup a Keystore Location:(wallet) in shared storage (ASM)
---------------------------------------------------------------------------------------------------
[root@node1 ~]# su - grid
[grid@node1 ~]$ . oraenv
ORACLE_SID = [+ASM1] ?
The Oracle base has been set to /u01/app/oracle
[grid@node1 ~]$
[grid@node1 ~]$ asmcmd
ASMCMD> lsdg
State Type Rebal Sector Logical_Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED EXTERN N 512 512 4096 1048576 61440 51711 0 51711 0 N DATA/
MOUNTED EXTERN N 512 512 4096 4194304 40960 6136 0 6136 0 Y OCRVOTE/
ASMCMD>
ASMCMD> cd DATA/
ASMCMD> ls
testdb/
ASMCMD> cd testdb/
ASMCMD> mkdir WALLET
ASMCMD> cd WALLET
ASMCMD> pwd
+DATA/testdb/WALLET
Step 2: Add the wallet location in the sqlnet.ora file
---------------------------------------------------------------------------------------------------
[grid@node1 ~]$ cd /u01/app/grid/product/12.2.0.1/grid/network/admin/
[grid@node1 admin]$ ls -lrt
total 28
-rw-r--r--. 1 grid oinstall 1441 Aug 28 2015 shrept.lst
drwxr-xr-x. 2 grid oinstall 4096 Jan 26 2017 samples
-rw-r--r--. 1 grid oinstall 301 Apr 9 13:06 endpoints_listener.ora
-rw-r--r--. 1 grid oinstall 1281 Apr 9 13:25 listener1804091PM2501.bak
-rw-r--r--. 1 grid oinstall 203 Apr 9 13:25 sqlnet.ora
-rw-r--r--. 1 grid oinstall 1281 Apr 9 13:25 listener.ora.bak.node1
-rw-r--r--. 1 grid oinstall 1519 Apr 9 13:33 listener.ora
copy the sqlnet.ora file to oracle home .
[root@node1 ~]# su - oracle
[oracle@node1 ~]$ cp /u01/app/grid/product/12.2.0.1/grid/network/admin/sqlnet.ora /u01/app/oracle/product/12.2.0.1/db_1/network/admin/
[oracle@node1 ~]$
[oracle@node1 ~]$ ls -lrt /u01/app/oracle/product/12.2.0.1/db_1/network/admin/sqlnet.ora
-rw-r--r--. 1 oracle oinstall 335 Apr 25 11:46 /u01/app/oracle/product/12.2.0.1/db_1/network/admin/sqlnet.ora
Add below entry to sqlnet.ora
ENCRYPTION_WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = +DATA/testdb/WALLET)
)
)
Step 3: Create a Keystore: to store the TDE Master Encryption Keys.
---------------------------------------------------------------------------------------------------
SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '+DATA/testdb/WALLET' IDENTIFIED BY testdb1;
keystore altered.
And now check the wallet location in ASM disk group to verify it is created .
[grid@node1 ~]$ asmcmd
ASMCMD>
ASMCMD>
ASMCMD> lsdg
State Type Rebal Sector Logical_Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED EXTERN N 512 512 4096 1048576 61440 51710 0 51710 0 N DATA/
MOUNTED EXTERN N 512 512 4096 4194304 40960 6136 0 6136 0 Y OCRVOTE/
ASMCMD>
ASMCMD>
ASMCMD> cd DATA/testdb/WALLET
ASMCMD> ls -lt
Type Redund Striped Time Sys Name
KEY_STORE UNPROT COARSE APR 25 11:00:00 N ewallet.p12 => +DATA/testdb/KEY_STORE/ewallet.296.974375403
Step 4: Open the Keystore:
---------------------------------------
We must manually open the Keystore, before any TDE Master encryption key can be created or accessed in the Keystore i.e. for the database to utilize TDE, the Keystore must be opened in the database.
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY testdb1 ;
keystore altered.
check the STATUS of the Keystore as shown below.
SQL> set lines 200 pages 200
SQL> col WRL_TYPE for a15
SQL> col WRL_PARAMETER for a50
SQL> select * from V_$ENCRYPTION_WALLET;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
--------------- -------------------------------------------------- ------------------------------ -------------------- --------- --------- ----------
ASM +DATA/testdb/WALLET/ OPEN_NO_MASTER_KEY PASSWORD SINGLE UNDEFINED 0
Set Autologin
---------------------
SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '+DATA/testdb/WALLET/' IDENTIFIED BY testdb1;
keystore altered.
SQL> select * from V_$ENCRYPTION_WALLET;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
--------------- -------------------------------------------------- ------------------------------ -------------------- --------- --------- ----------
ASM +DATA/testdb/WALLET/ OPEN_NO_MASTER_KEY PASSWORD SINGLE UNDEFINED 0
NOTE: as there is no master key created yet ,the status is showing as OPEN_NO_MASTER_KEY .
Step 5: Create TDE Master Encryption Key:
-------------------------------------------------------------
TDE Master Encryption Key is stored in the Keystore. Once the Keystore is created and opened,we can create the TDE Master Encryption Key for the database
SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY testdb1 WITH BACKUP;
keystore altered.
SQL> select CON_ID,KEY_ID,KEYSTORE_TYPE,CREATOR_DBNAME,CREATOR_PDBNAME from v$encryption_keys;
CON_ID
----------
KEY_ID
------------------------------------------------------------------------------
KEYSTORE_TYPE CREATOR_DBNAME CREATOR_PDBNAME
----------------- ------------------------------ ------------------------------
0
ARZvm930eU+mv22FRqWrGkQAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
SOFTWARE KEYSTORE sdb
the STATUS of the Keystore also gets changed from OPEN_NO_MASTER_KEY to OPEN
SQL> select * from V_$ENCRYPTION_WALLET;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
--------------- -------------------------------------------------- ------------------------------ -------------------- --------- --------- ----------
ASM +DATA/testdb/WALLET/ OPEN PASSWORD SINGLE UNDEFINED 0
Step 6: Encrypt the Data:
--------------------------------------
Once the Keystore is created and opened with an active Master Encryption Key, we are all set to start encrypting the data.
We can either ENCRYPT individual table columns in the database or ENCRYPT a entire tablespace.
Encrypt Table columns:
We will create a test table and encrypt one column of the table .
SQL> CREATE USER testtde identified by testtde ;
User created.
SQL> GRANT CREATE SESSION TO testtde ;
Grant succeeded.
SQL> GRANT connect to testtde ;
Grant succeeded.
SQL> create table testtde.test_ts ( name varchar (20),
id number(10) ENCRYPT,
contact number(10)) ;
Table created.
SQL> col OWNER for a30
SQL> col TABLE_NAME for a30
SQL> col COLUMN_NAME for a30
SQL> Select OWNER ,TABLE_NAME ,COLUMN_NAME ,ENCRYPTION_ALG from dba_tabes where TABLE_NAME =upper('test_ts') ;
OWNER TABLE_NAME COLUMN_NAME ENCRYPTION_ALG
------------------------------ ------------------------------ ------------------------------ -----------------------------
TESTTDE TEST_TS ID AES 192 bits key
Encrypt Tablespace:
-----------------------------
We can encrypt a tablespace using TDE while creating it using CREATE TABLESPACE statement by means of ENCRYPTION clause.
SQL> create tablespace enc_data datafile '+DATA/' size 10M ENCRYPTION USING 'AES256' default storage (ENCRYPT) ;
Tablespace created.
SQL> SELECT tablespace_name, encrypted FROM dba_tablespaces where tablespace_name =UPPER ('enc_data') ;
TABLESPACE_NAME ENC
------------------------------ ---
ENC_DATA YES
Now we will see how database will behave when TDE is implemented and the wallet is not open!!!
SQL> CREATE TABLE testtde.newtestcase (
2 empno NUMBER(10),
3 ename VARCHAR2(50)
4 )TABLESPACE ENC_DATA;
Table created.
SQL> CREATE INDEX newtestcase_idx on testtde.newtestcase(empno) TABLESPACE ENC_DATA ;
Index created.
SQL> insert into testtde.newtestcase VALUES (10, 'This is secret data');
1 row created.
SQL> select * from testtde.newtestcase ;
EMPNO ENAME
---------- --------------------------------------------------
10 This is secret data
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE close IDENTIFIED BY testdb1;
keystore altered.
SQL> select * from testtde.newtestcase ;
select * from testtde.newtestcase
*
ERROR at line 1:
ORA-28365: wallet is not open
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE open IDENTIFIED BY testdb1;
keystore altered.
SQL> select * from testtde.newtestcase ;
EMPNO ENAME
---------- --------------------------------------------------
10 This is secret data