Transparent Data Encryption Oracle

Transparent Data Encryption (TDE) was introduced in Oracle 10G Release 2. Tablespace encryption helps you to secure the complete contents of one Tablespace instead of encrypt column by column. Oracle has also built in support for hardware-based crypto accelaration 11.2.0.2 for Intel XEON 5600 CPUs with AES-NI. Keep in mind that TDE can also be a performance impact if you encrypt your data and have for example unencrypted indexes. In this case a FULL TABLE SCAN can occur.

We must complete three steps to encrypt our data.

• Create a Wallet

• Create a Tablespace

• Test the encryption

License: TDE is a part of the Oracle Advanced Security Option which also includes Strong Authentication and Network encryption. It is only avaible in the Enterprise Edtion of the database as extra cost option.

Create a Wallet:

Berfore we can create a encrypted tablespace we must first create a Oracle Wallet which holds the encryption key. The database read the sqlnet.ora File to find the wallet. If no entry is present in the sqlnet.ora File the database trys to find the Wallet under $ORACLE_HOME/admin/$ORACLE_SID/wallet.

sqlnet.ora

ENCRYPTION_WALLET_LOCATION=
  (SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/etc/ORACLE/wallet/)))

The following command creates and open the wallet

SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "welcome1";
System altered.
SQL> !ls /etc/ORACLE/wallet
ewallet.p12

To see the status of your wallet you can query v$encryption_wallet

SQL> select * from v$encryption_wallet;

WRL_ WRL_PARAMETER                  STATUS
---- ------------------------------ ------------------
file /etc/ORACLE/wallet/            OPEN

If you want to close the wallet you may get a ORA-28390 which is a expected is expected, to close the wallet in 11.2.0.1 onwards there’s new syntax.

SQL> ALTER SYSTEM SET ENCRYPTION WALLET CLOSE;
ALTER SYSTEM SET ENCRYPTION WALLET CLOSE
*
ERROR at line 1:
ORA-28390: auto login wallet not open but encryption wallet may be open

SQL> ALTER SYSTEM SET ENCRYPTION WALLET CLOSE IDENTIFIED BY "welcome1";

System altered.

To reopen the wallet just use the normal open command.

ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "welcome1";

Create a Tablespace:

To create a encrypted tablespace open your wallet and use the encryption syntax in your SQL Statement.

CREATE TABLESPACE lobts3
DATAFILE '/u01/app/oracle/oradata/orcl/lobtbs3.dbf' SIZE 20M
AUTOEXTEND ON NEXT 64K
ENCRYPTION USING 'AES256'
DEFAULT STORAGE(ENCRYPT);

ALTER USER hr QUOTA UNLIMITED ON lobts3;

To check your tablespace query the dba_tablespace View:

SQL> SELECT tablespace_name, encrypted FROM dba_tablespaces where tablespace_name = 'LOBTS3';

TABLESPACE_NAME                ENC
------------------------------ ---
LOBTS3                         YES

Test the encryption

Create a table in your encrypted tablespace and place some data in it.

CONN hr/hr

CREATE TABLE bigemp (
  empno    NUMBER(10),
  ename   VARCHAR2(50)
)
TABLESPACE lobts3;

CREATE INDEX bigemp_test_idx ON bigemp(ename) TABLESPACE lobts3;

INSERT INTO bigemp (empno, ename) VALUES (10, 'This is secret data');
COMMIT;

Remember that your wallet must be open to access your data. If not the database will raise a error.

SQL> select * from hr.bigemp;
select * from hr.bigemp
                 *
ERROR at line 1:
ORA-28365: wallet is not open