fbpx

Oracle TDE implementation

In this article, we are going to learn about Oracle TDE implementation. The TDE full form is transparent data encryption.

What is TDE implementation?

Which is used to encrypt the sensitive data at table level and tablespace level also. Once TDE is configured on the data, only the authorized users can access this data. Transparent data encryption helps us to protect our data from being stolen.

Environment for this practice

In this practice, we are using the listed below environment:

Operating system (OS)Oracle Linux 7
DatabaseOracle 21c
PDB’sYES

Step 1: Start database and Check TDE status

Using the below commands, check the current status of TDE.

$ sqlplus / as sysdba

SQL*Plus: Release 21.0.0.0.0 - Production on Sat Dec 4 12:09:28 2021
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1577057552 bytes
Fixed Size                  9686288 bytes
Variable Size             922746880 bytes
Database Buffers          637534208 bytes
Redo Buffers                7090176 bytes
Database mounted.
Database opened.


SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORA21CPDB1                     MOUNTED NO



SQL> ALTER PLUGGABLE DATABASE ORA21CPDB1 open;

Pluggable database altered.


SQL> ALTER PLUGGABLE DATABASE ORA21CPDB1 SAVE STATE;

Pluggable database altered.



SQL> set lines 300
     col name for a15
     col wrl_type for a10
     col status for a30

SQL> select p.con_id, p.name, p.open_mode, ew.wrl_type, ew.wallet_type, ew.status 
     from v$pdbs p join v$encryption_wallet ew on (ew.con_id = p.con_id)
     order by p.con_id;


    CON_ID NAME            OPEN_MODE  WRL_TYPE   WALLET_TYPE          STATUS
---------- --------------- ---------- ---------- -------------------- ------------------------------
         2 PDB$SEED        READ ONLY  FILE       UNKNOWN              NOT_AVAILABLE
         3 ORA21CPDB1      READ WRITE FILE       UNKNOWN              NOT_AVAILABLE

As you can see in the wallet_type column value is unknown, which means the wallet is not configured yet.

Step 2: Create directory for TDE

We need to create a directory for Keystore inside the ORACLE_BASE location.

$mkdir $ORACLE_BASE/admin/ora21c/wallet

Step 3: Set keystore location

In the previous version, we need to define ENCRYPTION_WALLET_LOCATION inside sqlnet.ora but the sqlnet parameter are deprecated in 18c. So, instead of sqlnet, we are going to use the new parameters WALLET_ROOT and TDE CONFIGURATION.

SQL> alter system set wallet_root='$ORACLE_BASE/admin/ora21c/wallet' scope=spfile;

System altered.


SQL> shutdown immediate;
SQL> startup;


SQL> alter system set tde_configuration='KEYSTORE_CONFIGURATION=FILE' scope=both;

System altered.

Step 4: Create password protected keystore

With the WALLET_ROOT parameter, the wallet will be stored in subdirectory name tde. If the directory does not exist inside the wallet must be created manually.

$mkdir $ORACLE_BASE/admin/ora21c/wallet/tde

Now create the Keystore with a password.

SQL> administer key management
     create keystore '$ORACLE_BASE/admin/ora21c/wallet/tde' identified by Ocptechno21c;

keystore altered.

Now we have a wallet, but its status is closed. Check the below output.

SQL>
 set lines 300
     col name for a15
     col wrl_type for a10
     col status for a30

SQL> select p.con_id, p.name, p.open_mode, ew.wrl_type, ew.wallet_type, ew.status
     from v$pdbs p join v$encryption_wallet ew on (ew.con_id = p.con_id)
     order by p.con_id;

    CON_ID NAME            OPEN_MODE  WRL_TYPE   WALLET_TYPE          STATUS
---------- --------------- ---------- ---------- -------------------- ------------------------------
         2 PDB$SEED        READ ONLY  FILE       UNKNOWN              CLOSED
         3 ORA21CPDB1      READ WRITE FILE       UNKNOWN              CLOSED

Step 5: Open wallet

Using the below command we open the wallet.

SQL> administer key management set keystore open identified by Ocptechno21c container=ALL;

keystore altered.

Check wallet status again.

SQL> set lines 300
     col name for a15
     col wrl_type for a10
     col status for a30


SQL> select p.con_id, p.name, p.open_mode, ew.wrl_type, ew.wallet_type, ew.status
     from v$pdbs p join v$encryption_wallet ew on (ew.con_id = p.con_id)
     order by p.con_id;

    CON_ID NAME            OPEN_MODE  WRL_TYPE   WALLET_TYPE          STATUS
---------- --------------- ---------- ---------- -------------------- ------------------------------
         2 PDB$SEED        READ ONLY  FILE       PASSWORD             OPEN_NO_MASTER_KEY
         3 ORA21CPDB1      READ WRITE FILE       PASSWORD             OPEN_NO_MASTER_KEY

Now the status= OPEN_NO_MASTER_KEY, the wallet is open but doesn’t have a master key. We need to set the master key for all PDB’s.

Step 6: Set Master key for All PDB’s

Check the key column status in the wallet.

SQL> set lines 300
     col name for a10
     col key_id for a60

SQL> select p.con_id, p.name, p.open_mode, ek.key_id
     from v$pdbs p left outer join v$encryption_keys ek on (ek.con_id = p.con_id)
     order by p.con_id;


    CON_ID NAME       OPEN_MODE  KEY_ID
---------- ---------- ---------- ------------------------------------------------------------
         2 PDB$SEED   READ ONLY
         3 ORA21CPDB1 READ WRITE

The key_id column value is empty.

SQL> administer key management set key identified by Ocptechno21c with backup container=ALL;

keystore altered.

Check the key_id column value again. This time you will see the value.

SQL> set lines 300
     col name for a10
     col key_id for a60

SQL> select p.con_id, p.name, p.open_mode, ek.key_id
     from v$pdbs p left outer join v$encryption_keys ek on (ek.con_id = p.con_id)
     order by p.con_id;


    CON_ID NAME       OPEN_MODE  KEY_ID
---------- ---------- ---------- ------------------------------------------------------------
         2 PDB$SEED   READ ONLY
         3 ORA21CPDB1 READ WRITE AYYvB4MZGk/4v3xsIUhnfB4AAAAAAAAAAAAAAAAAAAAAAAAAAAAA

Step 7: Create tablespace with encryption

We successfully configured the TDE, now it’s time to create encrypted tablespace. You can use any existing tablespace also.

SQL> alter session set container=ORA21CPDB1;

Session altered.


SQL> create tablespace wall_enc datafile '/u01/app/oracle/oradata/ORA21C/ora21cpdb1/wall_enc01.dbf' 
     size 100m encryption using 'AES128' default storage(encrypt);

Tablespace created.

Check tablespace is encrypted or not.

SQL> select t.name, e.encryptedts, e.encryptionalg
 from v$encrypted_tablespaces e join v$tablespace t 
     on (e.ts# = t.ts#)
     order by t.name;

NAME                                                              ENC ENCRYPT
----------------------------------------------------------------- --- -------
WALL_ENC                                                          YES AES128

Create a table inside this encrypted tablespace and insert a few records in it.

SQL> create table ocp_enc (id number) tablespace wall_enc;

Table created.


SQL> insert into ocp_enc values (10);

1 row created.


SQL> commit;



SQL> select * from ocp_enc;

        ID
----------
        10

Step 8: Restart Instance

Restart the database and try to access the table which we created in step 7.

SQL> alter session set container=CDB$ROOT;

Session altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORA21CPDB1                     READ WRITE NO
SQL>
SQL>
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL>
SQL> startup
ORACLE instance started.

Total System Global Area 1577057552 bytes
Fixed Size                  9686288 bytes
Variable Size             956301312 bytes
Database Buffers          603979776 bytes
Redo Buffers                7090176 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL> alter session set container=ORA21CPDB1;


Session altered.

SQL>
SQL>
SQL> select * from ocp_enc;
select * from ocp_enc
              *
ERROR at line 1:
ORA-28365: wallet is not open

This time you received the error ORA-28365: wallet is not open, so let’s check the wallet status.

SQL> set lines 300
     col name for a15
     col wrl_type for a10
     col status for a30

SQL> select p.con_id, p.name, p.open_mode, ew.wrl_type, ew.wallet_type, ew.status
     from v$pdbs p join v$encryption_wallet ew on (ew.con_id = p.con_id) order by p.con_id;

    CON_ID NAME            OPEN_MODE  WRL_TYPE   WALLET_TYPE          STATUS
---------- --------------- ---------- ---------- -------------------- ------------------------------
         3 ORA21CPDB1      READ WRITE FILE       UNKNOWN              CLOSED

If the database instance is down then the wallet is automatically closed, and you can not access the data unless you open the wallet.

SQL> alter session set container=cdb$root;

Session altered.

SQL>
SQL>
SQL> administer key management set keystore open identified by Ocptechno21c container=ALL;

keystore altered.

SQL>
SQL>
SQL> set lines 300
     col name for a15
     col wrl_type for a10
     col status for a30

SQL> select p.con_id, p.name, p.open_mode, ew.wrl_type, ew.wallet_type, ew.status
     from v$pdbs p join v$encryption_wallet ew on (ew.con_id = p.con_id) order by p.con_id;

    CON_ID NAME            OPEN_MODE  WRL_TYPE   WALLET_TYPE          STATUS
---------- --------------- ---------- ---------- -------------------- ------------------------------
         2 PDB$SEED        READ ONLY  FILE       PASSWORD             OPEN
         3 ORA21CPDB1      READ WRITE FILE       PASSWORD             OPEN

SQL>
SQL>
SQL> alter session set container=ORA21CPDB1;

Session altered.



SQL> select * from ocp_enc;

        ID
----------
        10

Step 9: Auto login keystore

To avoid the step 8 situation, we can create an auto-login Keystore. The wallet is open automatically after instance restart.

SQL> alter session set container=cdb$root;

Session altered.


SQL> administer key management create auto_login keystore from keystore   
    '$ORACLE_BASE/admin/ora21c/wallet/tde' identified by Ocptechno21c;  

keystore altered.

Now check the wallet status:

SQL> set lines 300
     col name for a15
     col wrl_type for a10
     col status for a30

SQL> select p.con_id, p.name, p.open_mode, ew.wrl_type, ew.wallet_type, ew.status from v$pdbs p join     
     v$encryption_wallet ew on (ew.con_id = p.con_id) order by p.con_id;

    CON_ID NAME            OPEN_MODE  WRL_TYPE   WALLET_TYPE          STATUS
---------- --------------- ---------- ---------- -------------------- ------------------------------
         2 PDB$SEED        READ ONLY  FILE       AUTOLOGIN            OPEN
         3 ORA21CPDB1      READ WRITE FILE       AUTOLOGIN            OPEN

Step 10: Close Password wallet and open the Auto login keystore

A close password wallet and the auto-login wallet will work.

SQL> administer key management set keystore close identified by Ocptechno21c container=ALL;

keystore altered.

You Must Read:

Leave a Comment