I will explain what is the Transparent Data Encryption in Oracle in this article.
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/18.104.22.168/db/network/admin:>vi sqlnet.ora "sqlnet.ora" [New file] ENCRYPTION_WALLET_LOCATION= (SOURCE=(METHOD=FILE)(METHOD_DATA= (DIRECTORY=/oracle/product/22.214.171.124/db/network/admin/wallet))) ~ ~ "sqlnet.ora" [New file] 3 lines, 143 characters [oracle@MehmetSalih ~]$ :/oracle/appora/product/126.96.36.199/db/network/admin:>
Step 2: Specify any wallet password and Creates and opens the wallet like following.
[oracle@MehmetSalih:/oracle/appora/product/188.8.131.52/db/network/admin:>sqlplus / as sysdba SQL*Plus: Release 184.108.40.206.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 220.127.116.11.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/18.104.22.168/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";
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.