The following is another entry I wrote in 2010 that I’ve refrained from publishing. It’s a follow on to this overview of Oracle 11g security.
Keeping data safe in today’s computing environment demands encryption. In fact, the law often requires it. Several states in the USA now have laws stating that companies must safe guard personal data belonging to consumers. If the data gets stolen, and if the data is not encrypted, then the company that suffered the data breach must compensate consumers for their loss of privacy. So naturally, companies want to encrypt personal information stored within their databases. Oracle 11G makes such encryption straightforward. This article summarizes older encryption features and details the new encryption features in 11G releases 1 and 2.
Here’s a quick rundown on what’s been available for encryption for the last few releases. The ability to encrypt data as it travels across the network to and from the database has been available for several releases. Called sqlnet encryption, it requires purchasing the extra cost option Advanced Security and dates back to at least Oracle 8i. Note that sqlnet encryption does not encrypt the data once it is inside the database residing on disk, or at rest, as the catch phrase goes. Another feature available in Oracle 8i and onward are PL/SQL functions to encrypt and decrypt a piece of data. These functions could be used with custom programming to ensure the encryption of key data in the database. However, such functions demand custom programming and make it impossible to index the columns storing such data. Oracle 10g announced the arrival of Transparent Data Encryption (TDE), enabling the database to encrypt all data in a column such that a programmer did not have to alter a SQL statement to encrypt and decrypt it. Users needed only to set up the encryption wallet for the Oracle instance and then run an ALTER TABLE statement to modify the column to be encrypted. Provided a user had SELECT privileges on the table with encrypted columns, that user could transparently access the data using exactly the same SQL statement required if the column were not encrypted. And a hacker with unauthorized access to the underlying data file would not be able to see the data. Version 10g also enhanced the RMAN backup utility so it could create encrypted output files.
Despite the TDE feature, encryption in 10g had serious flaws. You had to specify individual columns and encrypt them one at a time. Certain column types could not be encrypted, most notably BLOB columns. If you created a new table or added columns with sensitive data, you had to remember to encrypt them.
Oracle 11G solves these short comings. The new tablespace encryption features enables an entire tablespace to be encrypted. Any new table created in that tablespace will get encrypted. 11G also provides encryption for the export data pump (expdp) utility. And BLOB files can be now be encrypted by means of using an 11G feature called SecureFiles. Please note that all three of these features require purchase of the extra cost option Advanced Security, which can only be used with the Enterprise Edition of Oracle database.
Here’s how to implement tablespace encryption. As in previous releases, you start by creating the Wallet, which contains the master encryption key for the entire database.
The default location for the wallet is $ORACLE_BASE/admin/$ORACLE_SID/wallet. You can override this in the $TNS_ADMIN/sqlnet.ora file by adding an ENCRYPTION_WALLET_LOCATION entry.
ENCRYPTION_WALLET_LOCATION=
(SOURCE=(METHOD=FILE)(METHOD_DATA=
(DIRECTORY=/u01/app/oracle/admin/TEST/encryption_wallet/)))
You must then create an encryption key, using an alter system key.
ALTER SYSTEM SET ENCRYPTION KEY AUTHENTICATED BY "str0ngphr4se!";
A note for release 11G R1 versus 11G R2. In 11G R1, you can only create an encryption key once. In 11G R2, you can reset the encryption key by running the above command again. Doing so will not invalidate already encrypted data. This feature is to provide support for environments where regulations require periodic changing of the encryption key. I have not tested what the performance or overhead penalty is for resetting the encryption key.
Whenever you open the database, you must open the wallet.
ALTER SYSTEM SET WALLET OPEN IDENTIFIED BY "str0ngphra4se!";
With the wallet in place, you can now create an encrypted tablespace. Note that you cannot ALTER an existing tablespace to become encrypted. One can move an existing table from a decrypted tablespace to an encrypted one, however. The following is sample code for creating an encrypted tablespace, then creating a table inside it using the CREATE TABLE AS SELECT method.
-- Make unencrypted tablespace
create tablespace foo
datafile '/u02/oradata/TEST/foo_1.dbf' size 100M ;
-- Make encrypted tablespace
create tablespace foo_enc
datafile '/u02/oradata/TEST/foo_enc_01.dbf' size 100M
encryption using 'AES256'
default storage (encrypt);
-- Put a table in the unencrypted tablespace. Query the all_objects view to make table.
16:55:06 SQL> create table decrypt_object tablespace foo as select * from all_objects;
Table created.
Elapsed: 00:00:08.08
-- Put a table in the encrypted tablespace. Query the all_objects view to make table.
16:54:50 SQL> create table enc_object tablespace foo_enc as select * from all_objects;
Table created.
Elapsed: 00:00:11.23
Finally, an illustration with unix commands show that the underlying datafiles for the tablespace are indeed encrypted. The first command shows the grep utility scanning for the string value “ALL_TABLES” in the unencrypted data files. It finds a match for a SYNONYM and a VIEW.
$ grep ALL_TABLES foo_1.dbf
ALL_TABLESÿÂQÿSYNONYMxm
ALL_TABLESÿÂPÿVIEWxm
$
But when the same command is run against the data file for the encrypted tablespace, no matches are found.
$ grep ALL_TABLES foo_enc_01.dbf
$
LOB columns could not be encrypted with Oracle 10G encryption. In 11G, you have 2 ways to encrypt the LOB columns. Tablespace encryption will work. I verified this by loading BLOB files into tables residing in the FOO and FOO_ENC tablespaces loaded above and than repeated the test of scanning the underlying data files in each for strings in the LOB files. You can also encrypt LOB columns with a new feature called SecureFiles. I’m not sure what is the performance impact of using SecureFiles vs Tablespace Encryption for encrypting LOBS.
SecureFiles is a new option to store BLOB and CLOB columns, and the old way is now called BasicFiles. SecureFiles offers performance enhancements over BasicFiles. Oracle recommends using SecureFiles for LOB storage over the older BasicFiles. Caching, locking, the write mechanism and logging are all enhanced in Securefiles. And if you pay for the extra cost Advanced Security Option, you can encrypt your SecureFile columns. Note that there is also another extra cost option called Advanced Compression, which enables you to use the compression and deduplication features of SecureFiles. These won’t be discussed here.
Before discussing the encryption feature for SecureFiles, I’ll provide some background information deploying SecureFiles in general.
A SecureFile LOB column must be created in a tablespace using Automatic Segment Storage Management (ASSM). To convert an existing pre-11G LOB column to a SecureFile column, you have the following options.
- CREATE TABLE AS SELECT. Use a CTAS statement to insert the data from the existing column into a SecureFile column in a new table.
- INSERT INTO using SELECT. Pre-create the target table with the SecureFile column and then run an INSERT INTO statement.
- Online table redefinition.
- Export/Import. You can use the expdp and impdp utilities to load the data. Note that the old exp and imp do not support encryption, so if you plan to import into an encrypted securefile, you will need expdp and impdp.
- Create a new column, update the new column with the values in the original column, then drop the old column.
The syntax for creating a SecureFile column looks like this:
create table company_docs
(doc_id, number not null primary key,
name varchar2(255) not null,
blob_content blob)
tablespace company_docs_data
lob (blob_content) store as securefile
(OPTIONAL DETAILED STORAGE CLAUSE GOES HERE);
For details on the detailed SecureFiles storage clause, see the oracle documentation.
If you would like to create the SecureFile column in encrypted format, you need to have the Oracle Wallet set up first, as described above. Once that is in place, the syntax for creating a SecureFile LOB column with encryption turned on is the following.
CREATE TABLE encrypt_a_lot
(id NUMBER, document BLOB)
LOB (document)
STORE AS SECUREFILE obscure_it
(ENCRYPT [ USING 'encrypt_algorithm' ] [ IDENTIFIED BY password ])
(OPTIONAL DETAILED STORAGE CLAUSE GOES HERE);
All records in the LOB column get encrypted, and that includes records across all partitions if the LOB column is spread over partitions. Note that in the above example, the ‘encrypt_algorithm’ indicates the name of the encryption algorithm. Valid algorithms are:
- 3DES168
- AES128
- AES192 (default)
- AES256
The last encryption feature to be covered here is support for encrypting the output of the Oracle’s export utility, expdp, alternately known as datapump. The old export utility exp does NOT support encryption.
The command line for datapump now has four parameters that govern the encryption feature.
- ENCRYPTION will encrypt part or all of a dump file. Valid keyword values are: ALL, DATA_ONLY, ENCRYPTED_COLUMNS_ONLY, METADATA_ONLY and NONE.
- ENCRYPTION_ALGORITHM specifies how encryption should be done. Valid keyword values are: AES128, AES192 and AES256. The default choice is AES128.
- ENCRYPTION_MODE indicates the method of generating the encryption key. Valid keyword values are: DUAL, PASSWORD and TRANSPARENT. The default choice is TRANSPARENT.
- ENCRYPTION_PASSWORD indicates the password key for creating encrypted data within a dump file. This is not a required argument unless you specify you want to use a password.
Here’s an illustration of using datapump to create encrypted output. First is a sample of running the utility with no encryption to create an output file called cleartext.dmp.
C:\oracle\admin\TESTDB\dpdump>expdp system/pwd directory=data_pump_dir dumpfile=cleartext.dmp
tables=dmg.demo_states
Here the command is run again with encryption set to all.
C:\oracle\admin\TESTDB\dpdump>expdp system/pwd directory=data_pump_dir dumpfile=crypto.dmp
tables=dmg.demo_states encryption = all
The next sample reveals that the unencrypted output is vulnerable to inspection with simple to use tools like findstr.
C:\oracle\admin\TESTDB\dpdump>findstr /C:"NEW JERSEY" cleartext.dmp
NEW JERSEY< ???NM
However the encrypted output file crypto.dmp cannot be parsed with text tools. No match is returned.
C:\oracle\admin\TESTDB\dpdump>findstr /C:"NEW JERSEY" crypto.dmp
C:\oracle\admin\TESTDB\dpdump>
In summary, Oracle 11G has significant new features to support encrypting data. You’ll want to plan carefully what you encrypt. There’s probably no reason to encrypt data unless it contains privacy oriented data, also known as personally identifiable data or PII. Such data can be used to uniquely identify an individual, such as a social security number. You’ll want to identify precisely which columns contain PII and then take measures to encrypt those columns or place the tables containing those columns in an encrypted tablespace. If your database does contain PII, you may also want to explore encrypting the data as it is transmitted using https and encrypted SQL*NET traffic, and investigate ensuring backups are encrypted by running RMAN encryption or using encrypted tape or disk devices. Oracle 11G enables you to encrypt your data at just about every level.
