Oracle has introduced TDE from 10g and has come through various enhancements till 12c release.
Encryption requires at least two things: an encryption key and an algorithm. TDE uses what is known as a two-tier key architecture: both column and tablespace encryption keys are stored in the database but are encrypted with another key called the master key . The master key is stored outside the database in a special container called an external security module , which can be something as easy to set up as an Oracle wallet or as sophisticated as a hardware security module device. The Oracle wallet is a file formatted according to Public Key Cryptography Standard No. 12 and encrypted with a password. For using the wallet as the external security module, a password must be provided to make the master key accessible to the database. Unless the right password is supplied, the wallet can’t be opened and the encrypted data can’t be retrieved. The wallet is automatically closed when the database instance is shut down and must be reopened by a security officer when the instance starts. So although thieves might be able to restore a database from tapes, without the wallet and the password, they will not be able to view the encrypted data. (In the hardware security module case, the hardware device must be made available to the database in a manner specified by the vendor of the device.)
Tablespace Encryption Setup
Let’s look at how to set up TDE tablespace encryption, using a file-based wallet. Note that the compatibility of the database must be set to 11.1 or higher. First, if you don’t have one, create the wallet:
1. Make sure the ORACLE_BASE variable has been set. If it has not, set it by issuing
$ export ORACLE_BASE=/opt/oracle
2. Change to the ORACLE_BASE directory and then to the admin subdirectory for that instance. In my case, the instance is named prolin1, so I issue
$ cd $ORACLE_BASE/admin/prolin1
3. Create a directory called “wallet” to hold the wallet:
$ mkdir wallet
4. Create the wallet, along with a secure password, preferably containing a mix of alphanumeric characters, such as “T45rustMe54”:
$ sqlplus / as sysdba
SQL> alter system set encryption key
identified by "T45rustMe54";
The password is case-sensitive.
The preceding step will create the wallet as well as open it. You need to create the wallet only once. After the database is opened, the wallet remains open until either the wallet is explicitly closed or the database is shut down, at which time the wallet automatically closes. You can reopen this wallet after the database is restarted, by using
SQL> alter system set wallet open
identified by "T45rustMe54";
System altered.
Now that the wallet is set up, you can create the encrypted tablespace.
1. The following code sets up an encrypted tablespace named enc128_ts:
create tablespace enc128_ts
datafile '/u01/oracle/database/
enc128_ts.dbf'
size 1M autoextend on next 1M
encryption using 'AES128'
default storage (encrypt)
/
Note the special encryption using 'AES128’ clause, which indicates that the AES algorithm is to be used with a 128-bit key. You can also use the values AES192 and AES256 (in place of AES128, the default value) to use 192- and 256-bit keys, respectively.
2. Once the tablespace is created, you can create objects in it. For instance, the following code creates a table called ACCOUNTS_ENC:
create table accounts_enc (
ACC_NO NUMBER NOT NULL,
FIRST_NAME VARCHAR2(30) NOT NULL,
... other columns ...
)
tablespace enc128_ts;
That’s it; no special clause is required. All the columns of the table (or anything else created in this tablespace) will be encrypted.
-------------
Steps to Configure Transparent Data Encryption (TDE)
Lets walk through the step by step process for implementing Transparent Data Encryption (TDE) in Oracle Database 12c.
Demonstration (step 1):
Configure Key store location in database for Oracle 12c Setup :
[oracle@labserver ~]$ echo $ORACLE_SID
prodcdb
[oracle@labserver ~]$ cd $ORACLE_HOME/network/admin
[oracle@labserver admin]$ pwd
/app/oracle/db/12.1.0.1/network/admin
[oracle@labserver admin]$ vi sqlnet.ora (append the following lines in SQLNET.ORA)
ENCRYPTION_WALLET_LOCATION=
(SOURCE=
(METHOD=FILE)
(METHOD_DATA=
(DIRECTORY=+DATA/PRODCDB/WALLET)
)
)
ENCRYPTION_WALLET_LOCATION.
[oracle@labserver ~]$ echo $ORACLE_SID
+ASM
[oracle@labserver ~]$ asmcmd
ASMCMD>
ASMCMD> cd +DATA/PRODCDB
ASMCMD> mkdir WALLET
ASMCMD> cd WALLET/
ASMCMD> pwd
+DATA/PRODCDB/WALLET
Demonstration (step 2):
sys@PRODCDB> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '+DATA/PRODCDB/WALLET' IDENTIFIED BY encWallet;
keystore altered.
Once we create the Keystore from the database, we can see the file 'ewallet.p12' gets created in the Keystore Location.
ASMCMD> pwd
+DATA/PRODCDB/WALLET
ASMCMD> ls -l
Type Redund Striped Time Sys Name
KEY_STORE MIRROR COARSE MAR 28 15:00:00 N ewallet.p12 => +DATA/PRODCDB/KEY_STORE/ewallet.338.875546829
ASMCMD>
Demonstration (step 3):
Open the Keystore:
sys@PRODCDB> show con_name
CON_NAME
------------------------------
CDB$ROOT
sys@PRODCDB> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY encWallet;
keystore altered.
We can optionally query the V_$ENCRYPTION_WALLET view to check the STATUS of the Keystore as shown below.
sys@PRODCDB> select * from V_$ENCRYPTION_WALLET;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
---------- ------------------------- ------------------------- --------------- --------- --------- ----------
ASM +DATA/PRODCDB/WALLET OPEN_NO_MASTER_KEY PASSWORD SINGLE UNDEFINED 0
To enable the Keystore to open automatically, use the following command.
Demonstration (Enable Auto Login for keystore):
Here, I am enabling Auto-Login for the Keystore defined for my Oracle 12c CDB database 'prodcdb'.
sys@PRODCDB> show con_name
CON_NAME
------------------------------
CDB$ROOT
sys@PRODCDB> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '+DATA/PRODCDB/WALLET' IDENTIFIED BY encWallet;
keystore altered.
Once, we enable the Auto-Login for the Keystore, we can see a new file 'cwallet.sso' gets created in the Keystore location.
[oracle@labserver ~]$ asmcmd
ASMCMD> cd +DATA/PRODCDB/WALLET
ASMCMD> ls -l
Type Redund Striped Time Sys Name
AUTOLOGIN_KEY_STORE MIRROR COARSE --- -- 08:00:00 N cwallet.sso => +DATA/PRODCDB/AUTOLOGIN_KEY_STORE/cwallet
We can also observe that WALLET_TYPE is set to AUTOLOGIN when querying V$ENCRYPTION_WALLET view.
sys@PRODCDB> select * from v$encryption_wallet;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
-------------------- ------------------------- ---------- -------------------- --------- --------- ----------
ASM +DATA/PRODCDB/WALLET OPEN AUTOLOGIN SINGLE NO 0
sys@PRODCDB> select * from v$encryption_wallet;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
-------------------- ------------------------- -------------------- -------------------- --------- --------- ----------
ASM +DATA/PRODCDB/WALLET OPEN_NO_MASTER_KEY AUTOLOGIN SINGLE UNDEFINED 0
Demonstration (step 4):
Activating the Master Encryption Key :
sys@PRODCDB> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY encWallet WITH BACKUP;
keystore altered.
Once a Master Encryption Key is created, we can query the V_$ENCRYPTION_KEYS to check the status of key as follows.
sys@PRODCDB> select CON_ID,KEY_ID,KEYSTORE_TYPE,CREATOR_DBNAME,CREATOR_PDBNAME from v$encryption_keys;
CON_ID KEY_ID KEYSTORE_TYPE CREATOR_DBNAME CREATOR_PDBNAME
---------- ----------------------------------------------------- ----------------- --------------- ---------------
0 AcxA2N2N5k/Wv80Sy7NkDHkAAAAAAAAAAAAAAAAAAAAAAAAAAAAA SOFTWARE KEYSTORE prodcdb CDB$ROOT
Once the Master Encryption Key is created, the STATUS of the Keystore also gets changed from OPEN_NO_MASTER_KEY to OPEN as shown below.
sys@PRODCDB> select * from V_$ENCRYPTION_WALLET;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
---------- ------------------------- ------------------------- --------------- --------- --------- ----------
ASM +DATA/PRODCDB/WALLET OPEN PASSWORD SINGLE NO 0
Step 5: Encrypt the Tablespace:
Once the Keystore is created and opened with an active Master Encryption Key, we are all set to start encrypting the data. We can either ENCRYPT individual table columns in the database or ENCRYPT a entire tablespace.
Encrypt Tablespace:
We can encrypt a tablespace using TDE while creating it using CREATE TABLESPACE statement by means of ENCRYPTION clause.
Demonstration (Encrypt Tablespace):
sys@PRODCDB> create tablespace enc_data
2 datafile '+DATA'
3 encryption
4 default storage (ENCRYPT)
5 ;
Tablespace created.
TDE Encryption Algorithms:
By default, TDE uses the AES encryption algorithm with a 192-bit key length (AES192). If we encrypt a table column without specifying a encryption algorithm, then the column is encrypted using the AES192 algorithm. TDE also enables us to specify a non-default encryption algorithm. We can choose from one of the following encryption algorithms for TDE:
3DES168
AES128
AES192 (default)
AES256
Restrictions using TDE:
Restrictions on Data Types:
Transparent Data Encryption (TDE) supports the following list of data types.
BINARY_DOUBLE
BINARY_FLOAT
CHAR
DATE
INTERVAL DAY TO SECOND
INTERVAL YEAR TO MONTH
NCHAR
NUMBER
NVARCHAR2
RAW (legacy or extended)
TIMESTAMP (includes TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE)
VARCHAR2 (legacy or extended)
Encryption requires at least two things: an encryption key and an algorithm. TDE uses what is known as a two-tier key architecture: both column and tablespace encryption keys are stored in the database but are encrypted with another key called the master key . The master key is stored outside the database in a special container called an external security module , which can be something as easy to set up as an Oracle wallet or as sophisticated as a hardware security module device. The Oracle wallet is a file formatted according to Public Key Cryptography Standard No. 12 and encrypted with a password. For using the wallet as the external security module, a password must be provided to make the master key accessible to the database. Unless the right password is supplied, the wallet can’t be opened and the encrypted data can’t be retrieved. The wallet is automatically closed when the database instance is shut down and must be reopened by a security officer when the instance starts. So although thieves might be able to restore a database from tapes, without the wallet and the password, they will not be able to view the encrypted data. (In the hardware security module case, the hardware device must be made available to the database in a manner specified by the vendor of the device.)
Tablespace Encryption Setup
Let’s look at how to set up TDE tablespace encryption, using a file-based wallet. Note that the compatibility of the database must be set to 11.1 or higher. First, if you don’t have one, create the wallet:
1. Make sure the ORACLE_BASE variable has been set. If it has not, set it by issuing
$ export ORACLE_BASE=/opt/oracle
2. Change to the ORACLE_BASE directory and then to the admin subdirectory for that instance. In my case, the instance is named prolin1, so I issue
$ cd $ORACLE_BASE/admin/prolin1
3. Create a directory called “wallet” to hold the wallet:
$ mkdir wallet
4. Create the wallet, along with a secure password, preferably containing a mix of alphanumeric characters, such as “T45rustMe54”:
$ sqlplus / as sysdba
SQL> alter system set encryption key
identified by "T45rustMe54";
The password is case-sensitive.
The preceding step will create the wallet as well as open it. You need to create the wallet only once. After the database is opened, the wallet remains open until either the wallet is explicitly closed or the database is shut down, at which time the wallet automatically closes. You can reopen this wallet after the database is restarted, by using
SQL> alter system set wallet open
identified by "T45rustMe54";
System altered.
Now that the wallet is set up, you can create the encrypted tablespace.
1. The following code sets up an encrypted tablespace named enc128_ts:
create tablespace enc128_ts
datafile '/u01/oracle/database/
enc128_ts.dbf'
size 1M autoextend on next 1M
encryption using 'AES128'
default storage (encrypt)
/
Note the special encryption using 'AES128’ clause, which indicates that the AES algorithm is to be used with a 128-bit key. You can also use the values AES192 and AES256 (in place of AES128, the default value) to use 192- and 256-bit keys, respectively.
2. Once the tablespace is created, you can create objects in it. For instance, the following code creates a table called ACCOUNTS_ENC:
create table accounts_enc (
ACC_NO NUMBER NOT NULL,
FIRST_NAME VARCHAR2(30) NOT NULL,
... other columns ...
)
tablespace enc128_ts;
That’s it; no special clause is required. All the columns of the table (or anything else created in this tablespace) will be encrypted.
-------------
Steps to Configure Transparent Data Encryption (TDE)
Lets walk through the step by step process for implementing Transparent Data Encryption (TDE) in Oracle Database 12c.
Demonstration (step 1):
Configure Key store location in database for Oracle 12c Setup :
[oracle@labserver ~]$ echo $ORACLE_SID
prodcdb
[oracle@labserver ~]$ cd $ORACLE_HOME/network/admin
[oracle@labserver admin]$ pwd
/app/oracle/db/12.1.0.1/network/admin
[oracle@labserver admin]$ vi sqlnet.ora (append the following lines in SQLNET.ORA)
ENCRYPTION_WALLET_LOCATION=
(SOURCE=
(METHOD=FILE)
(METHOD_DATA=
(DIRECTORY=+DATA/PRODCDB/WALLET)
)
)
ENCRYPTION_WALLET_LOCATION.
[oracle@labserver ~]$ echo $ORACLE_SID
+ASM
[oracle@labserver ~]$ asmcmd
ASMCMD>
ASMCMD> cd +DATA/PRODCDB
ASMCMD> mkdir WALLET
ASMCMD> cd WALLET/
ASMCMD> pwd
+DATA/PRODCDB/WALLET
Demonstration (step 2):
sys@PRODCDB> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '+DATA/PRODCDB/WALLET' IDENTIFIED BY encWallet;
keystore altered.
Once we create the Keystore from the database, we can see the file 'ewallet.p12' gets created in the Keystore Location.
ASMCMD> pwd
+DATA/PRODCDB/WALLET
ASMCMD> ls -l
Type Redund Striped Time Sys Name
KEY_STORE MIRROR COARSE MAR 28 15:00:00 N ewallet.p12 => +DATA/PRODCDB/KEY_STORE/ewallet.338.875546829
ASMCMD>
Demonstration (step 3):
Open the Keystore:
sys@PRODCDB> show con_name
CON_NAME
------------------------------
CDB$ROOT
sys@PRODCDB> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY encWallet;
keystore altered.
We can optionally query the V_$ENCRYPTION_WALLET view to check the STATUS of the Keystore as shown below.
sys@PRODCDB> select * from V_$ENCRYPTION_WALLET;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
---------- ------------------------- ------------------------- --------------- --------- --------- ----------
ASM +DATA/PRODCDB/WALLET OPEN_NO_MASTER_KEY PASSWORD SINGLE UNDEFINED 0
To enable the Keystore to open automatically, use the following command.
Demonstration (Enable Auto Login for keystore):
Here, I am enabling Auto-Login for the Keystore defined for my Oracle 12c CDB database 'prodcdb'.
sys@PRODCDB> show con_name
CON_NAME
------------------------------
CDB$ROOT
sys@PRODCDB> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '+DATA/PRODCDB/WALLET' IDENTIFIED BY encWallet;
keystore altered.
Once, we enable the Auto-Login for the Keystore, we can see a new file 'cwallet.sso' gets created in the Keystore location.
[oracle@labserver ~]$ asmcmd
ASMCMD> cd +DATA/PRODCDB/WALLET
ASMCMD> ls -l
Type Redund Striped Time Sys Name
AUTOLOGIN_KEY_STORE MIRROR COARSE --- -- 08:00:00 N cwallet.sso => +DATA/PRODCDB/AUTOLOGIN_KEY_STORE/cwallet
We can also observe that WALLET_TYPE is set to AUTOLOGIN when querying V$ENCRYPTION_WALLET view.
sys@PRODCDB> select * from v$encryption_wallet;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
-------------------- ------------------------- ---------- -------------------- --------- --------- ----------
ASM +DATA/PRODCDB/WALLET OPEN AUTOLOGIN SINGLE NO 0
sys@PRODCDB> select * from v$encryption_wallet;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
-------------------- ------------------------- -------------------- -------------------- --------- --------- ----------
ASM +DATA/PRODCDB/WALLET OPEN_NO_MASTER_KEY AUTOLOGIN SINGLE UNDEFINED 0
Demonstration (step 4):
Activating the Master Encryption Key :
sys@PRODCDB> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY encWallet WITH BACKUP;
keystore altered.
Once a Master Encryption Key is created, we can query the V_$ENCRYPTION_KEYS to check the status of key as follows.
sys@PRODCDB> select CON_ID,KEY_ID,KEYSTORE_TYPE,CREATOR_DBNAME,CREATOR_PDBNAME from v$encryption_keys;
CON_ID KEY_ID KEYSTORE_TYPE CREATOR_DBNAME CREATOR_PDBNAME
---------- ----------------------------------------------------- ----------------- --------------- ---------------
0 AcxA2N2N5k/Wv80Sy7NkDHkAAAAAAAAAAAAAAAAAAAAAAAAAAAAA SOFTWARE KEYSTORE prodcdb CDB$ROOT
Once the Master Encryption Key is created, the STATUS of the Keystore also gets changed from OPEN_NO_MASTER_KEY to OPEN as shown below.
sys@PRODCDB> select * from V_$ENCRYPTION_WALLET;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
---------- ------------------------- ------------------------- --------------- --------- --------- ----------
ASM +DATA/PRODCDB/WALLET OPEN PASSWORD SINGLE NO 0
Step 5: Encrypt the Tablespace:
Once the Keystore is created and opened with an active Master Encryption Key, we are all set to start encrypting the data. We can either ENCRYPT individual table columns in the database or ENCRYPT a entire tablespace.
Encrypt Tablespace:
We can encrypt a tablespace using TDE while creating it using CREATE TABLESPACE statement by means of ENCRYPTION clause.
Demonstration (Encrypt Tablespace):
sys@PRODCDB> create tablespace enc_data
2 datafile '+DATA'
3 encryption
4 default storage (ENCRYPT)
5 ;
Tablespace created.
TDE Encryption Algorithms:
By default, TDE uses the AES encryption algorithm with a 192-bit key length (AES192). If we encrypt a table column without specifying a encryption algorithm, then the column is encrypted using the AES192 algorithm. TDE also enables us to specify a non-default encryption algorithm. We can choose from one of the following encryption algorithms for TDE:
3DES168
AES128
AES192 (default)
AES256
Restrictions using TDE:
Restrictions on Data Types:
Transparent Data Encryption (TDE) supports the following list of data types.
BINARY_DOUBLE
BINARY_FLOAT
CHAR
DATE
INTERVAL DAY TO SECOND
INTERVAL YEAR TO MONTH
NCHAR
NUMBER
NVARCHAR2
RAW (legacy or extended)
TIMESTAMP (includes TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE)
VARCHAR2 (legacy or extended)
