I recently needed to enable encryption in an Apex/Oracle application to protect data at rest using Oracle transparent data encryption. This encryption works at the database level only and protects data in database files and redo logs. Accessing without being an authorized user of the database leaves selected data encrypted while authorized access transparently encrypts/decrypts the data. It is slick stuff that is general except when the data encrypted is in a FK relationship as well as function-based indexes.

The challenge I am dealing with is how much to encrypt. For the applications I am developing, personally identifiable information (PII) such as name, age, gender, and SSN are the targets to protect. TDE overhead is generally 3-5% CPU so it doesn't come for free. I am working with Oracle 11g which supports tablespace TDE but have many customers who have 10g rel 2 which supports column-level only. If I could get everyone on to 11g, but until then, I need to work this out.

For an existing table created earlier such as shown below

create table demographics

( demographics_pk number

, first_name varchar2(80)

, last_name varchar2(80)

, dob  date 

, ssn varchar2(9)

);

The TDE can be applied using the following:

alter table demographics modify (last_name encrypt using 'AES256' NO SALT);

alter table demographics modify (dob encrypt using 'AES256');

The first example encrypts demographics.last_name using the strong AES algorithm 256 bit encryption. The NO SALT option does not apply seeding and is required when that column will be indexed.

The second example would apply SALT automatically - slightly more secure but only useful when the column is not indexed.

Of course, none of this works if an encryption wallet to store the master and lesser keys is not set up on the Oracle instance beforehand. My experience with encryption wallets is to make sure you can back up and restore the wallet. I made the mistake on my laptop instance of not properly protecting the wallet when first learning about TDE and found out the hard way that the data becomes permanently encrypted. A bummer whose only cure is to drop the schema containing the permanently encrypted data and rebuild the schema. This is definitely not what you want to do with production systems, so I always build in some training on projects that include TDE so that the customer DBAs can handle it. Makes me sleep better at night as well.

Now, to the issue that started this blog. The PII that needs to be protected is a sensitive identifier like the SSN and additional information like name. An identity thief can use the two together to create credit. Ideally, I would like to encrypt all of it - names, dob, ssn, gender. this blocks theft from backup tapes and file system hacks and narrows identity theft to theft by authorized users. Not a pleasant issue to be sure but often easier to resolve.

In practice, I have function-based indexes on name so I can't encrypt those columns. I encrypted dob, gender, SSN, and alterative keys that could be cross-referenced with the name and other data over time by a clever hacker. I think that is sufficient since our names alone are generally considered public. I also encrypted name, dob, ssn and other data in all other tables containing PII. In my application, I have aliases of person data and audit tables. All of these were encrypted as well. Basically any table that has PII is a candidate for encryption.

The only wildcard is impact on performance. I am expecting the performance hit to not be 3-5% for multiple columns. I am not sure what will be acceptable but feel that a hit of up to 10% aggregate degradation may be acceptable.

A change of subject - Oracle technology is of minor interest to the DDJ, I believe. Encryption would generally be implemented from a client app, and over the years, there has been a lot of discussion to that effect. The DDJ community should be aware that there are different approaches to this problem and that shared-data applications should be based on a database. My favorite is Oracle for enterprise data-centric apps, especially if security is an issue - and nowaways security always is. Sorry, I will stop ranting, but think about it the next time you have an app that meets the above criteria.

++B