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 10g’s 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 10g’s 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 10g’s 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, “I’ve been thinking about what our good Director of Security and Compliance is going to ask our team to concentrate on next. I’d like us to get a head start on plugging any other security gaps, so I’ve 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 we’re saving on tape? Are those really secure? Our whole production database is on those tapes – what if someone ‘borrowed’ those tapes as they’re 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 Oracle10g’s 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 CIO’s 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
Let’s 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, I’ll create a new external table, HR.XT_EMPLOYEE_INFO, by combining several columns from the sample schema’s 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 table’s column declarations.
Listing 4.1 also shows the second attempt at creating the encrypted table to enforce encryption. Within that listing, I’ve also provided links to the actual external table’s 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 I’m never in danger of revealing the master encryption password for the database. Also note that the external table’s 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 that’s been created via the ORACLE_DATAPUMP access method, it’s no surprise that I can also encrypt a dump set that’s 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. That’s 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, I’ve also remapped the two tables that were successfully exported in the previous step into a new schema, NEWHR. I’ve also specified a value for the ENCRYPTION_PASSWORD parameter in its “obfuscated” format.
Encrypting Oracle Backup Sets
I’ll 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, she’s 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. It’s 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.
I’ve configured transparent mode for my RMAN backups by issuing the CONFIGURE ENCRYPTION FOR DATABASE ON; configuration command in Listing 4.5. Note that I’ve 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 database’s 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 I’ve 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. It’s 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. I’ll demonstrate this by first temporarily disabling transparent data encryption using the ALTER SYSTEM SET ENCRYPTION WALLET CLOSE; command from within SQL*Plus. I’ll 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 company’s firewall. In Listing 4.7 I’ve 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:
- Encryption of RMAN backup sets is only available in Oracle 10gR2 Enterprise Edition; therefore, the COMPATIBLE initialization parameter must be at least 10.2.x.x.
- Only RMAN backup sets can be encrypted; image copy backups cannot be encrypted.
- Finally, if I change or reset the current database master encryption key, the database can still be restored using an older master key. The database master key can in fact be reset at any time, and RMAN will still be able to restore any encrypted backup made for the database.
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:
- If the backup set was encrypted in transparent mode, RMAN attempts to obtain the decryption password from the Oracle Wallet only. If the wallet is not open, RMAN will return an error message and abort the RESTORE operation.
- If the backup set was encrypted in dual mode, RMAN tries to retrieve the decryption password from the Oracle Wallet first. If the wallet isn’t open, however, RMAN then looks for a password string that’s been specified in the SET DECRYPTION IDENTIFIED BY <password>; directive within the RMAN script. If a password can’t be found in either location, RMAN returns an error message, and the RESTORE operation will be aborted.
- If I used the password only mode to encrypt the backup set, RMAN only looks for the password as specified in the SET DECRYPTION IDENTIFIED BY <password>; directive in the RMAN script. If no such directive is found, RMAN returns an error message and aborts the RESTORE operation.
I’ll illustrate this last scenario in Listing 4.8. First, I’ll remove the datafile for the TBS_ENCRYPTED tablespace and “bounce” the database so that the tablespace’s 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 wasn’t specified. (Note that I used the VALIDATE directive to determine if the RESTORE operation is possible; I’ve 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, it’s 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.
Conclusion
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 Security’s 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 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:
B14191-01 Oracle Database Backup and Recovery Advanced User’s 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 Administrator’s Guide
