We have had several clients asked to implement Oracle eBS encryption schemes.
The Oracle Database provides *transparent data encryption” (TDE). The purpose of this is to encrypt sensitive data so that someone cannot steal the physical media of the database (e.g., disks) and read the data directly of the disk (bypassing Oracle security).
Encryption for clones -> This is less worthless than trying to encrypt columns in production tables (which people always seem to want to do for some reason).
we tend not to use encryption for the purpose of protect data in clones. Instead, I prefer to scrub the sensitive data completely, via a process like this…
1) DBA clones production
2) DBA runs “cleaning” script to
– A) create a randomly-generated, fake, but real-looking value for every sensitive value in the database. E.g., if you have 2,000 SSNs, it will create 2,000 dummy SSNs (but in the XXX-XX-XXXX format). It will store the real/fake SSN pairs in a mapping table.
– B) use the mapping table to update all real SSNs to their fake ones. This keeps consistency among the data.
– C) drop the mapping table (completely drop — watch out for the recycle bin, flashback database, etc). Doing this right takes a bit of thought.
3) DBA makes the cloned instance available to users/developers/etc
I like this approach for several reasons:
1) It keeps the cloned data “real looking”. A function, for example, that strips the dashes from an SSN will continue to work. Processes won’t fail or work funny because credit card numbers are too short or too long.
2) It gets around the problem that encrypted values are often longer than the unencypted values (especially if you want to base64 encode them so you can store them in a VARCHAR column, which you would). This becomes a problem when the encrypted values are too long to fit in the column width that Oracle designed.
3) It is irreversable. With encryption, someone who knows the process and key can reverse it. (True, you could use a cryptographic hash instead, but then you have potential collisions to worry about).
I’ve implemented this approach to protect SSNs, bank account numbers, credit card numbers, etc. It seems acceptable.