Transparent Data EncryptionBy Arup Nanda
Encrypt sensitive data transparently without writing a single line of code.
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).
How It Works
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.
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.
alter system set encryption key authenticated by "remnant";
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.
Open the Wallet
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";
alter system set encryption wallet close;
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)
alter table accounts modify (ssn encrypt);
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');
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
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);
select * from accounts where ssn = '123456789';
select * from accounts where ssn like '123%';
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.
Key and Password Management
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';
Want "Salt" with That?
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);
SQL> create index in_acc_01 on accounts (ssn); ORA-28338: cannot encrypt indexed column(s) with salt
Using Data Pump with TDE
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.
$ impdp arup/arup ENCRYPTION_PASSWORD =piglet tables=accounts ORA-39176: Encryption password is incorrect.
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.
Arup Nanda (firstname.lastname@example.org) is the lead DBA at Starwood Hotels and Resorts in White Plains, New York. He is the coauthor of Oracle Privacy Security Auditing from Rampant Press, 2003.