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 BLOB
and CLOB
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.
See Also:
"Using Transparent Data Encryption" for detailed information about the SQL syntax used for transparent data encryption.
"Supported Encryption and Integrity Algorithms" for a list of the algorithms this feature supports.
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.
To create a new master key and begin using transparent data encryption, issue the following 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
To create a new table with encrypted columns, use the CREATE TABLE command in the following form:
CREATE TABLE table_name ( column_name column_type ENCRYPT,....);
The ENCRYPT
keyword against a column specifies that the column should be encrypted.
If an existing table has columns that require encryption, then use the ALTER TABLE
command in the following form:
ALTER TABLE table_name MODIFY ( column_name column_type ENCRYPT,...);
The ENCRYPT
keyword against a column specifies that the column should be encrypted.
To disable access to all encrypted columns in the database, use the following command:
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.
See Also:
Examples of ALTER SYSTEM
commands in Example 3-1 and Example 3-2.
Examples of ALTER TABLE
commands in Example 3-8, Example 3-9, and Example 3-10.
Examples using SQL for encryption include Example 3-5, Example 3-6, and Example 3-7.
Only a small number of steps are needed to configure and use this feature. This section contains these topics:
Opening the Encrypted Wallet for Database Access to Encryption Keys
Changing the Encryption Key or Algorithm on Tables Containing Encrypted Columns
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 walletThe 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
where 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.
Example 3-1 Opening the External Security Module Wallet with ALTER SYSTEM
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.
If you wish to use a wallet specifically for transparent data encryption, then you must specify a second wallet location in sqlnet.ora
by using the ENCRYPTION_WALLET_LOCATION
parameter.
See Also:
"Sample sqlnet.ora File"for an example of the syntax used to set this parameterThe 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.
To set the master key, use the following SQL syntax:
ALTER SYSTEM SET ENCRYPTION KEY certificate_ID IDENTIFIED BY password
where
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.
Example 3-2 Setting or Resetting the Master Key To Use a PKI-Based Private Key
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 SALT
or NO SALT
parameter specified with the ENCRYPT
clause. Example 3-3 and Example 3-4 illustrate such commands.
Example 3-3 Adding Salt to an Encrypted Column
ALTER TABLE employee MODIFY (first_name ENCRYPT SALT);
Example 3-4 Removing Salt from an Encrypted Column
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:
Creating a Table with an Encrypted Column That Uses the Default Algorithm
Creating a Table with an Encrypted Column Using a Non-Default Algorithm and No Salt
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.
Example 3-7 Creating a New External Table with a Password-Generated Column 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 ADD
or MODIFY
clause.
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.
Example 3-8 Adding Encrypted Columns to Existing Tables
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 saltIf 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.
Example 3-11 Creating Index on a Column 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.
Example 3-12 Changing the Encryption Key on Tables Containing Encrypted Columns
ALTER TABLE employee REKEY;
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 ENCRYPT
clause.
You can change encryption algorithms and encryption keys on existing encrypted columns by setting a different algorithm with the SQL ENCRYPT
clause.
See Also:
Example 3-6 for the correct syntax when choosing the NO SALT
parameter for the SQL ENCRYPT
clause
"Changing the Encryption Key or Algorithm on Tables Containing Encrypted Columns" for syntax examples when setting a different algorithm with the SQL ENCRYPT
clause
Table 3-1 lists the supported encryption algorithms.
Table 3-1 Supported Encryption Algorithms for Transparent Data Encryption
Algorithm | Key Size | Parameter Name |
---|---|---|
Triple DES (Data Encryption Standard) |
168 bits |
|
AES (Advanced Encryption Standard) |
128 bits |
|
AES |
192 bits (default) |
|
AES |
256 bits |
|
For integrity protection, the SHA-1
hashing algorithm is used.
The following datatypes can be encrypted using this feature:
CHAR
DATE
INTERVAL DAY TO SECOND
INTERVAL YEAR TO MONTH
NCHAR
NUMBER
NVARCHAR2
RAW
TIMESTAMP
(includes TIMESTAMP WITH TIME ZONE
and TIMESTAMP WITH LOCAL TIME ZONE
)
VARCHAR2
Table 3-2 provides a summary of the SQL commands you can use to implement and manage transparent data encryption.
Table 3-2 Transparent Data Encryption SQL Commands Quick Reference
Task | SQL Command |
---|---|
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:
Security Considerations for Using Transparent Data Encryption
Transparent Data Encryption and Replication in Distributed Environments
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 ManagerYou 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
password
command.
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:
Public
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:
DBA_ENCRYPTED_COLUMNS
ALL_ENCRYPTED_COLUMNS
Shows the algorithm used to encrypt columns for all tables that are accessible to a particular user.
USER_ENCRYPTED_COLUMNS
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.