Transparent Data Encryption in Oracle

Hi,

I will explain what is the Transparent Data Encryption in Oracle in this article.


TDE

 

 

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.

 

All data in the Oracle database is physically kept in Datafiles. In order to prevent some private data from being accessed by malicious people, Oracle recommends that the data be physically stored by encrypting them in the Datafile. In this case, even when the Datafiles are accessed directly or when the Backups are accessed by malicious people, private data can not be stolen by unauthorized persons.

 

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. In this article I will encrypt the Tablespace. Thus, all data in tablespace that I created with TDE will be encrypted.

 

 

Now let’s go to make an example to understand TDE clearly, perform Transparent Data Ecryption at the Tablespace level like following.

 

Step -1: Edit sqlnet.ora file under $ORACLE_HOME/network/admin location and add location of wallet to the sqlnet.ora file like following.

[oracle@MehmetSalih: /oracle/appora/product/12.1.0.2/db/network/admin:>vi sqlnet.ora
 "sqlnet.ora" [New file]
 ENCRYPTION_WALLET_LOCATION=
 (SOURCE=(METHOD=FILE)(METHOD_DATA=
 (DIRECTORY=/oracle/product/12.1.0.2/db/network/admin/wallet)))
 ~
 ~
 "sqlnet.ora" [New file] 3 lines, 143 characters
 [oracle@MehmetSalih ~]$ :/oracle/appora/product/12.1.0.2/db/network/admin:>


 

Step 2: Specify any wallet password and Creates and opens the wallet like following.

[oracle@MehmetSalih:/oracle/appora/product/12.1.0.2/db/network/admin:>sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Thu Sep 4 11:27:31 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
 Connected to:
 Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
 With the Partitioning, Automatic Storage Management, OLAP, Data Mining
 and Real Application Testing options
 



SQL> alter system set encryption key authenticated by "test123";
System altered.

 

 

 

 

Wallet is created like following.

oracle:testsunucu:/oracle/appora/product/12.1.0.2/db/network/admin/wallet:>ls -all
 total 8
 drwxr-xr-x 2 oracle oinstall 256 Sep 04 11:28 .
 drwxr-xr-x 4 oracle oinstall 256 Sep 04 11:27 ..
 -rw-r--r-- 1 oracle dba 2845 Sep 04 11:28 ewallet.p12

 

Step 3: 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.



  

Step 4:  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

 

Step 5: When we open wallet, we got error again. To solve this problem you need to restart database.

 

SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "test123";
 ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "test123"
 *
 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.

Step 6: Now open wallet with its password.

 SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "test123";
System altered.



 

Step 7:  Query related table again after opening wallet.

SQL> select * from ets_test;
ID DATA
---------- --------------------------------------------------
1 TDE Test


We did not get any error after open wallet, thus our data has been stored on disk as encrypted.

 

 

 

Mehmet Salih Deveci

I am Founder of IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress mehmetsalih.deveci@outlook.com.-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  mehmetsalih.deveci@outlook.com a mail atabilirsiniz.

2 thoughts on “Transparent Data Encryption in Oracle

Leave a Reply

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