A place to store sensitive information

Securing Stored Data Using Transparent Data Encryption

Transparent data encryption enables you to encrypt sensitive data, such as credit card numbers, stored in table columns. Encrypted data is transparently decrypted for a database user who has access to the data. Transparent data encryption helps protect data stored on media in the event that the storage media or data file gets stolen.

About Transparent Data Encryption

Oracle Database 11g uses authentication, authorization, and auditing mechanisms to secure data in the database, but not in the operating system data files where data is stored. To protect these data files, Oracle Database provides transparent data encryption. Transparent data encryption encrypts sensitive table data stored in data files. To prevent unauthorized decryption, transparent data encryption stores the encryption keys in a security module external to the database.

This section contains the following topics:

Benefits of Using Transparent Data Encryption
When to Use Transparent Data Encryption
How Transparent Data Encryption Works
Overview of Basic Transparent Data Encryption Operations

Benefits of Using Transparent Data Encryption

Transparent data encryption has the following advantages:

As a security administrator, you can be sure that sensitive data is safe in case the storage media or data file gets stolen.

Implementing transparent data encryption helps you address security-related regulatory compliance issues.

You do not need to create triggers or views to decrypt data. Data from tables is transparently decrypted for the database user.

Database users need not be aware of the fact that the data they are accessing is stored in encrypted form. Data is transparently decrypted for the database users and does not require any action on their part.

Applications need not be modified to handle encrypted data. Data encryption/decryption is managed by the database.

When to Use Transparent Data Encryption

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.

Restrictions on Using Transparent Data Encryption

You cannot use transparent data encryption to encrypt columns used in foreign key constraints. This is because every table has a unique column encryption key.

Transparent data encryption encrypts and decrypts data at the SQL layer. Oracle Database utilities and features that bypass the SQL layer cannot leverage the services provided by transparent data encryption. Do not use transparent data encryption with the following database features:

• Index types other than B-tree
• Range scan search through an index
• External large objects (BFILE)
• Materialized View Logs
• Synchronous Change Data Capture
• Transportable Tablespaces
• Original import/export utilities

Transparent data encryption protects data stored on disk/media. It does not protect data in transit. Use Oracle Advanced Security network encryption solutions discussed in Chapter 2, "Configuration and Administration Tools Overview"to encrypt data over the network.

How Transparent Data Encryption Works

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. This key is called the column encryption key. The column encryption keys for all tables, containing encrypted columns, are encrypted with the database server master encryption key and stored in a dictionary table in the database. No keys are stored in the clear.

The master encryption key 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 encryption key in this way prevents its unauthorized use.

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 the wallet password can be unknown to the database administrator, requiring the security administrator to provide the password.

Overview of Basic Transparent Data Encryption Operations

To enable 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

Enclose the password in double quotation marks (" "). 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.

The master encryption key remains accessible to the database until the database instance is shutdown. To load the master encryption key after the database is restarted, use the following command:

ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY password

Enclose the password in double quotation marks (" "). 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 ENCRYPTION WALLET CLOSE

The preceding command disables access to the master key in the wallet and prevents access to data in the encrypted columns. You need to open the wallet again, using the 

ALTER SYSTEM SET WALLET OPEN IDENTIFIED BY password 

command, to re-enable access to the master encryption key.