Transparent Data Encryption

Encrypt sensitive data transparently without writing a single line of code.

September/October 2005

It's your organization's worst nightmare: Someone has stolen backup tapes of your database. Sure, you built a secure system, encrypted the most sensitive assets, and built a firewall around the database servers. But the thief took the easy approach: He took the backup tapes, ostensibly to restore your database on a different server, start the database on it, and then browse the data at his leisure. Protecting the database data from such theft is not just good practice; it's a requirement for compliance with most laws, regulations, and guidelines. How can you protect your database from this vulnerability?

One solution is to encrypt the sensitive data in the database and store the encryption keys in a separate location; without the keys, any stolen data is worthless. However, you must strike a balance between two contradictory concepts: the convenience by which applications can access encryption keys, and the security required to prevent the key theft. And to comply with company and federal regulations, you need a solution immediately, without any complex coding.

A new feature in Oracle Database 10g Release 2 lets you do just that: You can declare a column as encrypted without writing a single line of code. When users insert the data, the database transparently encrypts it and stores it in the column. Similarly, when users select the column, the database automatically decrypts it. Since all this is done transparently without any change to the application code, the feature has an appropriate name: Transparent Data Encryption (TDE).

I covered the fundamentals of encryption in Oracle Database 10g in " Encrypt Your Data Assets" in the January/February 2005 issue of Oracle Magazine. To recap the key points, encryption requires you to apply an encryption algorithm and an encryption key to the clear-text input data. And to successfully decrypt an encrypted value, you must know the values of the same algorithm and key.

In that article, I described how to build an encryption infrastructure using the Oracle-provided encryption tools. Using Oracle Database 10g Release 2 and TDE, however, you won't need to build that infrastructure. All you have to do is define a column to be encrypted, and Oracle Database 10g creates a cryptographically secure encryption key for the table containing the column and encrypts the clear text data in that column, employing your specified encryption algorithm. Guarding this table key is very important; Oracle Database 10g encrypts it using a master key, which is stored in a secure location called a wallet, which can be a file on the database server. Encrypted table keys are placed in the data dictionary. When a user enters data into the column defined as encrypted, Oracle Database 10g gets the master key from the wallet, decrypts the encryption key for that table from the data dictionary, uses that encryption key on the input value, and stores the encrypted data in the database, as shown in Figure 1.

You can encrypt any or all the columns in a table. If a table has four columns, and columns 2 and 3 are encrypted, Oracle Database 10g generates a single encrypted table key for the table and uses it to encrypt those columns. On the disk, the values in columns 1 and 4 are stored as clear text and values in the other two columns are stored in encrypted format. Since the data is stored encrypted, all downstream components, such as backup and archived logs, also have the encrypted format.

When a user selects encrypted columns, Oracle Database 10g transparently retrieves the encrypted table key from the data dictionary, fetches the master key from the wallet, and decrypts the table key. Then the database decrypts the encrypted data on the disk and returns the clear text to the user.

With this encrypted data, if the data on the disk is stolen, it can't be retrieved without the master key, which is in the wallet and not part of the stolen data. Even if the wallet is stolen, the master key can't be retrieved from it without the wallet password. Hence, the thief can't decrypt the data, even if he steals the disks or copies the data files. This satisfies the compliance requirements for many regulations and directives. And all of this is done without changing the application or writing complex encryption and key management systems. Now I'll show you how to enable and use TDE.

The first time you use TDE, you must specify the wallet location, set the wallet password, and open the wallet.

1. Specify the wallet location.
When you first enable TDE, you must create the wallet where the master key will be stored. By default, the wallet is created in the directory $ORACLE_BASE/admin/$ORACLE_SID/wallet. So, if $ORACLE_BASE is /u01/app/oracle and $ORACLE_SID is SWBT4, then the wallet will be stored in the directory /u01/app/oracle/admin/SWBT4/wallet. You can also choose a different directory by specifying it in the sqlnet.ora file located in $ORACLE_HOME/network/admin directory. For instance, if you want the wallet to be in /orawall directory, place the following lines in the sqlnet.ora file:

ENCRYPTION_WALLET_LOCATION =
 (SOURCE=
   (METHOD=file)
     (METHOD_DATA=
       (DIRECTORY=/orawall)))

In this example, we'll assume the default location is chosen. You should also include the wallet location in regular backups.

2. Create the wallet.
Now you must create the wallet and set the password to access it. To do this, issue the following as a user with the ALTER SYSTEM privilege:

alter system set encryption key
authenticated by "remnant";

This command

• Creates the wallet in the location specified in Step 1

• Sets the password of the wallet as "remnant"

• Opens the wallet for TDE to store and retrieve the master key

The password is case-sensitive and must be enclosed in double quotes. Note that the password "remnant" doesn't show up in clear text in any dynamic performance views or logs.

Since the wallet is created only once, you need to perform the two previous steps only once. The wallet must be opened explicitly, however, after the database instance starts. When you create the wallet (as in Step 2, above), you also open the wallet for operation. After you create the wallet and set the password, every time you open the database, you'll have to open the wallet using the same password as follows:

alter system set encryption wallet open authenticated by "remnant";

You can close the wallet like this:

alter system set encryption wallet close;

The wallet must be open for TDE to work. If the wallet is closed, you can access all nonencrypted columns, but not encrypted columns.

To encrypt columns using TDE, all you need to do is add a simple clause— ENCRYPT—to the column definition. Before you do that, however, you must decide which type of encryption and key length to use. For a detailed discussion on this issue, refer to my "Encrypt Your Data Assets" article mentioned above.

On a regular schema, suppose you have a table of account holders as follows:

ACC_NO      NUMBER
ACC_NAME    VARCHAR2(30)
SSN         VARCHAR2(9)

Currently, the table has all data in clear text. You want to convert the column SSN, which holds the Social Security Number, to be stored as encrypted. You can issue

alter table accounts modify (ssn encrypt);

This statement does two things:

• It creates an encryption key for the table. If you change another column in the same table to use the encrypted format, the same table key will be used.

• It converts all values in the column to encrypted format.

This statement doesn't change the data type or the size of the column, nor does it create a trigger or a view.

By default, the algorithm AES with 192-bit key is used to encrypt. You can also choose a different algorithm by specifying the appropriate additional clause in the command. For instance, to use 128-bit AES encryption, you can use

alter table accounts modify (ssn encrypt using 'AES128');

You can use AES128, AES192, AES256, or 3DES168 (168-bit Triple DES algorithm) as clauses. The values are self-explanatory; for instance, AES256 is for Advanced Encryption Standard algorithm with 256-bit key.

After encrypting the column, you'll see the following when you describe the table:

SQL> desc accounts
Name           Null?          Type
------------   ------------   ---------------------
ACC_NO                        NUMBER
ACC_NAME                      VARCHAR2(30)
SSN                           VARCHAR2(9) ENCRYPT

Note the ENCRYPT keyword after the data type. To find the encrypted columns in the database, you can search the data dictionary view DBA_ENCRYPTED_COLUMNS. (TDE can't be enabled on a SYS-owned table.)

Since encryption and decryption consume CPU cycles, you must consider their effect on performance. When you access the nonencrypted columns of a table, the performance isn't any different from tables without TDE. When you access encrypted columns, however, there's a small performance overhead while decrypting during selects and encrypting during inserts, so you might want to encrypt columns selectively. If you no longer need to encrypt a column, you can turn it off with the following:

alter table account modify (ssn decrypt);

Also consider the use of indexes. In the above example, let's assume that there's an index on the column SSN, named in_accounts_ssn. If the query against the ACCOUNTS table has an equality predicate, as follows,

select * from accounts
where ssn = '123456789';

the index in_accounts_ssn is used. If the query instead uses a LIKE predicate, as in

select * from accounts
where ssn like '123%';

the index will be ignored, and a full table scan will be used. The reason is simple. The B-tree structure of the index makes sure that values with the same first few characters—"fraternal", "fraternity", and so on—are physically close together. When processing a LIKE predicate, Oracle Database 10g searches the index entries via a pattern match, and physical proximity helps speed up the index search, which is better than the full table scan.

However, if the column is encrypted, the actual values in the index are very different (since they're encrypted), and thus they'd be scattered all over the index. This makes index scans more expensive than full table scans. Hence, in this LIKE predicate query example, Oracle Database 10g chooses to ignore the index and does a full table scan.

In the case of equality predicates, the specific index entry is searched instead of a number of values following a pattern. So, an execution path using the index is faster than a full table scan, and the optimizer chooses to use the index. When you're deciding which columns to encrypt, consider how encryption affects indexes, and be aware that you might want to rewrite certain queries involving encrypted columns.

What if someone learns of the table keys or you suspect someone may have decrypted the encrypted table keys? You can simply create a new key for the table—in other words, rekey the table—and recreate the encrypted column values using the new table key by issuing a simple command. While you're at it, you might also want to choose a different encryption algorithm such as AES256. You can do both by issuing

alter table accounts rekey using 'aes256';

Encryption is all about hiding data, but sometimes it's easier to guess the value of encrypted data if there's repetition in the original plain text value of the data. For instance, a salary information table may contain repeated values. In that case, the encrypted values will be the same, too, and an intruder could determine all entries with the same salary. To prevent such an occurrence, a "salt" is added to the data that makes the encrypted value different even if the input data is same. TDE, by default, applies a salt.

If you try to create an index on an encrypted column, however, you can't include a salt in it. To remove the salt from the SSN column, for example, execute the following:

alter table accounts modify
(ssn encrypt no salt);

If you try to create an index on a column that's encrypted with a salt, you'll get an error, as in the following example:

SQL> create index in_acc_01
on accounts (ssn);
ORA-28338: cannot encrypt indexed column(s) with salt

You'll get the same error if you try to encrypt an indexed column with salt. Similarly, you can't use salt if there's an implicit index creation, such as when the columns are part of primary key or are defined as unique. Also, you can't use salt if the columns are part of a foreign key.

By default, if you use the Data Pump export utility (EXPDP) to export data from a table with encrypted columns, the data in the resulting dump file will be in clear text, even the encrypted column data. The following command exports the ACCOUNTS table—with its encrypted columns—and returns a warning:

$ expdp arup/arup tables=accounts
ORA-39173: Encrypted data has been stored unencrypted in dump file set.

This is just a warning, not an error; the rows will still be exported.

To protect your encrypted column data in the data pump dump files, you can password-protect your dump file when exporting the table. This password, specified as an ENCRYPTION_PASSWORD parameter in the EXPDP command, applies to this export process only; this is not the password of the wallet. Listing 1 shows the EXPDP command issued with the password "pooh". Note how the output of the command in Listing 1 does not show the password "pooh"; it's hidden as a string of asterisks. The resulting dump file will not have visible clear text data for columns encrypted with TDE.

Code Listing 1: Exporting a password-protected dump file

$ expdp arup/arup ENCRYPTION_PASSWORD=pooh tables=accounts
Export: Release 10.2.0.0.0 - Beta on Friday, 01 July, 2005 16:14:06
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
Connected to: Oracle Database 10 g Enterprise Edition Release 10.2.0.0.0 - Beta
With the Partitioning, OLAP and Data Mining options
Starting "ARUP"."SYS_EXPORT_TABLE_01":
arup/******** ENCRYPTION_PASSWORD=********* tables=accounts
Estimate in progress using BLOCKS method...
Processing ...
                            

When you import this encrypted dump file, you have to provide the same password used with the export, as shown in Listing 2.

Code Listing 2: Importing a password-protected dump file

$ impdp arup/arup ENCRYPTION_PASSWORD=pooh
tables=accounts table_exists_action=replace
Import: Release 10.2.0.0.0 - Beta on Friday, 01 July, 2005 16:04:20
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
Connected to: Oracle Database 10
 g Enterprise Edition Release 10.2.0.0.0 - Beta
With the Partitioning, OLAP and Data Mining options
Master table "ARUP"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "ARUP"."SYS_IMPORT_TABLE_01":
arup/******** ENCRYPTION_PASSWORD=********* table_exists_action=replace
Processing ...
                            

The following shows the result if you omit the ENCRYPTION_PASSWORD parameter during the import:

$ impdp arup/arup tables=accounts
ORA-39174: Encryption password must
be supplied.

The following shows the result if you provide the wrong password:

$ impdp arup/arup ENCRYPTION_PASSWORD
=piglet tables=accounts
ORA-39176: Encryption password is
incorrect.

The original export utility (EXP) cannot export a table with encrypted columns.

Protecting your data from attacks and complying with the myriad laws that govern business is no trivial task. TDE lets you immediately provide data encryption and compliance with absolutely no coding and key management complexity, so you can focus on more strategic efforts.