11g Tablespace Encryption

In Oracle 10g itself Transparent Data Encryption (TDE) was introduced. But the downside of that feature was that we could only perform the encryption individually at the table column level.

Now in Oracle 11g, we can enable encryption at the tablespace level and that will then cascade down to every table which resides in that particular tablespace.

Let’s now see the steps involved.

We need to specify the encryption wallet location in the sqlnet.ora file as shoewn below.

ENCRYPTION_WALLET_LOCATION=
(SOURCE=(METHOD=FILE)(METHOD_DATA=
(DIRECTORY=/u01/app/oracle/admin/leven202/wallet)))

Next provide an encryption key for the wallet. Think of it as a password which will be required to open the wallet.

SQL> ALTER SYSTEM SET ENCRYPTION KEY AUTHENTICATED BY “gavin”;

System altered.

We will see that a file ‘ewallet.p12’ has been created in the location which we specified for the wallet in the sqlnet.ora.

SQL> !ls /u01/app/oracle/admin/leven202/wallet
ewallet.p12

Note – this will also automatically open the wallet as well. If we try and open the wallet we will get an error.

SQL> ALTER SYSTEM SET WALLET OPEN IDENTIFIED BY “gavin”;
ALTER SYSTEM SET WALLET OPEN IDENTIFIED BY “gavin”
*
ERROR at line 1:
ORA-28354: wallet already open

Now create the encrypted tablespace. The syntax is the same as creating a normal tablespace except for two clauses:

SQL> create tablespace encrypt_data
2 datafile ‘/u03/oradata/leven202/encrypt_data01.dbf’ size 100m
3 ENCRYPTION USING ‘AES256’
4 DEFAULT STORAGE (ENCRYPT);

Tablespace created.

SQL> alter user scott quota unlimited on encrypt_data
2 ;

User altered.

Now connect as user SCOTT and create a table in the encrypted tablespace.

SQL> conn scott/tiger
Connected.
SQL> create table TEST_ENCRYPTION
2 (col_a VARCHAR2(20))
3 tablespace encrypt_data;

Table created.

SQL> insert into TEST_ENCRYPTION
2 values
3 (‘SECURE DATA!’);

1 row created.

SQL> commit;

Commit complete.

To test the encryption, we will create a normal tablespace with no encryption and create a table in that tablespace.

SQL> create tablespace noencrypt_data
2 datafile
3 ‘/u03/oradata/leven202/noencrypt_data01.dbf’ size 20M;

Tablespace created.

SQL> alter user scott quota unlimited on noencrypt_data;

User altered.

SQL> conn scott/tiger
Connected.

SQL> create table TEST_NOENCRYPTION
2 (col_a VARCHAR2(20))
3 tablespace noencrypt_data
4 ;

Table created.

SQL> insert into TEST_NOENCRYPTION
2 values
3 (‘UNSECURE DATA’);

1 row created.

SQL> commit;

Commit complete.

We will now flush the contents of the buffer cache to ensure blocks are written to the datafiles on disk and then we will copy the two data files from the Unix machine to our client PC.

SQL> conn / as sysdba
Connected.
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;

System altered.

SQL> /

System altered.

Thanks to Tim Hall from Oracle-Base for pointing this out. Download the Free Trial version of UltraEdit which we will use to read the contents of both the datafiles.

Using UltraEdit tool, we will open both the data files which we have copied to a directory on our client PC and search for the text string ‘SECURE’ and ‘UNSECURE’ in the data files. Remember we had inserted some values into both the tables containing the strings ‘SECURE’ and ‘UNSECURE’.

For the datafile noencrypt_data01.dbf’ we will see that the search does reveal the data stored in the data file while the same search using data file encrypt_data01.dbf does not reveal any data because the data has been encrypted.

Search noencrypt_data01.dbf

Search encrypt_data01.dbf

Have a look at the screenshots below.

Remember that if we shutdown the database, we will need to open the wallet first in order to write to the ENCRYPT_DATA tablespace.

SQL> conn scott/tiger
Connected.
SQL> create table test
2 (col_a varchar2(20))
3 tablespace encrypt_data;

Table created.

SQL> insert into test
2 values
3 (‘Is the wallet open?’);
insert into test
*
ERROR at line 1:
ORA-28365: wallet is not open

SQL> conn / as sysdba
Connected.

SQL> ALTER SYSTEM SET WALLET OPEN IDENTIFIED BY “gavin”;
System altered.

SQL> conn scott/tiger
Connected.

SQL> insert into test
2 values
3 (‘Is the wallet open?’);

1 row created.