Home » Oracle » Oracle Database » TDE encryption in Oracle 12c step by step

TDE encryption in Oracle 12c step by step

TDE stands for Transparent Data Encryption. We can encrypt both the tablespace and individual table columns using TDE.This article, we will see the step by step process for TDE encryption in oracle 12c

What is TDE (Transparent Data Encryption)

  • As the name suggests, TDE(Transparent Data Encryption) transparently encrypts data at rest in Oracle Databases. It stops unauthorized attempts by the operating system to access database data stored in files, without impacting how applications access the data using SQL. So we don’t have any impact on business. If the malicious user tries to open the file using a HEX editor (like UltraEdit), then only non-printable characters will be present. TDE can encrypt entire application tablespaces or specific sensitive columns.
  • TDE is fully integrated with the Oracle database. Encrypted data remains encrypted in the database, whether it is in tablespace storage files, temporary tablespaces, undo tablespaces, or other files that Oracle Database relies on such as redo logs. Also, TDE can encrypt entire database backups (RMAN) and Data Pump exports.

How is TDE licensed?

TDE is part of Oracle Advanced Security, which also includes Data Redaction. It is available as an additional licensed option for the Oracle Database Enterprise Edition. It is included, configured, and enabled by default in Oracle Autonomous Databases and Database Cloud Services.

TDE encryption in Oracle 12c step by step

We can enable TDE in both the CDB and non-CDB databases. Let’s take the steps for both CDB and non-CDB.

Non -CDB

(1) Before attempting to enable encryption, a wallet/keystore must be created to hold the encryption key. The search order for finding the wallet is as follows:

See also  How to Clone Oracle Application Server 10g

If present, the location specified by the ENCRYPTION_WALLET_LOCATION parameter in the sqlnet.ora file.
If present, the location specified by the WALLET_LOCATION parameter in the sqlnet.ora file.
The default location for the wallet. If the $ORACLE_BASE is set, this is “$ORACLE_BASE/admin/DB_UNIQUE_NAME/wallet”, otherwise it is “$ORACLE_HOME/admin/DB_UNIQUE_NAME/wallet”, where DB_UNIQUE_NAME comes from the initialization parameter file.
Although encrypted tablespaces can share the default database wallet, Oracle recommends you use a separate wallet for transparent data encryption functionality by specifying the ENCRYPTION_WALLET_LOCATION parameter in the sqlnet.ora file.

Let’s create a directory

mkdir -p /u01/app/oracle/admin/TEST/encryption_wallet/

Edit the “$ORACLE_HOME/network/admin/sqlnet.ora” files, adding the following entry.

ENCRYPTION_WALLET_LOCATION=
(SOURCE=(METHOD=FILE)(METHOD_DATA=
(DIRECTORY=/u01/app/oracle/admin/TEST/encryption_wallet/)))

This parameter can also be used to identify a Hardware Security Model (HSM) as the location for the wallet

(2) Now create the Keystore using the Administer Key Management command

ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '<keystore location' IDENTIFIED BY <password ;

ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/u01/app/oracle/admin/TEST/encryption_wallet/' IDENTIFIED BY testwallet01;

A file ewallet.p12 will get created if you check the directory

ls /u01/app/oracle/admin/TEST/encryption_wallet/
ewallet.p12

(3) Now, before using the Keystore, we need to open the keystore.
Here is the command to open and close it.

ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY testwallet01 ;

We can Close using the below command

ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY testwallet01;

(4) Now, before enabling encryption, we need to activate the master key. There are two ways to do it

(a) Generate the Master key using Single command

SQL>Administer key management set encryption key identified by testwallet01 with backup;
SQL> select key_id from v$encryption_keys;

(b)Generate the Master key using a two-step process.

SQL> administer key management create key identified by testwallet01 with backup;
SQL> select key_id from v$encryption_keys;
SQL> administer key management use key '<key id from previous step>' identified by testwallet01 with backup;

(5) We can check the information about the Keystore in V$ENCRYPTION_WALLET view.

SET LINESIZE 200
COLUMN wrl_parameter FORMAT A50
SELECT wrl_parameter,status FROM v$encryption_wallet;
WRL_PARAMETER                              STATUS
---------------------                      -------------
/u01/app/oracle/admin/TEST/encryption_wallet/ OPEN 
  1. Now we are all set to encrypt the table column
See also  Practice Oracle Cloud Infrastructure 1Z0 932 /1072 questions

Let’s create a tablespace

CREATE TABLESPACE encrypted_test_ts
DATAFILE '/u01/app/oracle/oradata/TEST/encrypted_test_ts01.dbf' SIZE 128K
AUTOEXTEND ON NEXT 64K
ENCRYPTION USING 'AES256'
DEFAULT STORAGE(ENCRYPT);

ALTER USER test QUOTA UNLIMITED ON encrypted_ts;

The ENCRYPTED column of the DBA_TABLESPACES and USER_TABLESPACES views indicates if the tablespace is encrypted or not.

SELECT tablespace_name, encrypted FROM dba_tablespaces where tablespace_name='ENCRYPTED_TEST_TS';
TABLESPACE_NAME ENC
--------------------------   ---------
ENCRYPTED_TEST_TS           YES

CDB

( 1) Before attempting to enable encryption, a wallet/keystore must be created to hold the encryption key. The search order for finding the wallet is as follows:

If present, the location specified by the ENCRYPTION_WALLET_LOCATION parameter in the sqlnet.ora file.
If present, the location specified by the WALLET_LOCATION parameter in the sqlnet.ora file.
The default location for the wallet. If the $ORACLE_BASE is set, this is “$ORACLE_BASE/admin/DB_UNIQUE_NAME/wallet”, otherwise it is “$ORACLE_HOME/admin/DB_UNIQUE_NAME/wallet”, where DB_UNIQUE_NAME comes from the initialization parameter file.
Although encrypted tablespaces can share the default database wallet, Oracle recommends you use a separate wallet for transparent data encryption functionality by specifying the ENCRYPTION_WALLET_LOCATION parameter in the sqlnet.ora file.

Let’s create a directory

mkdir -p /u01/app/oracle/admin/TEST/encryption_wallet/

Edit the “$ORACLE_HOME/network/admin/sqlnet.ora” files, adding the following entry.

ENCRYPTION_WALLET_LOCATION=
(SOURCE=(METHOD=FILE)(METHOD_DATA=
(DIRECTORY=/u01/app/oracle/admin/TEST/encryption_wallet/)))

This parameter can also be used to identify a Hardware Security Model (HSM) as the location for the wallet

(2) Now create the Keystore using the Administer Key Management command

ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '<keystore location>' IDENTIFIED BY <password>;
ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/u01/app/oracle/admin/TEST/encryption_wallet/' IDENTIFIED BY testwallet01;

A file ewallet.p12 will get created if you check the directory

ls /u01/app/oracle/admin/TEST/encryption_wallet/
ewallet.p12

(3) Now, before using the keystore, we need to open the keystore. Now with CDB, we either specify CONTAINER = ALL for the root container. Then this will open the keystore for all the PDB or this will open the keystore in the current container only.
Here is the command to open and close it

ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY testwallet01 CONTAINER=ALL ;

We can Close using the below command

ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY testwallet01 CONTAINER=ALL;

(4) Now before enabling encryption, we need to activate the Master key. Now either we can enable with CONTAINER=ALL then it will be generated for all the PDB. if you dont specify the container=ALL, then it will create for the current container only

Administer key management set encryption key identified by testwallet01 with backup CONTAINER=ALL;

(5) We can check the information about the keystore in V$ENCRYPTION_WALLET view.

SET LINESIZE 200
COLUMN wrl_parameter FORMAT A50
SELECT wrl_parameter,status FROM v$encryption_wallet;
WRL_PARAMETER STATUS
--------------------- -------------
/u01/app/oracle/admin/TEST/encryption_wallet/ OPEN

(6) Now we are all set to encrypt the table column

See also  ORA-00934 group function is not allowed here

Let’s create a tablespace

CREATE TABLESPACE encrypted_test_ts
DATAFILE '/u01/app/oracle/oradata/TEST/encrypted_test_ts01.dbf' SIZE 128K
AUTOEXTEND ON NEXT 64K
ENCRYPTION USING 'AES256'
DEFAULT STORAGE(ENCRYPT);

ALTER USER test QUOTA UNLIMITED ON encrypted_ts;

The ENCRYPTED column of the DBA_TABLESPACES and USER_TABLESPACES views indicates if the tablespace is encrypted or not.

SELECT tablespace_name, encrypted FROM dba_tablespaces where tablespace_name='ENCRYPTED_TEST_TS';
TABLESPACE_NAME ENC
--------------------------   ---------
ENCRYPTED_TEST_TS           YES

I hope you like this post on how to do TDE encryption in Oracle 12c step by step

Related Articles

How To Restore TDE Wallet Files From Backup in Oracle Database
how to check if oracle database is encrypted
TDE encryption in oracle 11g step by step
How To Export TDE Master Encryption Key
How to Configure Auto Login TDE Wallet

1 thought on “TDE encryption in Oracle 12c step by step”

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top