Transparent Data Encryption (TDE)

[Former Member]

Dear all,

My client has asked me to implement transparent data encryption on an Oracle database. After reading note 974876, I went ahead and implemented it all and I have to say it was pretty easy to do and didn't take much time.

While I was validating the encryption results, I realised that the old versions of the encrypted data was still visible on datafile level. So, while there is an encrypted version of the data in the datafile, which is the one that is visible using se16 or select queries on database level, there is another version as well, which is the unencrypted version. I could see this data using blockdump. After looking at google, I found the below link that describes exactly what I am experiencing

http://www.pythian.com/blogs/510/oracle-10g-transparent-encryption-not-so-encrypted

and in fact, similar finding are in the Oracle website: The solution they propose there is the following

-

---

Create a new table space in a new data file (CREATE TABLESPACE ... )

Encrypt the clear text values in the original table space and data file

(ALTER TABLE ... ENCRYPT)

Repeat 2.) for all tables that contain encrypted columns

Move all tables from the original table space into the new data file

(ALTER TABLE ....MOVE... )

Drop the original table space (DROP TABLESPACE). Do not use the 'and

datafiles' parameter; Oracle recommends to use stronger methods for OS - level operations; see 6.)

Use tools like 'shred', 'eraser', 'SDelete' or other commands for your

platform to delete the old data file on the OS level.

-

---

Any ideas what you should do in this case in a SAP environment. I was thinking of following the instructions above, which is really a tablespace reorganization but I would be interested in other opinions as well.

I opened a message to SAP about this but came back to me saying that this is a consulting issue. Nevertheless, I believe that this should have been covered in note 974876, since this note claims to give you instructions on how to configure encryption and then you end up with having datafiles on disks (and in tapes going offsite) containing encrypted and unencrypted versions of the same data.

Many thanks

Andreas

[stefan_koehler · Accepted solution]

Hello Andreas,

> Any ideas what you should do in this case in a SAP environment.

The only supported way is to move the objects into another tablespace and drop the old one. That's sad but true.

You can also reorganize the tables in the tablespace itself and fill up the "free" space with a dummy table, but then you have no guarantee that the PCTFREE part is not containing any decrypted data.

But this is also a reorganization and so you can also move the tables to another tablespace and rename it later.

Btw. the sapnote #974876 describes also how you can encrypt and reorg the data in one step (TDE activation by online reorganization)

Regards

Stefan

[Former Member · Accepted solution]

Thanks for the information

In fact, SAP updated the note recently at my request, so it is much clearer now what should be done

Thanks again

Andreas