Oracle Advanced Security Transparent Data Encryption (TDE) enables you to encrypt individual columns or entire application tablespaces to safeguard sensitive data against unauthorized access from outside of the database environment. TDE transparently encrypts the data when it it written to disk and decrypts it when it is read back to an authorized user or application. The solution is transparent to applications because data is encrypted automatically when written to storage and decrypted when read from storage. Applications do not need to be modified to take advantage of this feature. TDE is licensed as part of Oracle Advanced Security option.
TDE provides a two-tier encryption key management architecture consisting of data encryption keys and master encryption keys. The master encryption keys are used to encrypt the data encryption keys, and the data encryption keys are used to encrypt the data at rest. The data encryption keys are managed automatically by TDE without user interaction whereas the master encryption keys are stored in a keystore that is outside of the database. The master encryption keys are managed using the operations shown on this page. The keystore can be Oracle Wallet (a standards-based PKCS12 file) or Oracle Key Vault (a centralized key management platform) or PKCS11 standards-based Hardware Security Module (HSM).
If you do not have sqlnet.ora file in your $ORACLE_HOME/network/admin folder you will receive this error message
So first we need to create sqlnet.ora file before proceeding further. To create it follow below steps
oracle@ggsource admin]$ ls -ltr
total 24
-rw-r--r--. 1 oracle oinstall 373 Oct 31 2013 shrept.lst
drwxr-sr-x. 2 oracle oinstall 64 Dec 16 16:24 samples
-rw-r--r-- 1 oracle oinstall 348 Apr 8 08:30 listener.ora
-rw-r--r-- 1 oracle oinstall 512 Apr 8 08:30 tnsnames.ora
-rw-r--r-- 1 oracle oinstall 348 Apr 8 08:33 listener2004088AM3320.bak
-rw-r--r-- 1 oracle oinstall 512 Apr 8 08:33 tnsnames2004088AM3320.bak
-rw-r--r-- 1 oracle oinstall 201 Apr 8 08:33 sqlnet.ora
Once you have sqlnet.ora you will be able to proceed to step 2. Select the default setting click next
Specify the types of Wallet(s) to create, the Wallet password, and a description for the initial key.
Review the changes to be made to the keystore and click finish
Your Keystore is now configured successfully.
Notice the changes in sqlnet.ora
New version
[oracle@ggsource admin]$ cat sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
ENCRYPTION_WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = /u01/app/oracle/admin/gclone1/wallet)
)
)
NAMES.DIRECTORY_PATH= (TNSNAMES, LDAP, EZCONNECT)
Earlier version
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
#SQLNET.ORA Network Configuration File
#Created by Oracle Enterprise Manager Clone Database tool
NAMES.DIRECTORY_PATH= (TNSNAMES, LDAP, EZCONNECT)
Now let’s try to do a demo and encrypt a table column
This is the table we want to encrypt and click edit
SYS@ gclone1>select * from scott.tde_demo;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
12 rows selected.
I want to encrypt salary column. So select the column and apply
Click on the job name to see the status. Our job is succeeded.
You can see the encrypted table is listed under tables with encrypted columns section.
Since the wallet status is open you can still query the encrypted data. But remember column is encrypted. It is transparent to applications because data is encrypted automatically when written to storage and decrypted when read from storage. Applications do not need to be modified to take advantage of this feature.
SYS@ gclone1>select * from scott.tde_demo;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
12 rows selected.
Let’s close the wallet keystore and see what happens
in order to open and close the key store you need to provide the wallet password you configured in above steps.
Now keystore is closed.
let’s try to query the table again. You can see that no one will be able to access the encrypted data and will receive wallet not open error message.
SYS@ gclone1>select * from scott.tde_demo;
select * from scott.tde_demo
*
ERROR at line 1:
ORA-28365: wallet is not open
So in order to take advantage of Transparent data encryption make sure your key store is always open. At database level also you can verify the status of the wallet. It is showing as closed.
SYS@ gclone1>col WRL_PARAMETER for a40
SYS@ gclone1>select * from V_$ENCRYPTION_WALLET;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
-------------------- ---------------------------------------- ------------------------------ -------------------- --------- --------- ----------
FILE /u01/app/oracle/admin/gclone1/wallet/ CLOSED UNKNOWN SINGLE UNDEFINED 0
You can open the wallet from oem by clicking this option
Or you can do the same through command prompt using below command
SYS@ gclone1>ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "System@123System";
System altered.
SYS@ gclone1>select * from V_$ENCRYPTION_WALLET;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
-------------------- ---------------------------------------- ------------------------------ -------------------- --------- --------- ----------
FILE /u01/app/oracle/admin/gclone1/wallet/ OPEN PASSWORD SINGLE NO 0
You can now query the table
SYS@ gclone1>select * from scott.tde_demo;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
12 rows selected.
Similarly you can close the wallet from command prompt if needed
SYS@ gclone1>ALTER SYSTEM SET ENCRYPTION WALLET CLOSE IDENTIFIED BY "System@123System";
System altered.
SYS@ gclone1>select * from V_$ENCRYPTION_WALLET;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
-------------------- ---------------------------------------- ------------------------------ -------------------- --------- --------- ----------
FILE /u01/app/oracle/admin/gclone1/wallet/ CLOSED UNKNOWN SINGLE UNDEFINED 0
Sometimes when you restart your database you will find that wallet is not opened automatically. so you need to open it manually. this is a common issue faced.
SYS@ gclone1>select * from V_$ENCRYPTION_WALLET;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
-------------------- ---------------------------------------- ------------------------------ -------------------- --------- --------- ----------
FILE /u01/app/oracle/admin/gclone1/wallet/ OPEN PASSWORD SINGLE NO 0
SYS@ gclone1>shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ gclone1>SYS@ gclone1>startup
ORACLE instance started.
Total System Global Area 536870912 bytes
Fixed Size 2926472 bytes
Variable Size 348129400 bytes
Database Buffers 180355072 bytes
Redo Buffers 5459968 bytes
Database mounted.
Database opened.
SYS@ gclone1>select * from V_$ENCRYPTION_WALLET;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
-------------------- ---------------------------------------- ------------------------------ -------------------- --------- --------- ----------
FILE /u01/app/oracle/admin/gclone1/wallet/ CLOSED UNKNOWN SINGLE UNDEFINED 0
SYS@ gclone1>select * from scott.tde_demo;
select * from scott.tde_demo
*
ERROR at line 1:
ORA-28365: wallet is not open
SYS@ gclone1>ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "System@123System";
System altered.
SYS@ gclone1>select * from V_$ENCRYPTION_WALLET;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
-------------------- ---------------------------------------- ------------------------------ -------------------- --------- --------- ----------
FILE /u01/app/oracle/admin/gclone1/wallet/ OPEN PASSWORD SINGLE NO 0
To avoid this, we can enable auto login ,so that next time when db gets restart, it will open the wallet automatically. Note that WALLET_TYPE has chnages from PASSWORD to AUTOLOGIN
SYS@ gclone1>ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '/u01/app/oracle/admin/gclone1/wallet/' IDENTIFIED BY "System@123System";
keystore altered.
SYS@ gclone1>startup force
ORACLE instance started.
Total System Global Area 536870912 bytes
Fixed Size 2926472 bytes
Variable Size 348129400 bytes
Database Buffers 180355072 bytes
Redo Buffers 5459968 bytes
Database mounted.
Database opened.
SYS@ gclone1>select * from V_$ENCRYPTION_WALLET;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
-------------------- ---------------------------------------- ------------------------------ -------------------- --------- --------- ----------
FILE /u01/app/oracle/admin/gclone1/wallet/ OPEN AUTOLOGIN SINGLE NO 0
DBATracker 