Indexing with Oracle TDE

Oracle Transparent Data Encryption allows data encryption to be declared in a database schema, meaning that anything persisted on disk is protected from prying eyes.

It is a simple matter to setup TDE by bascially configuring a wallet location in sqlnet.ora, setting the key, and then opening the wallet after database startup. There's a good tutorial for this on OTN.

Declaring data encryption is then simply a matter of using the ENCRYPT keyword in your schema defintion, for example:

CREATE TABLE T1
(SEQ NUMBER(15),
CCNUMBER CHAR(16) ENCRYPT USING 'AES128' NO SALT);

Indexing encrypted columns is covered in the Advanced Security Guide. It mentions specifically that you cannot create an index on a column that has been encrypted with salt (hence the 'NO SALT' above). There is another restriction that you cannot use encrypted columns in functional indexes, but I've yet to find this covered explicitly in the doco. You may be surprised to find out that this also means you get caught if you try to create an index with descending values, such as:

CREATE INDEX T1_AK1 ON T1 (CCNUMBER, SEQ DESC);

This will fail with the error "ORA-28337: the specified index may not be defined on an encrypted column". The reason for this is that the use of "descending" will be treated as a functional index.

Removing the "descending" qualifier allows a valid index to be built:

CREATE INDEX T1_AK2 ON T1 (CCNUMBER, SEQ);