Featured Database Articles

Oracle's Transparent Data Encryption

Security. Each day it seems another breach is reported, another hack revealed, more personal user information is stolen, apparently despite the best efforts to thwart such attacks. It's becoming increasingly obvious that guarding against break-ins is simply not enough; one must be prepared for the maliciously inclined to succeed at hacking their way into 'secure' systems. For the Oracle DBA this may not be as daunting a task as it first appears. Oracle offers Transparent Data Encryption (TDE) [available with the Oracle Advanced Security Option] to protect sensitive data at the column, table or tablespace level, rendering any attempts to abscond with encrypted data files essentially useless. Let's take a deeper look into Oracle's TDE and see how it can protect vulnerable data.

TDE 'transparently' encrypts (and decrypts) data protected by an Oracle keystore/wallet. Using the chosen encryption algorithm (selected from several available) data is encrypted upon insert and decrypted upon select in protected databases as long as the wallet/keystore is open. The list of available encryption algorithms is shown below:

Given the available encryption and hashing options, data protected with TDE will be undecipherable without the keystore password.

Configuration begins with the creation of a wallet/keystore associated with the database. Some preliminary work needs to be done before attempting to create such a wallet; the local sqlnet.ora file must be modified to include the following entry:

Related Articles

This allows Oracle to find the wallet location, so it can be opened and used. Choose a directory (or create one specifically for this purpose) and create a full path for each ORACLE_SID that will have a keystore. For example, if the base directory is /oracle/mywallet and there are databases named SNORD and PLEEBO on that server the full paths to create will be /oracle/mywallet/SNORD and /oracle/mywalled/PLEEBO. Match the case of the directory name with the ORACLE_SID found in the ora_pmon_ process name.

Presuming the Advanced Security Option is licensed for your installation the next step is to create the wallet/keystore. The following script can be used to ensure no steps are missed:

Connect to the database as SYS AS SYSDBA to execute the commands; the &&amp: variables will contain the ORACLE_SID and the encryption password, in that order. As an example, if the script was named cr_wallet.sql it would be executed in this manner:

SQL> @cr_wallet snord plinkenfarb

Remember the encryption wallet password; it is a good idea to store it in a password safe of some sort for retrieval later.

Now that the wallet is created (if all of the commands were executed then it will be an auto-login wallet that will open when the database is started) and the master key is in use data can be encrypted. One way is to create encrypted tablespaces; in such cases all data in the tables is encrypted regardless of sensitivity. As stated earlier as long as the encryption wallet is open the data is transparently encrypted/decrypted without user intervention. It is also possible to encrypt specific columns of specific tables; in this case only the encrypted columns will be obfuscated if the data is somehow obtained by unauthorized persons so that not even a 'strings' command will return it. As with the full tablespace encryption any encrypted columns in tables will be automatically and transparently decrypted while the wallet is open.

Creating encrypted tablespaces is not much different from creating 'regular' tablespaces; the additional commands are highlighted in the example below which uses Oracle Managed Files to generate file names:

Multiple columns can be encrypted in a single table, and not every table in the schema needs to have columns encrypted. In the example above two columns are encrypted with different encryption levels. Notice the NO SALT directive for one column; this is necessary if the encrypted column is to be indexed. Even though SALT-ed columns make brute force attacks even harder to complete every NO SALT column is still quite secure against attacks.

TDE isn't the 'be-all' and 'end-all' of data obfuscation, but it can cause a would-be hacker to look elsewhere for 'easier' targets. As the old adage states -- "An ounce of prevention is worth a pound of cure." Consider licensing the Advanced Security Option and configuring TDE to protect valuable, personal data from attack. It just might get the DBA team a bit more sleep at night.

Advertiser Disclosure:
Some of the products that appear on this site are from companies from which QuinStreet receives compensation. This compensation may impact how and where products appear on this site including, for example, the order in which they appear. QuinStreet does not include all companies or all types of products available in the marketplace.