Transparent Data Encryption (TDE) feature was introduced for the first time in Oracle 10g R2. TDE can be used in Enterprise edition and is a feature that can be used with the Advanced Security license.
It provides mechanism to encrypt the data stored in the OS data files. TDE enables the encryption of data at the storage level to prevent data tempering from outside of the database.
With Oracle Transparent Data Encryption feature, you can encrypt data on Tablespace, table and column level with the Master key that you have stored in Wallet. As long as the wallet is open, authorized users in the database can see the data.
TDE supports any of the following encryption algorithms to encrypt the data.
- 3DES168, AES128, AES192, AES256
TDE uses an encryption key to encrypt or decrypt the data. The encryption key is saved in a Keystore which is saved external to the database.For example, the Keystore can be saved in a file.
TDE supports two levels of encryption:
- Columns Level Encryption: Encrypt the table column data
- Tablespace Level Encryption: Encrypt all the data in a tablespace
Let’s see how to configure TDE:-
- Set a KeyStore local in SQLNET.ORA
- Create a Password based KeyStore
- Open the KeyStore
Step 1: Set KEYSTORE location in SQLNET.ORA
1.1) Go to ORACLE_HOME\NETWORK\ADMIN and open the SQLNET.ora in a notepad file
Vi $ORACLE_HOME/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
ENCRYPTION_WALLET_LOCATION =
(SOURCE =(METHOD = FILE)(METHOD_DATA =
(DIRECTORY = /home/oracle/app/oracle/product/12.1.0/network/admin/WALLET)))
STEP 2: Create a Password-based KeyStore
Login as SYSDBA or a user who has been granted the ADMINISTER KEY MANAGEMENT
sqlplus sys/password as sysdba
Create a KEYSTORE
ADMINISTER KEY MANAGEMENT CREATE KEYSTORE ‘/home/oracle/app/oracle/product/12.1.0/network/admin/WALLET’ IDENTIFIED BY passwod123;
Wallet is created like following:
oracle:XYZ: /home/oracle/app/oracle/product/12.1.0/network/admin /wallet:>ls -all
-rw-r--r-- 1 oracle dba 2845 Nov 30 11:28 ewallet.p12
Create test tablespace and table and insert some test data like following:
SQL> CREATE TABLESPACE encrypted_ts datafile '+DATA/test01.dbf' SIZE 128K AUTOEXTEND ON NEXT 64K ENCRYPTION USING 'AES256' DEFAULT STORAGE(ENCRYPT);
Tablespace created.
SQL> SELECT tablespace_name, encrypted FROM dba_tablespaces;
TABLESPACE_NAME ENC
------------------------------ ---
SYSTEM NO
SYSAUX NO
UNDOTBS1 NO
TEMP NO
USERS NO
EXAMPLE NO
ENCRYPTED_TS YES
7 rows selected.
SQL> CREATE TABLE ets_test (
id NUMBER(10),
data VARCHAR2(50)
)
TABLESPACE encrypted_ts;
Table created.
SQL> INSERT INTO ets_test (id, data) VALUES (1, 'TDE Test');
1 row created.
SQL> COMMIT;
Commit complete
When we query the related table, we got following error because of wallet.
SQL> select * from ets_test;
select * from ets_test
*
ERROR at line 1:
ORA-28365: wallet is not open
When we open wallet, we got error again. To solve this problem you need to restart database.
Open the keystore
Login as sysdba and Open the KEYSTORE
3 | ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY password123; |
ERROR at line 1:
ORA-28354: Encryption wallet, auto login wallet, or HSM is already open
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 6029524992 bytes
Fixed Size 2257424 bytes
Variable Size 1140854256 bytes
Database Buffers 4865392640 bytes
Redo Buffers 21020672 bytes
Database mounted.
Database opened.
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY password123;
Query related table again after opening wallet.
SQL> select * from ets_test;
ID DATA
---------- --------------------------------------------------
1 TDE Test
**********************************************************************
Our Website : https://www.ksplsoft.com/
About Us:
KSPL is a leading group of highly skilled professional group, who are having more than 10 years of experience in various technologies. We are providing support and maintenance for existing product like RDBMS, Reporting, Cloud Solutions, Mobile development and Web development etc. Also we develop new product using various technologies like cloud, Unix, Database, Reports, Mobile applications and web designing and creation. With a team of dedicated and creative engineers, we focus on mentioned work/projects you to meet the challenges with tight deadline and trust to market.
KSPLsoft 