1.0 Introduction

Oracle provides many ways to protect the data from unauthorized access, such as in built grant mechanism, FGAC etc. These methods do go a long way in protecting the data from unauthorized access. But these methods don’t protect us if someone steals the datafile itself and reads the data directly from the data files. We could use dbms_obfuscation_toolkit , dbms_crypto to store the data crypted on the datafile itself. But using these methods are quite cumbersome and they are programmatic method for encryption. Till 10g R2 Oracle did not provide any out of the box mechanism to encrypt data.

In 10g R2 Oracle, now provides out-of-box method to protect data at the operating system level. The transparent data encryption (TDE) feature provides a “out-of-box” method to store sensitive data encrypted on the data files, to prevent access to the data at the operating system level.

2. How does it work ?

Transparent Data Encryption is a key based encryption system. Once encrypted one would need the key to decrypt the data. One just needs to mark the column as encrypted to encrypt the column .

e.g.

When a table contains encrypted columns, Oracle encrypts them all with a single key (key specific for the table) regardless of how many columns are encrypted. All the table keys are encrypted with a database master key and stored in a dictionary table (enc$). The database master key is stored in a Oracle wallet, which is protected by a password. So to gain the access to the data stored in the data files one would need access to the data file, and oracle instance to mount the datafile, the Oracle wallet and one would also need to know the password to access the Oracle wallet.

The diagram below describes the encryption process:

When someone with proper authorization selects data from the table, Oracle gets the encrypted table key from the dictionary tables, decrypts it with the master key obtained from the wallet, uses this key to decrypt the encrypted data and returns the decrypted data to the client.

The diagram below describes the decryption process: