TDE encryption in oracle 11g step by step

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

What is TDE (Transparent Data Encryption)?

As the name suggests, TDE(Transparent Data Encryption) transparently encrypts data at rest in Oracle Databases. It stops unauthorized attempts by 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 on business. If the malicious user tries to open the file using a HEX editor (like UltraEdit), then only non-printable characters will be present. TDE can be used 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

Let’s see the steps required to set up TDE. These will be the same steps for both tablespace encryption and table column encryption.

(1) Before attempting to enable encryption, a wallet or 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 recommends you use a separate wallet for transparent data encryption functionality by specifying the ENCRYPTION_WALLET_LOCATION parameter in the sqlnet.ora file.

Let’s 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

Let’s 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 let’s create a table in this tablespace.

CONN test/test@TEST11G
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 data file.

CONN sys/password@TEST11G AS SYSDBA
ALTER SYSTEM FLUSH BUFFER_CACHE;

When the file is opened using a HEX editor (like UltraEdit) or checked using the strings command, the ‘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 created the normal tablespace, this data would be 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/test@TEST11G
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/password@TEST11G 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 encrypting the whole tablespace, you may want to encrypt a single column. Then you can do something like this.

CONN test/test@TEST11G
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 in case the instance is restarted, else it will give an error.

The command to explicitly open and close the wallet is

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

Now let’s 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 let’s try to query the encrypted data.

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

Now let’s open the wallet.

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

Now let’s 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

Related Articles

How To Restore TDE Wallet Files From Backup in Oracle Database
how to check if oracle database is encrypted
TDE encryption in Oracle 12c step by step