Step by step TDE setup in oracle 12c RAC

Updated: Apr 30




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

831 views0 comments

Recent Posts

See All

#oracle #ASM #controlfile #multiplexing #oracle19c It is a best practice for any database to have controlfile as well as redolog file to be multiplexed . This helps during any disk failure to keep one

In the previous post we saw how to upgrade OMS from 13.2 to 13.4 version .(Click Here) Once OMS is upgraded we have to upgrade the agents running in the servers which are monitored by OEM . This whole