In the previous articles in this series, we first discussed how to implement fine-grained auditing using Oracle 10g’s enhanced DBMS_FGA package. We then looked at methods that prevent users from querying or changing data via the fine-grained access control tools that are supplied with the enhanced DBMS_RLS package in Oracle 10g.

To continue the scenario I set forth in those articles, imagine next that your CIO asks you and your fellow Oracle DBAs to attend a meeting with the newly-appointed Director of Security and Compliance at your company. “We’ve heard good things about your team,“ the Director tells your CIO, “especially that thing with catching the thief in Accounts Payable. And we’re certainly impressed with how easily your team implemented those new security features to prevent any more violations of accounting policies.”

The new Compliance Director continues, “And now we need to go one step further. To insure that we’re in compliance with Sarbanes-Oxley guidelines, we’ll need to make sure that we’ve encrypted all critical and sensitive data in all of our databases. And this means not just our critical financial data - we’ll need to make sure all sensitive employee data is encrypted too.”

Then the Compliance Director says, “Oh, and I almost forgot ... this needs to be done by the end of 2006 so that we can add it to our end-of-year Sarbanes-Oxley compliance report. That gives you plenty of time to get it done.” Your CIO looks concerned, since it’s already late November. But then you smile at the CIO and say, “No problem. We can make that date easily.” Once again, Oracle 10g’s robust security features save the day as you explain your plans to encrypt data within the database.

Encrypting Sensitive Data

I’ve demonstrated how to determine how to track which users are performing questionable or fraudulent transactions with Oracle 10gR2’s with Fine-Grained Auditing (FGA) features. I’ve also illustrated how Oracle 10gR2 insures that access to sensitive data via queries and DML statements can be controlled with the enhanced row-level security features.

As robust as these features are, however, it’s more important than ever to guarantee that data is encrypted within the database’s physical files as well. For example, if a malicious intruder were to gain even read-only access to my database’s datafiles, then all of the protections afforded by fine-grained auditing and row-level security would be bypassed because unencrypted data could be read “in the clear.” While I grant it might take some additional manipulation to make sense of these data, it’s a risk that I’d rather not take.

Prior to Oracle 10g, the only tool I had to encrypt data was the DBMS_OBFUSCATION_TOOLKIT Oracle-supplied package. This package provided me with the ability to apply predefined encryption methods to data stored within a specific column in an Oracle database table. However, DBMS_OBFUSCATION_TOOLKIT has some serious drawbacks:

• Limited encryption algorithms. DBMS_OBFUSCATION_TOOLKIT only provides a few encryption methods in earlier releases, specifically, Triple DES (3DES) and DES.
• Non-transparent encryption and decryption. DBMS_OBFUSCATION_TOOLKIT provides no direct method to directly encrypt data in columns that need protection. I’m thus forced have to develop a separate function to encrypt data for each individual column. In addition, decrypting column data that’s been encrypted with DBMS_OBFUSCATION_TOOLKIT is equally difficult: I need to create a decryption function for each encrypted column.
• Difficult implementation. To apply encryption and decryption for queries, I’ll need to create a view that accesses that table and displays the encrypted data by calling the decryption function for that column. For DML operations, it’s even more complex: I will most likely need to create triggers against INSTEAD-OF views that implement the encryption method I’ve chosen.

Note that Oracle 10g does supply a new package named DBMS_CRYPTO that eases some of these difficulties and is obviously designed to replace DBMS_OBFUSCATION_TOOLKIT. DBMS_CRYPTO also provides significantly more encryption algorithms and sophisticated ciphers. (See my prior article on DBMS_CRYPTO for additional details on this new package in Oracle 10g.)

The Ultimate Encryption Solution: Transparent Data Encryption (TDE)

However, what I really hoped for was an even better alternative to DBMS_OBFUSCATION_TOOLKIT, something that would allow me to encrypt and decrypt columns without having to create any additional views and triggers. My hopes were answered with the introduction of Transparent Data Encryption (TDE) features in Oracle 10g. Implementing Transparent Data Encryption is simple and elegant:

• First, I’ll need to create and store a public encryption key for my database in an Oracle Wallet using either command-line utilities or the Oracle Wallet Manager (OWM).
• Once the Oracle Wallet is created, I open it for use against my database, and then I create a separate master encryption key for all data that needs to be encrypted inside the database.
• When I choose to encrypt a column’s data, an external Oracle application called the External Security Module (ESM) uses the database’s master encryption key to apply encryption and decryption to the data using one of four supplied encryption algorithms. Oracle stores metadata about which columns are encrypted column in a special (and secure!) data dictionary table in the database.

Creating an Oracle Wallet with Oracle Wallet Manager (OWM)

To create an Oracle Wallet for the first time, I’ll execute the Oracle Wallet Manager (OWM) utility by either selecting it from the list of available Oracle 10g applications (in Windows NT), or by typing the command owm from within a terminal window prompt (in all other operating system environments). Figure 3.1 shows the initial screen that OWM displays.

When I choose to create a new Wallet, OWM will display a warning that the default wallet directory doesn’t exist (Figure 3.2). I can then supply the desired directory path (Figure 3.3). Once this directory is chosen, OWM will prompt for a password for the Wallet (Figure 3.4). This password must conform to minimum password security rules (i.e., at least one capital letter, one lowercase letter, one number, and one special character) and must be a minimum of eight characters in length.

Finally, OWM will offer the option to create additional security certificates (Figure 3.5), but I’ll simply ignore this option for now. The result of a successful Wallet creation is shown in Figure 3.6. Now the new Oracle Wallet is created, OWM allows me to save the wallet file (usually named ewallet.p12) in the directory of my choice. As shown in Figure 3.7, I’ve chosen to save my wallet file in my database server’s /home/oracle/_dba/ folder. This is the file that the External Security Module (ESM) will read when it’s time to apply encryption to each selected column in the database.

Now that the Oracle Wallet is prepared, I’ll next need to configure my Oracle database to utilize it. First, I need to add parameters to my database’s SQLNET.ORA network configuration file. I’ll then open the Wallet for access from within the database by issuing the ALTER SYSTEM SET WALLET OPEN IDENTIFIED BY <password>; command from within SQL*Plus. Once the Wallet has been opened successfully, I’ll create the database’s master encryption password for the database by issuing the ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY <password>; command. Listing 3.1 shows the commands I issued to prepare the database for column encryption.

Exploiting Unencrypted Data In Oracle Datafiles

Now that my Oracle Wallet is set up and the database itself is ready to handle column-level encryption, let me first illustrate how easy it is to obtain information directly from the datafile of an Oracle tablespace when data is not encrypted. First, I’ll create a new, relatively small tablespace, TBS_ENCRYPTED, and I’ll name its datafile tbs_encrypted01.dbf. I’ll then create a new table, HR.EMPLOYEE_SECURED, in that new tablespace, and I’ll load some test data into that table. See Listing 3.2 for the code I used to create these objects.

Now that I have some test data to view, allow me to show you how easy it is to browse the contents of a datafile directly to view unencrypted data stored within. As shown in Listing 3.3, I’ll first take the TBS_ENCRYPTED tablespace offline, and then I’ll use the Linux strings utility to browse the contents of this tablespace’s datafile. This utility reads a file and then returns output that filters out all character strings. Note that it’s possible to view the unencrypted values for Social Security Number using this method. (Of course, it might take a little bit of interpretation to find significant data using this method, but hackers are an industrious and ingenious lot.)

Encrypting Columns With TDE: The ENCRYPT command

Now that I’ve opened the Oracle Wallet and have created the database’s master encryption key, it’s a relatively simple matter to apply encryption directly to several columns in my database’s tables. Oracle 10g allows me to choose among several options for encrypting data within columns:

Encryption Algorithms. There are four different algorithms available:

• AES192: Advanced Encryption Standard with a 192-bit key size. This is the default encryption algorithm; it will be applied if I attach the ENCRYPT attribute to a column.
• AES128: Advanced Encryption Standard with a 128 bit key size.
• AES256: Advanced Encryption Standard with a 256-bit key size.
• 3DES168: Triple Data Encryption Standard with a 168-bit key size.

“Salted” vs. “Unsalted” Encryption. By default, Oracle 10g also “salts” the encrypted column by adding a random string to the data value before it’s encrypted. This makes it more difficult for an intruder to break down encrypted data because it disrupts the intruder’s ability to apply standard pattern matching techniques. Oracle 10g also permits the deactivation of this default salting method by specifying the directive NO SALT.

To illustrate these features, I’ll add different encryption levels to four of the columns in table HR.EMPLOYEE_SECURED as shown in Listing 3.4:

• Column SOCSECNBR will be encrypted using the default AES192 encryption method. Even though it’s not necessary because it’s the default value, I’ll specify the SALT attribute.
• Likewise, columns ETHNICITY and TERMINATION_RSN will be encrypted using the default AES192 encryption method and will be ”salted” by default.
• The TERMINATION_DT column will be encrypted using the default AES192 encryption method; however, it will not be “salted” because I’ve specified the NO SALT attribute.

Encryption of Indexed Values. Since Oracle may be able to utilize an index value to answer a query much faster than querying data in a table directly, Oracle 10g also ensures that indexed values are encrypted. Oracle 10g does require that “unsalted” encryption be applied to the indexed column. To illustrate this, I’ll add an index on the AP.VENDORS.CREDIT_CARD column in Listing 3.4, and then I’ll attempt to activate standard encryption for that column. Note that Oracle 10g will initially reject my attempt to encrypt the data in that column because I didn’t specify the NO SALT directive; once I do so, Oracle 10g will allow me to encrypt the data successfully.

Viewing Metadata For Encrypted Columns. Oracle 10g also provides methods to retrieve metadata about the columns that have been encrypted with Transparent Data Encryption. The query in Listing 3.5 accesses the DBA_ENCRYPTED_COLUMNS data dictionary table, and it shows the five columns that have been encrypted up to this point, as well as their current encryption algorithm and whether “salting” has been employed.

Applying Encryption During Table or Column Creation. Oracle 10g permits me to apply encryption to a newly-created column simultaneously during its addition to the table. I can also apply encryption to any column that requires additional security during the creation of a new table simply by specifying the ENCRYPT directive for that column.

Changing A Table’s Encryption Algorithm. Oracle 10g will apply the same encryption algorithm for all columns that are encrypted within the table. In other words, it’s not possible to apply 3DES168 encryption to one column, and AES192 encryption to another: All columns in the table are encrypted using the identical algorithm. If I attempt to encrypt a column using an encryption algorithm different from the one already in use, Oracle 10g will return an error; however, Oracle 10g will allow me to change the encryption algorithm for the entire table using the ALTER TABLE <table_name> REKEY USING <new_encryption_algorithm>; command.

Removing Encryption From a Column. To remove encryption on a column that has been previously encrypted, I simply issue the ALTER TABLE <table_name> MODIFY <column_name> DECRYPT; command against the encrypted column.

Listing 3.6 offers examples of how to add a new encrypted column, how to “rekey” an encrypted table so that it uses a different encryption algorithm, how to remove “salting” from a previously “salted” column, and how to remove encryption from a previously encrypted column. I’ve also included the results of querying the database’s metadata in DBA_ENCRYPTED_COLUMNS after these examples have been applied to the database.

Proof of Concept. To prove that Transparent Data Encryption is actually working, I’ve rerun the same tests against the datafile for the TBS_ENCRYPTED tablespace, and the results of these tests are shown in Listing 3.7. Note that although the strings command still returns some character string values, notice that the strings are essentially meaningless collections of encrypted data.

Deactivating Transparent Data Encryption: Closing the Wallet

What happens if the Oracle Wallet is closed while the database is open? The answer is that the data stored in the encrypted columns cannot be accessed; however, all non-encrypted data can still be accessed.

I’ve demonstrated in Listing 3.8 what happens when the Wallet is closed by issuing the ALTER SYSTEM SET ENCRYPTION WALLET CLOSE; command. I then issued a query against two columns in AP.VENDORS that are not encrypted, and the query returned the expected result from that table. Note that when I issue a query against an encrypted column, however, Oracle returns an ORA-28365 exception and informs me that the Oracle Wallet is not open at this time.

Now, A Warning: Additional Licensing Costs

I would be remiss if I neglected to mention that these Transparent Data Encryption features will most likely incur additional licensing costs because TDE is considered part of Oracle Advanced Security. However, if you must absolutely ensure that sensitive data stored within your databases is fully encrypted, and you decide to pursue encryption via DBMS_OBFUSCATION_TOOLKIT or DBMS_CRYPTO, I’d strongly suggest that you weigh the costs of developing a custom solution via these packages versus the relatively simple methods that Transparent Data Encryption provides.

Conclusion

With the addition of Transparent Data Encryption features, Oracle 10g ensures that a malicious intruder’s efforts to view data that’s stored within the physical datafiles that comprise an Oracle database’s tablespaces are thwarted by encrypting data with robust, difficult-to-decipher encryption algorithms. These new features are relatively simple to implement and require virtually no maintenance to insure their viability.

References and Additional Reading

Even though I’ve hopefully provided enough technical information in this article to encourage you to explore with these features, I also strongly suggest that you first review the corresponding detailed Oracle documentation before proceeding with any experiments. Actual implementation of these features should commence only after a crystal-clear understanding exists. Please note that I’ve drawn upon the following Oracle 10gR2 documentation for the deeper technical details of this article:

B14214-02 Oracle Database New Features Guide

B14258-01 PL/SQL Packages and Types Reference

B14268-02 Oracle Database Advanced Security Administrator’s Guide

B14266-01 Oracle Database Security Guide

Previous   Next

Back to DBAsupport.com