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 |
| Database | Oracle 21c |
| PDB's | YES |
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/walletStep 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/tdeNow 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: