This chapter describes how to secure sensitive data within an Oracle database by using transparent data encryption, the feature that enables you to encrypt database columns and manage encryption keys. This chapter contains the following topics:
Oracle Database 10g uses authentication, authorization, and auditing mechanisms to secure data in the database, but not in the operating system files where the data is stored. To protect those files, Oracle Database 10g provides transparent data encryption. This feature enables you to protect sensitive data in database columns stored in operating system files by encrypting it. Then, to prevent unauthorized decryption, it stores encryption keys in a security module external to the database.
This section contains the following topics:
Transparent data encryption enables simple and easy encryption for sensitive data in columns without requiring users or applications to manage the encryption key. This freedom can be extremely important when addressing, for example, regulatory compliance issues. No need to use views to decrypt data, because the data is transparently decrypted once a user has passed necessary access control checks. Security administrators have the assurance that the data on disk is encrypted, yet handling encrypted data becomes transparent to applications.
Use transparent data encryption to protect confidential data such as credit card and social security numbers without having to manage key storage or create auxiliary tables, views, and triggers. An application that processes sensitive data can use this feature to provide strong data encryption with little or no change to the application.
Do not, however, use transparent data encryption with these database features:
Index types other than B-tree
Range scan search through an index
Large object datatypes such as
Original import/export utilities
Other database tools and utilities that directly access data files
Applications that need to use these unsupported features can use the
DBMS_CRYPTO package for their encryption needs.
Note:Enabling encryption on an existing table results in a full table update like any other ALTER TABLE operation that modifies table characteristics. Administrators should keep in mind the potential performance and redo log impact on the database server before enabling encryption on a large existing table.
Transparent data encryption is a key-based access control system. Even if the encrypted data is retrieved, it cannot be understood until authorized decryption occurs, which is automatic for users authorized to access the table.
When a table contains encrypted columns, a single key is used regardless of the number of encrypted columns. The keys for all tables containing encrypted columns are encrypted with the database server master key and stored in a dictionary table in the database. No keys are stored in the clear.
As shown in Figure 3-1, the master key of the server is stored in an external security module that is outside the database and accessible only to the security administrator. For this external security module, Oracle uses an Oracle wallet as described in this chapter. Storing the master key in this way prevents its unauthorized use. In addition to storing the master key, the Oracle wallet is also used to generate encryption keys and perform encryption and decryption.
Using an external security module separates ordinary program functions from encryption operations, making it possible to divide duties between database administrators and security administrators. Security is enhanced because no single administrator is granted complete access to all data.
To use transparent data encryption, you must have the
ALTER SYSTEM privilege and a valid password to the Oracle wallet. If an Oracle wallet does not exist, then a new one is created using the password specified in the SQL command.
ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY password
This command generates the database server master encryption key, which the server uses to encrypt the column encryption key for each table. No table columns in the database can be encrypted until the master key of the server has been set.
A master key that has been set remains accessible to the database until the database instance is shutdown. To load the master key after the database is restarted, use the following command:
ALTER SYSTEM SET WALLET OPEN IDENTIFIED BY password
CREATE TABLE table_name ( column_name column_type ENCRYPT,....);
ENCRYPT keyword against a column specifies that the column should be encrypted.
ALTER TABLE table_name MODIFY ( column_name column_type ENCRYPT,...);
ENCRYPT keyword against a column specifies that the column should be encrypted.
ALTER SYSTEM SET WALLET CLOSE
The command disables access to the master key in the wallet and prevents access to data in the encrypted columns.
Only a small number of steps are needed to configure and use this feature. This section contains these topics:
Before transparent data encryption can be enabled, the compatibility level for the database must be 10.2. To start using transparent data encryption, the security administrator must create a wallet and set a master key.
The wallet can be the default database wallet shared with other Oracle Database components or a separate wallet specifically used by transparent data encryption. Oracle recommends that, for greater security, a separate wallet be used to store transparent data encryption master keys.
See Also:"Oracle Wallet Management for Transparent Data Encryption" for more information on configuring transparent data encryption to use a separate wallet
The external security module stores the encryption keys in an Oracle wallet. The database must load the master and column encryption keys into memory from the wallet before it can encrypt or decrypt columns. Use the following
ALTER SYSTEM command to explicitly open the wallet:
ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY password
password is a string value specified in
sqlnet.ora as the password to the wallet.
Once the wallet has been opened, it remains open until you shut down the database instance (or close it explicitly by issuing an
ALTER SYSTEM SET ENCRYPTION WALLET CLOSE command). When you restart the instance, you must issue the
ALTER SYSTEM SET ENCRYPTION WALLET OPEN command again.
If the schema does not have the
ALTER SYSTEM privilege, or the wallet is unavailable, or an incorrect password is given, then the command returns an error and exits. If the wallet is already open, the command returns an error and takes no action. Example 3-1 shows an example of each usage case.
ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "U83j10LLt8v"; Wallet opened. ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "U83j10LLt8v"; ORA-XXXXX: Wallet already opened. ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "U93j10LLt8v"; ORA-XXXXX: Incorrect wallet password given.
The external security module can use wallets with the automatic login feature enabled. These wallets remain open all the time. The security administrator does not have to reopen the wallet after a database instance has been restarted. If your environment does not require the extra security provided by a wallet that must be explicitly opened for use, then you may use an autologin wallet.
See Also:"Using Auto Login" for information about autologin wallets and how to use them.
By default, the external security module stores encryption keys in the Oracle wallet specified in the
sqlnet.ora configuration file. If no wallet location is specified in the
sqlnet.ora file, then the default database wallet is used.
See Also:"Sample sqlnet.ora File"for an example of the syntax used to set this parameter
The master key is stored in the external security module and is used to protect column encryption keys. By default, the master key is a random key generated by transparent data encryption. It can also be an existing key pair from a PKI certificate designated for encryption. To use transparent data encryption with PKI key pairs, the issuing certificate authority must be able to issue X.509v3 certificates with the key usage field marked for encryption.
Neither key type is more secure, but if you have already deployed PKI within your organization, then you can leverage such PKI services as key escrow and recovery. However, encryption using current PKI algorithms requires significantly more system resources than symmetric key encryption. Using a PKI key pair as a master key may result in greater performance degradation when accessing encrypted columns in the database.
To set or reset the master key, you use the same
ALTER SYSTEM command. The following sections explain how and why to perform each of these tasks.
Before encrypting any database columns, you must generate or set a master key. This master key is used to encrypt the column encryption key that is generated automatically when you issue a SQL command with the
ENCRYPT clause on a database column.
ALTER SYSTEM SET ENCRYPTION KEY certificate_ID IDENTIFIED BY password
certificate_ID is an optional string containing the unique identifier of a certificate stored in the security module. Use this parameter if you intend to use your PKI private key as your master key. This parameter has no default setting.
You can search for a
certificate_ID by querying the
V$WALLET fixed view when the wallet is open. Only certificates that can be used as master keys by transparent data encryption are shown.
password is the mandatory wallet password for the security module, with no default setting. It is case sensitive; enclose it in double-quotation marks.
See Also:Oracle Database SQL Reference for the SQL rules for supplying passwords.
If no wallet exists, then this command creates a new one at the wallet location specified in the
sqlnet.ora parameter file. If no wallet location is specified in the
sqlnet.ora file, then the default database wallet location is used. If an existing autologin wallet is present at the expected wallet location, then a new wallet is not created.
Regenerate the master key only if it has been compromised. Frequent master key regeneration does not necessarily enhance system security. Security modules can store a large, but not infinite, number of keys, and frequent master key regeneration can exhaust all the available storage space.
To reset the master key, use the SQL syntax as shown in "Setting the Master Key for First Time Use of Transparent Data Encryption".
Note that the
ALTER SYSTEM SET ENCRYPTION KEY command is a DDL command requiring the
ALTER SYSTEM privilege, and it automatically commits any pending transactions. Example 3-2 shows a sample usage of this command.
ALTER SYSTEM SET ENCRYPTION KEY "j23lm781098dhb345sm" IDENTIFIED BY "p3812dH9E";
Note that for PKI-based keys, certificate revocation lists are not enforced because enforcing certificate revocation may lead to losing access to all encrypted information in the database.
Salt is a way to strengthen the security of encrypted data. It is a random string added to the data before it is encrypted, causing repetition of text in the clear to appear different when encrypted. Salt thus removes one method attackers use to steal data, namely, matching patterns of encrypted text.
To add or remove salt from encrypted columns, you again use the
ALTER TABLE MODIFY command with either the
NO SALT parameter specified with the
ENCRYPT clause. Example 3-3 and Example 3-4 illustrate such commands.
ALTER TABLE employee MODIFY (first_name ENCRYPT SALT);
ALTER TABLE employee MODIFY (first_name ENCRYPT NO SALT);
To remove salt from an encrypted column before indexing it, use the syntax shown in Example 3-4. This command also re-encrypts the data.
To create relational tables with encrypted columns, specify the SQL
ENCRYPT clause when you define database columns with the
CREATE TABLE statement.
This section contains the following topics:
By default, transparent data encryption uses AES with a 192-bit length key (
AES192). If you use the
ENCRYPT clause with no other modifiers, the column you specify will be encrypted by using AES192 as shown in Example 3-5.
By default, transparent data encryption adds salt to cleartext before encrypting it. This makes it harder for attackers to steal the data through a brute force attack.
However, if you plan to index the encrypted column, you must use
NO SALT. Example 3-6 shows how to specify the
NO SALT parameter with the SQL
ENCRYPT clause (
empID NUMBER ENCRYPT NO SALT). It also shows the syntax for specifying a different encryption algorithm (
salary NUMBER(6) ENCRYPT USING '3DES168'). Note that the string which specifies the algorithm must be enclosed in single quotation marks.
The external table database feature enables you to access data in external sources as if it were in a table in the database. External tables can be updated using the
ORACLE_DATAPUMP access driver. Otherwise, they are read-only.
See Also:Oracle Database Concepts for discussions of Schema Objects and Tables.
To encrypt specific columns in an external table, you specify the
ENCRYPT clause when you define those columns. That specification causes a randomly generated key to be used to encrypt the columns.
However, if you intend to move your external table, that key will not be available in the new location. For such a table, you should specify your own password to encrypt the columns. Then, after you move the data, use the same password to regenerate the key so you can access encrypted column data in the new location.
Table partition exchange also requires a password-based column encryption key.
CREATE TABLE emp_ext ( first_name, last_name, empID, salary, ssn ENCRYPT IDENTIFIED BY "xIcf3T9u" ) ORGANIZATION EXTERNAL ( TYPE ORACLE_DATAPUMP DEFAULT DIRECTORY "D_DIR" LOCATION('emp_ext.dat') ) REJECT LIMIT UNLIMITED as select * from employee;
See Also:Oracle Database SQL Reference about CREATE TABLE, ENCRYPT, and the rules for passwords.
To add an encrypted column to an existing table, or to encrypt or decrypt an existing column, you use the
ALTER TABLE SQL command with the
This section contains the following topics:
To add an encrypted column to an existing table, you use the
ALTER TABLE ADD command, specifying the new column with the
ENCRYPT clause as shown in Example 3-8.
ALTER TABLE employee ADD (ssn VARCHAR2(11) ENCRYPT);
This command encrypts the new column with the default AES encryption algorithm, using a 192-bit key length (AES192) and adding salt to the cleartext data before encryption. However, if you plan to index this column use the
ENCRYPT clause with the
NO SALT parameter. For an example of this syntax, see "Adding or Removing Salt from an Encrypted Column".
To encrypt unencrypted columns, use the
ALTER TABLE MODIFY command, specifying the unencrypted column with the
ENCRYPT clause as shown in Example 3-9.
This command encrypts the column using the default AES192 algorithm, because no other encryption algorithm was specified. Salt is added by default because the
NO SALT parameter was not specified with
ENCRYPT. In this respect, this example is similar to Example 3-8.
See Also:"Adding or Removing Salt from an Encrypted Column"which shows how to specify a different encryption algorithm and no salt
If a column must be indexed, you must specify that no salt be added.
It may be necessary to disable encryption for reasons of compatibility or performance. To disable column encryption, use the
ALTER TABLE MODIFY command with the
DECRYPT clause as shown in Example 3-10.
To create an index on an encrypted column, you use the standard
CREATE INDEX command. The column being indexed must have been encrypted without salt. Example 3-11 shows how to create an index on a column that has been encrypted without salt.
CREATE TABLE employee ( first_name VARCHAR2(128), last_name VARCHAR2(128), empID NUMBER ENCRYPT NO SALT, salary NUMBER(6) ENCRYPT USING '3DES168' ); CREATE INDEX employee_idx on employee (empID);
Note:You cannot create an index on a column that has been encrypted with salt. If you try to do this, an error (ORA-28338) is raised.
Each table can have at most one encryption key for its columns. This key can be changed by using either the original encryption algorithm or a different algorithm specified in the
REKEY command phrase. The examples in this section illustrate these capabilities. Good security practices include backing up the wallet before and after such changes.
ALTER TABLE employee REKEY;
ALTER TABLE employee REKEY USING '3DES168';
See Also:Oracle Database SQL Reference about
By default, transparent data encryption uses the Advanced Encryption Standard with a 192-bit length cipher key (AES192). In addition, salt is added by default to cleartext before encryption unless specified otherwise. Note that salt cannot be added to indexed columns that you want to encrypt. For indexed columns, choose the
NO SALT parameter for the SQL
You can change encryption algorithms and encryption keys on existing encrypted columns by setting a different algorithm with the SQL
Example 3-6 for the correct syntax when choosing the
NO SALT parameter for the SQL
"Changing the Encryption Key or Algorithm on Tables Containing Encrypted Columns" for syntax examples when setting a different algorithm with the SQL
Table 3-1 lists the supported encryption algorithms.
|Algorithm||Key Size||Parameter Name|
Triple DES (Data Encryption Standard)
AES (Advanced Encryption Standard)
192 bits (default)
For integrity protection, the
SHA-1 hashing algorithm is used.
The following datatypes can be encrypted using this feature:
INTERVAL DAY TO SECOND
INTERVAL YEAR TO MONTH
TIMESTAMP WITH TIME ZONE and
TIMESTAMP WITH LOCAL TIME ZONE)
Table 3-2 provides a summary of the SQL commands you can use to implement and manage transparent data encryption.
Add encrypted column to existing table
Create table and encrypt column
Encrypt unencrypted existing column
Master key: set or reset
Master key: set or reset to use PKI certificate
Wallet: open to access master keys
This section contains these topics:
To store the master keys, transparent data encryption can use either the default database wallet shared by all Oracle components or a separate wallet. The wallet can be an autologin wallet that allows access to encrypted data without requiring a security administrator to explicitly open the wallet.
To create wallets used by transparent data encryption, you use the
ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY
password command. If no wallet exists in the default or specified locations, transparent data encryption creates a wallet when setting the master key for the first time. A wallet is not be created if the
WALLET_LOCATION parameter in the
sqlnet.ora file does not specify a valid path. The password specified in the SQL command for setting the master key becomes the password to open the wallet.
In addition to the SQL command, you can also use the
mkwallet command-line utility and Oracle Wallet Manager to create wallets. These are full-featured tools that allow you to create wallets and to view and modify their content.
See Also:Chapter 9, "Using Oracle Wallet Manager" for more information about Oracle Wallet Manager
You can create an autologin wallet with the
mkwallet utility or Oracle Wallet Manager. The autologin wallet allows convenient access to encrypted data across database instance restarts.
Transparent data encryption uses an autologin wallet only if it is available at the correct location and the SQL command to open an encrypted wallet has not already been executed. If an autologin wallet is being used, you must not use the
ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY
By default, transparent database encryption uses the default database wallet or the wallet specified by the
WALLET_LOCATION parameter in the
sqlnet.ora configuration file. Because this wallet can be shared by other Oracle components, Oracle recommends that a separate wallet be used exclusively for storing master keys used by transparent data encryption. To designate a separate wallet, set the
ENCRYPTION_WALLET_LOCATION parameter in the
sqlnet.ora file to point to the wallet used exclusively by transparent data encryption.
When determining which wallet to use, transparent data encryption first attempts to use the wallet specified by the parameter
ENCRYPTION_WALLET_LOCATION. If the parameter is not set, or no wallet is found at the specified location, then it attempts to use the wallet specified by the parameter
WALLET_LOCATION. If this fails as well, then transparent data encryption looks for a wallet at the default database location.
If there are multiple Oracle databases installed on the same server, then each database must access its own Transparent Data Encryption wallet. Sharing the same wallet between databases is not supported because it can potentially cause the loss of encrypted data.
This section contains the following topics:
Because the master keys are required to access any encrypted data, they must be properly backed up. This means that, because master keys reside in an Oracle wallet, the wallet should be periodically backed up in a secure location along with the database data files. You must back up a copy of the wallet whenever a new master key is set.
If you loose the wallet that stores the master key, you can restore access to encrypted data by copying the backed-up version of the wallet to the appropriate location. If the restored wallet was archived after the last time the master key was reset, then no additional action needs to be taken.
If the restored wallet does not contain the most recent master key, then you can recover old data up to the point when the master key was reset by rolling back the state of the database to that point in time. All modifications to encrypted columns after the master key was reset are lost.
Transparent data encryption supports the use of PKI asymmetric key pairs as master keys. This enables it to leverage existing key backup, escrow, and recovery facilities from leading certificate authority vendors.
In current key escrow or recovery systems, the certificate authority with key recovery capabilities typically stores a version of the private key or a piece of information that helps recover the private key. If the private key is lost, the user can recover the original key and certificate by contacting the certificate authority and initiating a key recovery process.
Typically, the key recovery process is automated and requires the user to present certain authenticating credentials to the certificate authority. Transparent data encryption puts no restrictions on the key recovery process other than that the recovered key and its associated certificate be a PKCS#12 file that can be imported into an Oracle wallet. This requirement is consistent with the key recovery mechanisms of leading certificate authorities.
After obtaining the PKCS#12 file with the original certificate and private key, you need to create a new empty wallet in the same location as the previous wallet. To do this, you can use the
mkwallet command line utility or Oracle Wallet Manager. You can then import the PKCS#12 file into the wallet by using the same utility. You should choose a strong password to protect the wallet.
After the wallet has been created and the correct certificates imported, log onto the database and execute the following command at the SQL prompt to complete the recovery process:
ALTER SYSTEM SET ENCRYPTION KEY certificate_id IDENTIFIED BY wallet_password
To retrieve the
certificate_id of the certificate in the wallet, query the
V$WALLET fixed view after the wallet has been opened.
When exporting tables containing encrypted columns, it is important that:
The sensitive data remain unintelligible during transport
Authorized users can decrypt that data after it is imported at the destination
Because the key for decryption is local to the server where the tables originally reside, decryption at the destination will not be possible using that key. Consequently, prior to exporting, the administrator re-keys the table(s) with a password key, which he then securely provides to the destination administrator.
Upon import, the destination administrator specifies the same password. The affected columns being imported are decrypted, enabling the receiving server to immediately re-encrypt those columns with a local server key. They are then ready for standard authorized use in their new location.
This feature affects performance only when data is retrieved from or inserted into an encrypted column. No reduction of performance occurs for such operations on other columns, even in a table containing encrypted columns.
The total performance effect depends on the number of encrypted columns and their frequency of access. The columns most appropriate for encryption are those containing the most sensitive data, including regulatory mandates.
Enabling transparent data encryption on columns in an existing table results in a full table update on all rows in the column as it encrypts all data stored within the column. This may cause the table to be inaccessible while encryption is being enabled. Using Online Redefinition to enable transparent data encryption allows the table to be available while it is being encrypted.
The redo log impact of a full table update on a large table should also be kept in mind. If transparent data encryption is being enabled on a very large table, then the redo log size might need to be increased to accommodate the operation.
Access to encrypted columns can be:
Controlled by standard authentication and authorization procedures and policies
Controlled by individual users who can grant access to others
Security considerations for transparent data encryption operate within the broader arena of total system security. Security administrators must identify the levels of risk to be addressed and the degrees of sensitivity in data maintained by the site. Costs and benefits must be evaluated for the alternative methods of achieving acceptable protections. In many cases, it makes sense to have separate security administrators, separate wallets, and protected backup procedures for encrypted materials. Having separate wallets permits auto-login for other Oracle components but preserves password protection for the wallet enabling transparent data encryption.
Additional security considerations apply to normal database and network operations when transparent data encryption is in use. Encrypted column data stays encrypted in the data files, undo logs, and redo logs, as well as in the buffer cache of the system global area (SGA). However, during expression evaluation it is decrypted, making it possible for decrypted data to appear in the swap file on the disk, potentially visible to privileged operating system users.
In general, the methods and protections vary with the choice of the tool by which replication is accomplished. SQL*Loader, Dataguard, and import/export are methods to enable transporting tables with encrypted columns. They rely on using a password created precisely to protect the data during transmission. When the data is reconstituted at the receipt-point, the columns marked as encrypted are immediately re-keyed with a master key local to the new table location.
See Also:Manuals for SQL*Loader, Dataguard, and import/export.
When asynchronous SQL-based replication is used, information describing the update to be performed is encoded (but not encrypted) in a LOB and can remain in the deferred queue for some time. While in the deferred queue, the data is not protected. To minimize this risk, configure replication so that encrypted table information does not remain in the deferred queue for a long time.
"Row-shipping" cannot be used, because the key to make the row usable is not available at the receipt-point.
The following three data dictionary views maintain information about the encryption algorithms used to encrypt columns:
Shows the algorithm used to encrypt columns for all tables that are accessible to a particular user.
Shows the algorithm used to encrypt columns for all tables in a particular user's schema.
See Also:Oracle Database Reference for a full description of these data dictionary views.