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;
Thx man, u r z best 😊
My requirement is column level encryption and followed all the steps as you have shown in Oracle 19C. But when I do select * from table. I see data in the column.. I mean not encrypted. is there something I missing to understand?
The data is encrypted at the file level.
Can you please explain how column value is decrypted from a record in table and display the actual value to front end application?
All the encryption is done at the files level, transparent for the application.
Hi man,
Could you add some more instructions on how the existence of a physical standby, alters the procedure?
Sorry, but I don’t have such an environment now to do it…