Oracle 10gR2 Security, Part 4: Securing Data Extracts, Exports and Recovery Files
January 26, 2007
Synopsis. Oracle 10gR2 includes some security features that have been long-overdue: the ability to encrypt DataPump Export dump sets and Recovery Manager (RMAN) backup sets. This article the fourth and final in this series discusses and illustrates how any Oracle DBA with a reasonable level of experience can implement this unprecedented level of database security.
In the previous three articles in this series, we first explored the fine-grained auditing capabilities of Oracle 10gs enhanced DBMS_FGA package, and we then investigated ways to forestall a user session from accessing or modifying data via the fine-grained access control capabilities of Oracle 10gs enhanced DBMS_RLS package. In the last article, we discovered how simple it is to encrypt values stored within table columns and indexes using Oracle 10gs new Transparent Data Encryption (TDE) features.
To conclude our ongoing saga of security pratfalls, imagine that your CIO stops by your desk early one morning and says, Ive been thinking about what our good Director of Security and Compliance is going to ask our team to concentrate on next. Id like us to get a head start on plugging any other security gaps, so Ive asked every team in our IT group to think about any possible vulnerabilities anywhere in our systems.
The CIO continues, One possible exposure that keeps me awake at night are the datafeeds we currently send out of house to our external vendors. I know we also send external feeds across our network for processing in our data warehouse in Osaka, Japan. In addition, what about the disaster recovery files for our production database here that were saving on tape? Are those really secure? Our whole production database is on those tapes what if someone borrowed those tapes as theyre being shipped to the storage vault?
You wait until your CIO has finished massaging the furrows in his brow, and then you grin and say, I hate to sound like a broken record, sir, but once again Oracle 10g has our bases covered. You then explain how Oracle10gs advanced security features permit you to encrypt Oracle DataPump export dumpsets, external tables, and RMAN backups and watch as a few more worry lines disappear from your CIOs visage.
Preparing for Encryption
Oracle 10g Transparent Data Encryption (TDE) features are at the heart of many of the encryption capabilities I will be exploring in this article. I explained how to set up Transparent Data Encryption in the prior article in this series. For the remainder of this article, please assume that an Oracle Wallet has already been created, the Oracle Wallet has already been opened, and finally that a master encryption key has been established already for the database. (Figures 3.1 through 3.7 illustrate how to set up the Oracle Wallet so that TDE features are enabled, while Listing 3.1 shows the necessary commands for preparing an Oracle database to take advantage of these features.)
Encrypting External Tables
Lets first turn our attention to a new feature in Oracle 10gR2, namely the ability to create an external table directly from any source data, including standard (i.e. non-external) and external tables. A new access method, ORACLE_DATAPUMP, lets me construct an external table using a CTAS (i.e. CREATE TABLE AS SELECT) SQL statement method and write the contents of that external table to one or more files in a designated directory.
To illustrate, Ill create a new external table, HR.XT_EMPLOYEE_INFO, by combining several columns from the sample schemas HR.EMPLOYEES table with data from the HR.EMPLOYEE_SECURED table that I created in the previous article. (See Listing 3.2 from that article to view the structure of HR.EMPLOYEE_SECURED.) Listing 4.1 shows the statements I executed to create the new external table.
Since the Oracle Wallet I created in the last article is still open, Oracle 10g allows me to read the contents of HR.EMPLOYEE_SECURED when I create the new external table. However, note that even though my query is reading an encrypted table, the data in the external table that is created is not encrypted by default. To enable encryption of data in the external table, I must add the ENCRYPT attribute to the tables column declarations.
Listing 4.1 also shows the second attempt at creating the encrypted table to enforce encryption. Within that listing, Ive also provided links to the actual external tables files to illustrate that Oracle 10gR2 does indeed encrypt the data.
So how does Oracle 10gR2 handle reading data from an external table that contains encrypted data? I simply need to include that password string within the CREATE TABLE DDL statements that I use to create the external table on, say, a different server or in a different physical location on the same server. Each time that a new external table is created, Oracle 10gR2 uses the current master key to generate a new password string for the external table, so Im never in danger of revealing the master encryption password for the database. Also note that the external tables password string can be specified in obfuscated format if I so choose.
Handling Encrypted Data with Oracle DataPump
Since I can encrypt and decrypt data stored in an external table thats been created via the ORACLE_DATAPUMP access method, its no surprise that I can also encrypt a dump set thats been constructed via Oracle 10g DataPump operations because DataPump uses the same access method to store data in a dump set whenever the source table contains LOBs or LONG columns.
Listing 4.2 shows an example of creating a DataPump dump set as part of a DataPump export operation. Note that I must set a value for the ENCRYPTION_PASSWORD parameter to activate encryption for the entire dumpset. The value for this parameter can be placed on the DataPump command line or within a parameter file, and it can be obscured by supplying it in its obfuscated format as well.
One of the advantages of this encryption approach is I can transfer the dumpset containing the encrypted data to another target database without concern that the data stored within the dumpset could be compromised, even if I am transporting the dumpset across an unsecured network link. Thats because without the encryption password, the dumpset cannot be used as a source for a DataPump import operation.
Listing 4.3 demonstrates how to import an encrypted DataPump export dumpset back into the same database. In this example, Ive also remapped the two tables that were successfully exported in the previous step into a new schema, NEWHR. Ive also specified a value for the ENCRYPTION_PASSWORD parameter in its obfuscated format.
Encrypting Oracle Backup Sets
Ill be the first to admit that I never really thought about my database backups as potential security risks! However, if a hacker is capable enough to ferret out sensitive information from the contents of an external table or DataPump export file, shes probably more than crafty enough to crack open a backup file to obtain unprotected data.
The good news is that Oracle 10g offers multiple encryption algorithms for RMAN backup sets. As shown in Listing 4.4, the new V$RMAN_ENCRYPTION_ALGORITHMS dynamic view can be queried to show all available algorithms. Oracle 10g also offers three different methods for encrypting RMAN backup sets: transparent mode, password mode, and dual mode. Deciding which mode to use mostly depends on how the RMAN backups will be used within an enterprise.
Method 1: Transparent Mode. This method uses Transparent Data Encryption (TDE) features to encrypt the RMAN backup sets. Its therefore best suited for encrypting backups that are going to be used for restoration and recovery operations on the same server, since the Oracle Wallet is usually set up on a per-server basis.
Ive configured transparent mode for my RMAN backups by issuing the CONFIGURE ENCRYPTION FOR DATABASE ON; configuration command in Listing 4.5. Note that Ive also changed the encryption algorithm for all database backup sets to AES192 using the CONFIGURE ENCRYPTION ALGORITHM '<algorithm>; command. As this example shows, no other changes are required to existing RMAN scripts because the CONFIGURE command saves this RMAN encryption configuration within the databases control file.
Controlling Backup Set Encryption Scope: CONFIGURE vs. SET. While the CONFIGURE command lets me establish and control backup set encryption at the database level, I can also override its scope via the SET command from within an RMAN command script. For example, even though I may have enabled encryption globally, I can still disable it for selected tablespaces with the SET ENCRYPTION ON FOR TABLESPACE <tablespace_name>; directive. Contrariwise, I could also globally disable encryption of RMAN backup sets and then activate it for specific tablespaces, as Ive illustrated at the end of Listing 4.5.
Method 2: Password Mode. In this mode, RMAN requires one additional directive to set a password before backups can be encrypted. Since I might not have installed TDE features on every database server it does incur additional licensing costs, after all! this method is more appropriate for encrypting RMAN backups that are going to be shipped to another server via a network connection. Its also appropriate for RMAN backups that are going to be stored on alternate media in a vaulted, protected location.
To implement password mode, I only need to add one additional line to my current RMAN backup script. The SET ENCRYPTION IDENTIFIED BY <password> ONLY; directive tells RMAN that any backup sets created during this RMAN session will be encrypted using just the specified password. Ill demonstrate this by first temporarily disabling transparent data encryption using the ALTER SYSTEM SET ENCRYPTION WALLET CLOSE; command from within SQL*Plus. Ill then specify a password string within my RMAN script and initiate a backup for just two tablespaces, EXAMPLE and TBS_ENCRYPTED, as shown in Listing 4.6.
Method 3: Dual Mode. This method provides the best of both worlds. If an Oracle Wallet is open when I create RMAN backup sets, then Oracle will apply encryption to them using Transparent Data Encryption methods; otherwise, RMAN will use the specified password for encryption. Therefore, dual mode is most useful for backups that may be utilized for restoration and recovery purposes on either side of your companys firewall. In Listing 4.7 Ive shown an example of an RMAN script that implements dual-mode encryption.
Limitations. There are some minor but nevertheless important restrictions that Oracle 10g places on encrypting RMAN backup sets:
Using Encrypted Oracle Backup Sets During Recovery Operations
Although these encryption methods certainly guarantee the security of my backup sets, there are some important implications when using an encrypted backup set during RMAN RESTORE and RECOVER operations:
Ill illustrate this last scenario in Listing 4.8. First, Ill remove the datafile for the TBS_ENCRYPTED tablespace and bounce the database so that the tablespaces corresponding datafile requires restoration and recovery via RMAN. Since I used the password-only mode when I created the most recent backup for this datafile, RMAN issued an ORA-19913 error during this first attempt at restoration because the password string wasnt specified. (Note that I used the VALIDATE directive to determine if the RESTORE operation is possible; Ive found that this can save valuable time while attempting restoration of an encrypted backup set.) When I reissue the RMAN command script with the proper password-only specification, however, RMAN readily finds the datafile, restores it, and proceeds with recovery.
Since the database generates a new encryption key for each encrypted backup, backup encryption keys are never stored in the clear. Depending upon the RMAN encryption mode I have selected, the key is encrypted using the specified password string, the DB master key, or both. This does reveal a critical caveat for using password-only RMAN encrypted backups: If I lose the password for the backup set I need to restore, there is no way to restore that backup set. Of course, this offers excellent protection should the backup set fall into the wrong hands, but it also means that I must carefully guard the password(s) that have been set up.
Finally, its important to remember that backup encryption can possibly result in a deleterious effect on backup performance because of the overhead of encrypting all the data in the backup. However, I may be able to overcome any performance issues by allocating additional channels to increase the speed of the RMAN encrypted backups.
Oracle 10g seals virtually every potential gap in database security with its robust encryption capabilities. DataPump Export dump sets, external tables created with the ORACLE_DATAPUMP access method, and Recovery Manager (RMAN) backup sets can be encrypted to prevent leakage of sensitive information. In addition, Oracle Advanced Securitys Transparent Data Encryption (TDE) features offer simple methods to enforce encryption at the column, table, datafile, backup set, and database levels.
References and Additional Reading
Even though Ive 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 Ive drawn upon the following Oracle 10gR2 documentation for the deeper technical details of this article:
B14191-01 Oracle Database Backup and Recovery Advanced Users Guide
B14194-03 Oracle Database Backup and Recovery Reference
B14200-02 Oracle Database SQL Reference
B14214-01 Oracle Database New Features Guide
B14215-01 Oracle Database Utilities
B14266-01 Oracle Database Security Guide
B14268-02 Oracle Database Advanced Security Administrators Guide