Indexing and Transparent Data Encryption Part I (The Secret Life of Arabia)

Database security has been a really hot topic recently so I thought I might write a few posts in relation to indexing and Transparent Data Encryption (TDE) which is available as part of the Oracle Advanced Security option.

To protect the database from unauthorized “backed-door” accesses, the data within the actual database files can be encrypted. This helps prevent privileged users such as Database and System Administrators who have direct access to these files from being able to inappropriately access confidential data by simply viewing the data directly with OS editors and the such.

There are two basic ways one can use Oracle TDE to encrypt data within an Oracle database; via Column or Tablespace encryption. Column-based encryption has a number of restrictions, especially in relation to indexing which I’ll cover in later posts. To start with, my focus will be on Tablespace encryption.

With Tablespace encryption, all objects within the tablespace are encrypted in the same manner using the same encryption key and so has far fewer restrictions and issues.

It’s relatively easy to setup up tablespace encryption although note you must have the Oracle Advanced Security Option.

First, ensure you have an entry in your sqlnet.ora that identifies the location of your Oracle Security Wallet, such as:

ENCRYPTION_WALLET_LOCATION=
(SOURCE=
(METHOD=FILE)
(METHOD_DATA=
(DIRECTORY=D:\wallet)))

As a user with ADMINISTER KEY MANAGEMENT privileges, create a password protected keystore wallet in the location referenced in the sqlnet.ora file such as:

SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE 'd:\wallet' identified by bowie;

keystore altered.

This wallet contains the master key used in turn to encrypt the various column and tablespace encryption keys used within the database to encrypt the actual data. Do not lose this wallet as without it, you will not be able to access any of your encrypted data !!

Next, open the database keystore using the password specified above such as:

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY bowie;

keystore altered.

Finally, create an encrypted tablespace to hold all your sensitive data, such as:

SQL> CREATE TABLESPACE safe DATAFILE 'd:\app\rfoote\oradata\orcl\safe01.dbf ' size 10m ENCRYPTION USING '3DES168' DEFAULT STORAGE(ENCRYPT);

Tablespace created.

OK, to set the scene, we’re going to first create and populate a table in an unencrypted tablespace (note I’ve created tiny tablespaces here just so I can quickly open the file with a basic windows notepad editor):

SQL> create table bowie (id number, salary number, text varchar2(30)) tablespace unsafe;

Table created.

SQL> insert into bowie select rownum, rownum, 'DAVID BOWIE' from dual connect by level <=100000;

100000 rows created.

SQL> commit;

Commit complete.

I now flush the buffer cache to ensure all the data is written to disk:

SQL> alter system flush buffer_cache;

System altered.

If I now simply opened the tablespace data file with a text or hex editor, I can easily see the contents of the table, for example:

DAVID BOWIE,Ã]Ã]
DAVID BOWIE,Ã^Ã^
DAVID BOWIE,Ã_Ã_
DAVID BOWIE,Ã`Ã`
DAVID BOWIE,ÃaÃa
DAVID BOWIE,ÃbÃb
DAVID BOWIE,ÃcÃc
DAVID BOWIE,ÃdÃd
DAVID BOWIE,ÃÃ
DAVID BOWIE,ÃÃ
DAVID BOWIE,ÃÃ

If I however create the same data but store the table in the encrypted tablespace:

SQL> create table bowie2 (id number, salary number, text varchar2(30)) tablespace safe;

Table created.

SQL> insert into bowie2 select rownum, rownum, 'DAVID BOWIE' from dual connect by level <=100000;

100000 rows created.

SQL> commit;

Commit complete.

SQL> alter system flush buffer_cache;

System altered.

If I now try to view the contents of the encrypted data file, I find there’s nothing that’s in a readable format. For example:

4VûÇÓÓž|²÷hTîç:qz-XÒåPÏœkNþr#ßto¢€p$oÉ<÷¨q뉸øÊ^rìÔó­š< Ï›”ÕïÑꖸ轫P6″²Ý÷FE›Û!涷½:|WÞÍ)᪞!
W:ë¿®¡i’:5g”=FE÷W’97ð˜g2Å1h›ö{ž—êûò×ø\ßÓð¶§
£õÉÔS‡DþÚ)?ÿ\‹L<ԏ#^@iIØ—Nkå#_}Í5BüŒèÚVµÄ?Àö•*¡”õtX
”osø

So good luck to any dodgy DBA or Sys Admin folk who tries to circumvent database security by directly viewing the database files or backups or stolen copies of the files, etc.

If we now also create an index on this table within the encrypted tablespace:

SQL> create index bowie2_idx on bowie2(id, text) tablespace safe;

Index created.

SQL> alter system flush buffer_cache;

System altered.

Again, a hunt for meaningful data in the data file will be fruitless. And because the data is encrypted at the tablespace layer, the index can be used with no restrictions:

SQL> set autotrace on
SQL> select * from bowie2 where id between 42 and 46;

        ID     SALARY TEXT
---------- ---------- ------------------------------
        42         42 DAVID BOWIE
        43         43 DAVID BOWIE
        44         44 DAVID BOWIE
        45         45 DAVID BOWIE
        46         46 DAVID BOWIE

Execution Plan
----------------------------------------------------------
Plan hash value: 3132122149

--------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name       | Rows  | Bytes |  Cost(%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |            |     5 |   215 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| BOWIE2     |     5 |   215 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | BOWIE2_IDX |     5 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID">=42 AND "ID"<=46)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
         18  recursive calls
          0  db block gets
         78  consistent gets
        311  physical reads
          0  redo size
        831  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed

Now comes the first trap.

If we were to replicate this data in an unencrypted tablespace, then of course the associated data would be visible within the database data files again. And it’s of course very easy to potentially replicate table data, either accidently or maliciously, via the creation of database indexes. For example, the default tablespace of a user might be in an unencrypted tablespace or a privileged user who we are trying to protect ourselves from has the create any index privilege.

So if we create an index in an unencrypted tablespace (I’m also adding a new row to easily identify it from the previous values inserted with the initial BOWIE table):

SQL> insert into bowie2 values (100001, 42, 'ZIGGY STARDUST');

1 row created.

SQL> commit;

Commit complete.

SQL> create index bowie2_all_i on bowie2(id, salary, text) tablespace unsafe;

Index created.

SQL> alter system flush buffer_cache;

System altered.

We can search all we want in the encrypted tablespace for meaningful data with little success but all the confidential data can now be seen via the index data, stored in the unencrypted tablespace:

ÃbVÃbVDAVID BOWIE@ <    ÃÁ+ZIGGY STARDUST@¡   ÃÃDAVID BOWIE@¡

Don’t let down your good TDE security intentions by being a little lazy in how you manage and police your database indexes.