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 18.104.22.168 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
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.
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 22.214.171.124 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