fbpx

Implementing Transparent Data Encryption in Oracle 19c Step by Step

Transparent Data Encryption (TDE) enables you to encrypt sensitive data that you store in tables and tablespaces.

After the data is encrypted, this data is transparently decrypted for authorized users or applications when they access this data. TDE helps protect data stored on media (also called data at rest) in the event that the storage media or data file is stolen.

In this guide I will show you how to implemente Oracle TDE on RAC, but you should be able to modify the procedure for a standalone database.

Check that the wallet_root is not set.

SQL> show parameter wallet_root

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
wallet_root                          string

Now make sure you have defined db_create_file_dest

SQL> show parameter db_create_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      +DATAC3

Set the wallet root

alter system set wallet_root='+DATAC3/LSG01' scope=spfile sid='*';

Reboot the DB for the changes to take effect

[oracle@xcm1iddb001 ~]$ srvctl stop database -d LSG01
[oracle@xcm1iddb001 ~]$ srvctl start database -d LSG01

Verify that the parameters have been set.

show parameter wallet_root

show parameter tde_configuration

Set the tde_configuration

alter system set tde_configuration="keystore_configuration=file" scope=both sid='*';

SQL> show parameter tde_configuration

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
tde_configuration                    string      keystore_configuration=file

Create the keystore

SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE IDENTIFIED BY BSLSG01;

keystore altered.

Open the keystore

-- with PDBs
SQL> ADMINISTER KEY MANAGEMENT set keystore open identified by BSLSG01 container=ALL;

keystore altered.

-- without PDBs
ADMINISTER KEY MANAGEMENT set keystore open identified by BSLSG01;

Set the key

-- with PDBs
SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY BSLSG01 with backup container=ALL;

keystore altered.

--without PDBs
ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY BSLSG01 with backup;

Enable auto login

-- auto login
SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE IDENTIFIED BY BSLSG01;

keystore altered.

set linesize 120
column STATUS format a20
column WRL_PARAMETER format a60
column MASTERKEY_ACTIVATED format a20
column WALLET_TYPE format a20

SQL> SELECT CON_ID, STATUS, WRL_PARAMETER,WALLET_TYPE FROM V$ENCRYPTION_WALLET;

    CON_ID STATUS               WRL_PARAMETER                                                WALLET_TYPE
---------- -------------------- ------------------------------------------------------------ --------------------
         1 OPEN                 +DATAC3/LSG01/tde/                                           PASSWORD
         2 OPEN                                                                              PASSWORD
         4 OPEN                                                                              PASSWORD

From the query above you can check that it is still not autologin.

You have to make it autologin.

Reboot the database and try again the query

[oracle@xcm1iddb001 ~]$ srvctl stop database -d LSG01
[oracle@xcm1iddb001 ~]$ srvctl start database -d LSG01

set linesize 120
column STATUS format a20
column WRL_PARAMETER format a60
column MASTERKEY_ACTIVATED format a20
column WALLET_TYPE format a20

SELECT CON_ID, STATUS, WRL_PARAMETER,WALLET_TYPE FROM V$ENCRYPTION_WALLET;

    CON_ID STATUS               WRL_PARAMETER                                                WALLET_TYPE
---------- -------------------- ------------------------------------------------------------ --------------------
         1 OPEN                 +DATAC3/LSG01/tde/                                           AUTOLOGIN
         2 OPEN                                                                              AUTOLOGIN
         4 OPEN                                                                              AUTOLOGIN

Enable encryption for new tablespaces

alter system set encrypt_new_tablespaces = always scope=both sid='*';

ALTER SYSTEM SET "_tablespace_encryption_default_algorithm"=AES256 scope=both sid='*';

Check for any encrypted tablespaces

select TABLESPACE_NAME, ENCRYPTED from dba_tablespaces where ENCRYPTED='YES';

select TABLESPACE_NAME, ENCRYPTED from dba_tablespaces order by 1;

Suppose you want to encrypt all the tablespaces of a schema.

Check on which tablespaces that schema has objects

SQL> select distinct tablespace_name 
from dba_segments where owner='SCHEMA1';

TABLESPACE_NAME
------------------------------
TS_SGRI_IDX
TS_SGRI_DAT

To encrypt a tablespace just use this command

alter tablespace TS_SGRI_DAT encryption online encrypt;

Do the same for any other tablespaces.

If you just want to encrypt a table column then you should use this command

ALTER TABLE schema.table MODIFY (column ENCRYPT);

Keep in mind that the table column encryption has a default encryption of AES192.

If you want to encrypt your tables with AES256 then you must specify the encryption type in the command as follows

ALTER TABLE schema.table MODIFY (column ENCRYPT USING 'AES256');

To check the columns that have been encrypted run this query

column table_name format a15;
column column_name format a15;
column encryption_alg format a20;

select table_name , column_name, encryption_alg  from dba_encrypted_columns;

Leave a Reply

Your email address will not be published.