Oracle SecureFiles, introduced in Oracle 11g, offer several advantages over traditional large objects (which have been renamed to BasicFile LOBs). Oracle SecureFile LOBs support intelligent data compression, a deduplication option that can eliminate storing more than one copy of the same data, and have several features to improve performance when using large objects. One of the more useful new features is that Oracle's Transparent Data Encryption capability has been extended to include Oracle SecureFiles.
When Transparent Data Encryption (TDE) is enabled for one or more LOB columns, Oracle will automatically encrypt and decrypt the columns at the block level in data files, backup files, and redo log files. LOBs can only be encrypted on a per-column basis and when enabled, all LOBs in that column (across all partitions if applicable) will be encrypted. The actions performed by Transparent Data Encryption are completely hidden from applications and users accessing the table. However, because the traditional import and export facilities and transportable-tablespace-based exports do not support TDE, it is necessary to use the Data Pump import and export utilities when moving data that contains encrypted columns.
The following encryption algorithms are supported by TDE:
- 3DES16 -- Triple Data Encryption Standard with a 168-bit key size.
- AES128 -- Advanced Encryption Standard with a 128-bit key size.
- AES192 -- Advanced Encryption Standard with a 192-bit key size. This is the default.
- AES256 -- Advanced Encryption Standard with a 256-bit key size.
Before it is possible to use Transparent Data Encryption, the security administrator must create a wallet and set a master key. It is possible to use the default database wallet shared by other Oracle Database components. Alternately, you can create a separate wallet specifically for TDE. Oracle strongly recommends using a separate wallet to store the master encryption key. If the ENCRYPTION_WALLET_LOCATION parameter is not present in the sqlnet.ora file, then the WALLET_LOCATION value will be used. If WALLET_LOCATION is not specified in the sqlnet.ora file, then the default database wallet location is used. The default database wallet location is ORACLE_BASE/admin/DB_UNIQUE_NAME/wallet.
To use a wallet specifically for TDE, a wallet location must be specified in the sqlnet.ora file by using the ENCRYPTION_WALLET_LOCATION parameter. The master key is used to protect the table keys and tablespace encryption keys and is stored in an external security module. By default, it is a random key generated by TDE. It can also be an existing key pair from a PKI certificate designated for encryption.
To set the master encryption key, use the following command:
ALTER SYSTEM SET ENCRYPTION KEY ["certificate_ID"] IDENTIFIED BY "password";
- certificate_ID -- This is an optional string containing the unique identifier of a certificate stored in the Oracle wallet. Use this parameter if you intend to use your PKI private key as your master encryption key.
- password -- This is the mandatory wallet password for the security module. It is case sensitive.
The database must load the master encryption key into memory before it can encrypt or decrypt columns/tablespaces. The following ALTER SYSTEM command explicitly opens the wallet:
ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "password"
The "password" value is the one used when the encryption key was created. The password string must be enclosed in double quotation marks. Once the wallet has been opened, it remains open until the database instance is shut down or is closed explicitly. The following command will explicitly close the wallet:
ALTER SYSTEM SET ENCRYPTION WALLET CLOSE IDENTIFIED BY "password"
Closing the wallet disables all encryption and decryption operations. Each time you restart a database instance, the wallet must be opened to re-enable encryption and decryption operations. Once a wallet has been created and opened, it is possible to use the CREATE TABLE and ALTER TABLE commands with the ENCRYPT clause.
The Transparent Data Encryption syntax used to encrypt non-LOB columns is identical to that used for enabling encryption on Oracle SecureFiles. One minor difference is that with non-LOB columns, it is possible to use the NO SALT parameter to prevent TDE from adding a random string to the data before encrypting it. With Oracle SecureFile LOB columns, the NO SALT option is not supported. The data dictionary view USER_ENCRYPTED_COLUMNS can be used to determine which columns are encrypted and their status. Some examples of the ENCRYPT clause follow:
Create an Oracle SecureFiles column with a specific encryption algorithm:
CREATE TABLE tab_3DES (col1 CLOB ENCRYPT USING '3DES168')
LOB(col1) STORE AS SECUREFILE(
Create an Oracle SecureFile LOB column with the default encryption algorithm (AES192) and a password key:
CREATE TABLE tab_enc_pw (col1 CLOB ENCRYPT IDENTIFIED BY badpassword)
LOB(col1) STORE AS SECUREFILE(
Enable LOB encryption using AES256:
ALTER TABLE tab_nocrypt MODIFY
( col1 CLOB ENCRYPT USING 'AES256');
Alter an Oracle SecureFiles column by re-keying the encryption:
ALTER TABLE tab_3DES REKEY USING 'AES256';
Disable LOB encryption:
ALTER TABLE tab_enc_pw MODIFY
( col1 CLOB DECRYPT);
The volume of large object data being stored in Oracle databases continues to increase as the cost of storage decreases. Much of that data is sensitive, such as medical records, employee data and intellectual property. Having the ability to encrypt that information is invaluable and reduces the possibility of an embarrassing and expensive breach of security.
About the author:
Matthew Morris is a database engineer for Computer Sciences Corporation in Orlando, Fla. For over 17 years he has worked with the Oracle Database as a support engineer, database administrator, developer and architect.
This was first published in October 2013