Encrypting Tablespaces

The first step is to create an Oracle Wallet to store the encryption key details. There are several type of wallet available we are going to create a simple file based one.  The default location of the wallet for a database is denoted by one of the following:-

1. ENCRYPTION_WALLET_LOCATION parameter in the sqlnet.ora file.
2. WALLET_LOCATION parameter in the sqlnet.ora file.
3. The default location for the wallet ($ORACLE_BASE/admin/$ORACLE_SID/wallet).

In this case we are going to go for the default. Login as sysdba and run the following command:-

SQL> alter system set encryption key identified by "Test12345";

System altered.

At this point it is worth noting that you will need to reopen  the wallet after an instance restart with the following command:-

SQL> alter system set encryption wallet open identified by "Test12345";

Should you need to the wallet can also be closed with the following:-

SQL> Alter system set encryption wallet close identified by "Test12345";

The next step is to create the encrypted tablespace. Now for this example I am going to create two tablespaces, one encrypted and the other not.

SQL> CREATE TABLESPACE encryptedts DATAFILE '/u01/app/oracle/datapump/data/encryptedts.dbf' size 10M autoextend on encryption using 'AES256' DEFAULT STORAGE(ENCRYPT);

SQL> CREATE TABLESPACE nocryptedts DATAFILE '/u01/app/oracle/datapump/data/nocryptedts.dbf' size 10M autoextend on;

SQL> SELECT tablespace_name, encrypted FROM dba_tablespaces;


Then create a couple of users and some objects to test with to test with:-

SQL> create USER test_encrypt identified by password;
SQL> alter user test_encrypt QUOTA UNLIMITED ON encryptedts;
SQL> alter user test_encrypt QUOTA UNLIMITED ON users;
SQL> grant create session to test_encrypt;
SQL> grant create any table to test_encrypt;

SQL> create table test_encrypt.enc_test (nocrypt  VARCHAR2(50),encrypt  VARCHAR2(50)) tablespace encryptedts;

SQL> insert into test_encrypt.enc_test values('Nocrypt column', 'This is a not encrypted!');

SQL> commit;

SQL> create USER test_nocrypt identified by password;
SQL> alter user test_nocrypt QUOTA UNLIMITED ON nocryptedts;
SQL> alter user test_nocrypt QUOTA UNLIMITED ON users;
SQL> grant create session to test_nocrypt;
SQL> grant create any table to test_nocrypt;

SQL> create table test_nocrypt.enc_test (nocrypt  VARCHAR2(50), encrypt  VARCHAR2(50)) tablespace nocryptedts;

SQL> insert into test_nocrypt.enc_test values('nocrypt column', 'This is a not encrypted!');

SQL> commit;

Now we are going to drop to os and inspect the datafiles. For safetys sake shutdown the database but if you’re a bit more cavalier than me flush the buffer cache to write the data down to disc first.

SQL> connect / as sysdba
Connected.

SQL> alter system flush buffer_cache;