TDE encryption in oracle 11g step by step

TDE stands for Transparent Data Encryption. It was initially release in Oracle 10gR1 where it gave the capability to encrypt the column in the table. With 11gR1 Now , we can encrypt both the tablespace and individual table column using TDE.

What is TDE (Transparent Data Encryption)

TDE(Transparent Data Encryption) as the name suggest transparently encrypts data at rest in Oracle Databases. It stops unauthorized attempts from the operating system to access database data stored in files, without impacting how applications access the data using SQL. So we don’t have any impact to Business. If the malicious user tries to open file using a HEX editor (like UltraEdit),then only non-printable characters will be present. TDE can be use to encrypt application tablespaces or specific sensitive columns. With Tablespace encrypted ,All objects created in the encrypted tablespace are automatically encrypted.

TDE encryption in oracle 11g step by step

Lets see the steps required to setup TDE. This will be same steps for both Tablespace encryption and Table column encrpytion.

(1) Before attempting to enable encryption, a wallet/keystore must be created to hold the encryption key. The search order for finding the wallet is as follows:

If present, the location specified by the ENCRYPTION_WALLET_LOCATION parameter in the sqlnet.ora file.
If present, the location specified by the WALLET_LOCATION parameter in the sqlnet.ora file.
The default location for the wallet. If the $ORACLE_BASE is set, this is “$ORACLE_BASE/admin/DB_UNIQUE_NAME/wallet”, otherwise it is “$ORACLE_HOME/admin/DB_UNIQUE_NAME/wallet”, where DB_UNIQUE_NAME comes from the initialization parameter file.
Although encrypted tablespaces can share the default database wallet, Oracle recommend you use a separate wallet for transparent data encryption functionality by specifying the ENCRYPTION_WALLET_LOCATION parameter in the sqlnet.ora file.

Lets create a directory

mkdir -p /u01/app/oracle/admin/TEST11G/encryption_wallet/

Edit the “$ORACLE_HOME/network/admin/sqlnet.ora” files, adding the following entry.

ENCRYPTION_WALLET_LOCATION=
(SOURCE=(METHOD=FILE)(METHOD_DATA=
(DIRECTORY=/u01/app/oracle/admin/TEST11G/encryption_wallet/)))

This parameter can also be used to identify a Hardware Security Model (HSM) as the location for the wallet

(2). Now The following command creates and opens the wallet.

ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "myPassword";
Example
CONN sys/<syspass>@TEST11G AS SYSDBA
ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "testwallet01";

(3)Now we are all set to encrypt the table column or tablespace

Lets create a tablespace

CREATE TABLESPACE TEST_TS
DATAFILE '/u01/app/oracle/oradata/TEST11G/test_ts01.dbf' SIZE 128K
AUTOEXTEND ON NEXT 64K
ENCRYPTION USING 'AES256'
DEFAULT STORAGE(ENCRYPT);

ALTER USER test QUOTA UNLIMITED ON TEST_TS;

The ENCRYPTED column of the DBA_TABLESPACES and USER_TABLESPACES views indicates if the tablespace is encrypted or not.

SELECT tablespace_name, encrypted FROM dba_tablespaces where tablespace_name='TEST_TS';
TABLESPACE_NAME ENC
--------------------------   ---------
TEST_TS           YES

Now lets create table in this tablespace

CONN test/[email protected]
CREATE TABLE test_encryption (
id NUMBER(10),
data VARCHAR2(100)
)
TABLESPACE test_ts;

INSERT INTO test_encryption (id, data) VALUES (1, 'This is test to a check encryption!');
COMMIT;

Flush the buffer cache to make sure the data is written to the datafile.

CONN sys/[email protected] AS SYSDBA
ALTER SYSTEM FLUSH BUFFER_CACHE;

When the file is opened using a HEX editor (like UltraEdit) or check using strings command ,Then ‘This is a test to check encryption!’ string is not visible in the table data within the encrypted tablespace.

$strings test_ts01.dbf | grep encryption
$ 

If we just create the normal tablespace, this data would visible

--create tablespace without encryption
CREATE TABLESPACE TEST_TS2
DATAFILE '/u01/app/oracle/oradata/TEST11G/ts2_data.dbf'
SIZE 1m;

--give quoto
ALTER USER test QUOTA UNLIMITED ON TEST_TS2;

----connect as normal user and create tablle
CONN test/[email protected]
CREATE TABLE test_t (
id NUMBER(10),
data VARCHAR2(100)
)
TABLESPACE test_ts2;

--insert data
INSERT INTO test_t (id, data) VALUES (1, 'This is test to a check encryption!');
COMMIT;

---Flush the buffer cache to make sure the data is written to the datafile
CONN sys/[email protected] AS SYSDBA
ALTER SYSTEM FLUSH BUFFER_CACHE;

---Now you can use to string to check the datafile
$strings ts2_data.dbf | grep encryption
This is test to a check encryption!

Instead of encryption whole tablespace, you may want to encryption single column, then you can do like this

CONN test/[email protected]
CREATE TABLE test_t (
id NUMBER(10),
data VARCHAR2(100) encrypt
)
TABLESPACE test_ts2;

This will encrypt the column data in table test_t

(4) We need to open the wallet incase instance is restarted, else it give error

The command to explicitly open and close the wallet are

ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "myPassword";
ALTER SYSTEM SET ENCRYPTION WALLET CLOSE;

Now lets test the instance restart case

SQL>CONN / AS SYSDBA
Connected.
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP
ORACLE instance started.
Database mounted.
Database opened.

Now lets try to query the encrypted data

CONN test/[email protected]
SQL> SELECT * FROM test_encryption;
select * from test_encryption
*
ERROR at line 1:
ORA-28365: wallet is not open

Now lets open the wallet

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

Now lets try to query the encrypted data again

SQL> SELECT * FROM test_encryption;
ID   DATA
---  -----
1    This is test to a check encryption

I hope you like this post on how to do TDE encryption in oracle 11g step by step