Encrypting Data with Oracle Transparent Data Encryption

About Encrypting Sensitive Data

Encrypted data is data that has been disguised so that only an authorized recipient can read it. You use encryption to protect data in a potentially unprotected environment, such as data you have placed on backup media that is sent to an offsite storage location.

Encrypting data includes the following components:

• An algorithm to encrypt the data. Oracle Databases use the encryption algorithm to encrypt and decrypt data. Oracle Database supports several industry-standard encryption and hashing algorithms, including the Advanced Encryption Standard (AES) encryption algorithm, which has been approved by the National Institute of Standards and Technology (NIST).
• A key to encrypt and decrypt data. When you encrypt data, Oracle Database uses the key and plain text data as input into the encryption algorithm. Conversely, when you decrypt data, the key is used as input into the algorithm to reverse the process and retrieve the clear text data. Oracle Database uses a symmetric encryption key to perform this task, in which the same key is used to both encrypt and decrypt the data. The encryption key is stored in the data dictionary, but encrypted with another master key.

You can encrypt individual table columns or an entire tablespace. Be careful that you do not mix the two. For example, suppose you encrypt a table column and then encrypt its surrounding tablespace. This double encryption can cause performance problems. In addition, column encryption has limitations in data type support, and only supports B-tree indexes for equality searches. To check the current encrypted settings, you can query theV$ENCRYPTED_TABLESPACES data dictionary view for tablespaces and the DBA_ENCRYPTED_COLUMNS view for encrypted columns.

When Should You Encrypt Data?

In most cases, you must encrypt sensitive data on your site to meet a regulatory compliance. For example, sensitive data such as credit card numbers, Social Security numbers, or patient health information must be encrypted.

Historically, users have wanted to encrypt data to restrict data access from their database administrators. However, this problem is more of an access control problem, not an encryption problem. You can address this problem by using Oracle Database Vault to control the access to your application data from database administrators.

In most cases, you encrypt sensitive data, such as credit cards and Social Security numbers, to prevent access when backup tapes or disk drives are lost or stolen. In recent years, industry regulations such as the Payment Card Industry (PCI) Data Security Standard and the Healthcare Insurance Portability and Accountability Act (HIPAA) have become a driving factor behind increased usage of encryption for protecting credit card and health care information, respectively.

How Transparent Data Encryption Works

Transparent Data Encryption enables you to encrypt individual table columns or an entire tablespace. When a user inserts data into an encrypted column, Transparent Data Encryption automatically encrypts the data. When authorized users select the column, then the data is automatically decrypted.

To encrypt data by using Transparent Data Encryption, you create the following components:

• A keystore to store the master encryption key. The keystore is an operating system file that is located outside the database. The database uses the keystore to store the master encryption key. To create the keystore, you can use the ADMINISTER KEY MANAGEMENT SQL statement. The keystore is encrypted using a password as the encryption key. You create the password when you create the keystore. Access to the contents (or master key) of the keystore is then restricted to only those who know the password. After the keystore is created, you must open the keystore using the password so that the database can access the master encryption key.

You can use either software keystores or hardware keystores. A software keystore is defined in a file that you create in a directory location. The software keystore can be one of the following types:

• Password-based keystores: Password-based keystores are protected by using a password that you create. You must open the keystore before the keys can be retrieved or used.
• Auto-login keystores: Auto-login keystores are protected by a system-generated password, and do not need to be explicitly opened by a security administrator. Auto-login keystores are automatically opened when accessed. Auto-login keystores can be used across different systems. If your environment does not require the extra security provided by a keystore that must be explicitly opened for use, then you can use an auto-login keystore.
• Auto-login local keystores: Auto-login local keystores are auto-login keystores that are local to the system on which they are created. Auto-login local keystores cannot be opened on any computer other than the one on which they are created.

A hardware keystore is used with a hardware security module, which is a physical device that is designed to provide secure storage for encryption keys. This guide explains how to configure software keystores only. For detailed information about hardware keystores, see Oracle Database Advanced Security Guide.

• A location for the keystore. You must specify the keystore location in the sqlnet.ora file.

Afterward, when a user enters data, Oracle Database performs the following steps:

1. Retrieves the master key from the keystore.
2. Decrypts the encryption key using the master key.
3. Uses the encryption key to encrypt the data the user entered.
4. Stores the data in encrypted format in the database.

If the user is selecting data, the process is similar: Oracle Database decrypts the data and then displays it in plain text format.

Transparent Data Encryption has the following benefits:

• As a security administrator, you can be sure that sensitive data is safe if the storage media or data file is stolen or lost.
• Implementing Transparent Data Encryption helps you address security-related regulatory compliance issues.
• Data from tables is transparently decrypted for the database user. You do not need to create triggers or views to decrypt data.
• Database users do not need to be aware 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 and decryption is managed by the database.

Transparent Data Encryption has a minimal impact on performance. Transparent Data Encryption column encryption affects performance only when data is retrieved from or inserted into an encrypted column. There is no impact on performance for operations involving unencrypted columns, even if these columns are in a table containing encrypted columns. However, be aware that encrypted data must have more storage space than plain text data. On average, encrypting a single column requires between 32 and 48 bytes of additional storage for each row. Transparent tablespace encryption provides even better performance because Oracle Database performs the encryption and decryption at the I/O block layer. Once blocks are decrypted, they are cached in Oracle Database memory for optimal performance.

Configuring Data to Use Transparent Data Encryption

To start using Transparent Data Encryption, you must create a keystore and set a master key. The keystore should be a separate keystore specifically used by Transparent Data Encryption. This keystore will be used for all data that is being encrypted through Transparent Data Encryption. This procedure describes how to create a software password-based keystore.

You must follow these steps to configure Transparent Data Encryption:

ü  Step 1: Configure the Keystore Location

ü  Step 2: Check the COMPATIBLE Initialization Parameter Setting

ü  Step 3: Create the Software Password-Based Keystore

ü  Step 4: Open (or Close) the Keystore

ü  Step 5: Create the Master Encryption Key

ü  Step 6: Encrypt Data

Step 1: Configure the Keystore Location

You can designate the directory location for the keystore in the sqlnet.ora file. You perform this step once.

To configure the keystore location:  

1. Create a directory in the $ORACLE_HOME directory to store the keystore.

For example, on Microsoft Windows, you could create a directory called ORA_KEYSTORES in the C:\oracle\product\12.1.0\db_1 directory.

2. Create a backup copy of the sqlnet.ora file, which by default is located in the $ORACLE_HOME/network/admin directory.
3. At the end of the sqlnet.ora file, add code similar to the following, where ORA_KEYSTORES is the name of the directory where you plan to store the keystore:

4.       ENCRYPTION_WALLET_LOCATION=

5.        (SOURCE=

6.         (METHOD=file)

7.          (METHOD_DATA=

8.           (DIRECTORY=C:\oracle\product\12.1.0\db_1\ORA_KEYSTORES)))

9. Save and close the sqlnet.ora file.

Step 2: Check the COMPATIBLE Initialization Parameter Setting

If you want to configure the full set of tablespace encryption features, then you must set the COMPATIBLE initialization parameter for the database to11.2.0.0 or later. Otherwise, ensure that it is at least 11.0.0.0. Be aware that once you set this parameter, you cannot change it.

To set the COMPATIBLE initialization parameter: 

1. Log into the database instance.

For example:

sqlplus sec_admin

Enter password: password

Connected.

2. Check the current setting of the COMPATIBLE parameter.

For example:

SHOW PARAMETER COMPATIBLE

NAME                                 TYPE        VALUE

------------------------------------ ----------- -------------

compatible                           string      11.0.0.0

3. If you must change the COMPATIBLE parameter, then complete the remaining steps in this procedure.

The value should be 11.2.0.0 or higher.

4. Locate the initialization parameter file for the database instance.

·         UNIX systems: This file is in the ORACLE_HOME/dbs directory and is named initORACLE_SID.ora (for example, initmydb.ora).

·         Windows systems: This file is in the ORACLE_HOME\database directory and is named initORACLE_SID.ora (for example, initmydb.ora).

5. Edit the initialization parameter file to use the correct COMPATIBLE setting.

For example:

COMPATIBLE = 12.0.0.0

6. In SQL*Plus, log in as a user who has the SYSDBA administrative privilege.

7.       sqlplus sys as sysdba

8.       Enter password: password

9. Restart the Oracle Database instance.

For example:

SHUTDOWN IMMEDIATE

STARTUP

10. Do not log out of SQL*Plus.

Step 3: Create the Software Password-Based Keystore

To create the keystore, use the ADMINISTER KEY MANAGEMENT SQL statement. By default, the Oracle keystore stores a history of retired master keys, which enables you to change them and still be able to decrypt data that was encrypted under an old master key. A case-sensitive keystore password unknown to the database administrator provides separation of duty: a database administrator can restart the database, but the keystore is closed and must be manually opened by a security administrator before the database can encrypt or decrypt the data.

To create the keystore:  

1.       In SQL*Plus, connect as a user who has been granted the SYSKM administrative privilege.

For example:

CONNECT psmith / AS SYSKM

Enter password: password

2.       Run the following ADMINISTER KEY MANAGEMENT SQL statement:

3.       ADMINISTER KEY MANAGEMENT CREATE KEYSTORE 'keystore_location' IDENTIFIED BY software_keystore_password;

In this specification:

·         keystore_location is the path to the keystore location that you defined in the sqlnet.ora file (for example,oracle\product\12.1.0\db_1\ORA_KEYSTORES). Enclose the keystore_location setting in single quotation marks. To find this location, query the WRL_PARAMETER column of the V$ENCRYPTION_WALLET view.

·         software_keystore_password is a new password that you, the security administrator, creates.

For example, to create the keystore in the c:\oracle\product\12.1.0\db_1\ORA_KEYSTORES directory:

ADMINISTER KEY MANAGEMENT CREATE KEYSTORE 'c:\oracle\product\12.1.0\db_1\ORA_KEYSTORES' IDENTIFIED BY password;

keystore altered.

After you run this statement, the ewallet.p12 file, which contains the keystore, appears in the keystore location.

Step 4: Open (or Close) the Keystore

Immediately after you create a software password-based keystore, you must manually open it before you can use Transparent Data Encryption. (You do not need to open the auto-login or hardware keystores because they open automatically.) You can check the status of whether a keystore is open or closed by querying the STATUS column of the V$ENCRYPTION_WALLET view.

To open the keystore: 

1.       Ensure that you are logged into SQL*Plus as a user who has been granted the SYSKM system privilege.

2.       Enter the following ADMINISTER KEY MANAGEMENT SQL statement:

3.       ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY software_keystore_password;

4.        

5.       keystore altered.

Replace software_keystore_password with the password that you created in "Step 3: Create the Software Password-Based Keystore".

In most cases, leave the keystore open unless you have a reason for closing it. You can close a keystore to disable access to the master key and prevent access to the encrypted columns. The keystore must be open for Transparent Data Encryption to work. To reopen the keystore, use theADMINISTER KEY MANAGEMENT statement.

To close the keystore:  

1.       Ensure that you are logged into SQL*Plus as a user who has been granted the SYSKM system privilege.

2.       Enter the following SQL statement:

3.       ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY software_keystore_password;

Step 5: Create the Master Encryption Key

The master encryption key is stored in a keystore. It protects the table keys and tablespace encryption keys. By default, the master encryption key is a random key generated by Transparent Data Encryption (TDE).

To create the master encryption key: 

1.       Ensure that you are logged into SQL*Plus as a user who has been granted the SYSKM system privilege.

2.       Run the following ADMINISTER KEY MANAGEMENT SQL statement:

3.       ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY software_keystore_password [WITH BACKUP [USING 'backup_identifier']];

4.        

5.       keystore altered.

In this specification:

·         software_keystore_password is the password that you created in "Step 3: Create the Software Password-Based Keystore".

·         WITH BACKUP creates a backup of the keystore. You must use this option for password-based keystores. You do not need to use it for auto-login or auto-login local keystores. Optionally, you can use the USING clause to add a brief description of the backup. Enclose this description in double quotation marks. This identifier is appended to the named keystore file (for example, ewallet_timestamp_emp_key_backup.p12).

For example:

ADMINISTER KEY MANAGEMENT SET ENCRYPTION KEY IDENTIFIED BY software_keystore_password WITH BACKUP USING 'password key backup';

Step 6: Encrypt Data

Encrypting a Tablespace

You can encrypt a new tablespace while you are creating it, but you cannot encrypt an existing tablespace. As a workaround, you can use theCREATE TABLE AS SELECT, ALTER TABLE MOVE, or use Oracle Data Pump import to get data from an existing tablespace into an encrypted tablespace. For details about creating a tablespace.

To encrypt a tablespace:  

1.       Ensure that you have created and opened the keystore, as described in the preceding steps of this section.

2.       In Enterprise Manager, access the Database home page.

3.       From the Administration menu, select Storage, then Tablespaces.

If the Database Login page appears, then log in as an administrative user, such as SYS. User SYS must log in with the SYSDBA role selected.

The Tablespaces page appears.

4.       Click Create, and then answer the questions in the subsequent page to start creating the tablespace and its required data file.

5.       In the Create Tablespace page, do the following:

a.       Under Type, in the Permanent list, select the Encryption box.

b.       Under Datafiles, select Add to add a data file. (Linux and Windows systems only)

c.       Select Encryption options to display the Encryption Options page.

d.       From the Encryption Algorithm list, select from the following options:

·         AES192: Sets the key length to 192 bits. AES is the abbreviation for Advanced Encryption Standard.

·         3DES168: Sets the key length to 168 bits. 3DES is the abbreviation for Triple Data Encryption Standard.

·         AES128: Sets the key length to 128 bits. This option is the default.

·         AES256: Sets the key length to 256 bits.

e.       Click Continue.

6.       In the Create Tablespace page, click OK.

Encrypting Individual Table Columns

The decisions that you make when you identify columns to encrypt are determined by governmental security regulations, such as California Senate Bill 1386, or by industry standards such as the Payment Card Industry (PCI) Data Security Standard. Credit card numbers, Social Security numbers, and other personally identifiable information (PII) fall under this category. Your own internal security policies — trade secrets, research results, or employee salaries and bonuses — determine your needs for encryption. See "When Should You Encrypt Data?" for guidelines about when and when not to encrypt data.

Follow these guidelines when you select columns to encrypt:

·         Check the data types of the columns you plan to encrypt. Transparent Data Encryption supports the following data types:

	BINARY_FLOAT	NUMBER
	BINARY_DOUBLE	NVARCHAR2
	CHAR	RAW
	DATE	TIMESTAMP
	NCHAR	VARCHAR2
	Large object types (LOBs) such as BLOB and CLOBFoot 1

•  

·         ^Footnote 1 You cannot encrypt external LOBs (BFILE).

·         Ensure that the columns you select are not part of a foreign key. With Transparent Data Encryption, each table has its own encryption key, which is stored in the database data dictionary and encrypted with the external master key. Encrypted columns cannot be used as foreign keys.

To encrypt a column in a table:  

1.       Ensure that you have created and opened the keystore and created a master encryption key.

See the following sections, if necessary:

·         "Step 3: Create the Software Password-Based Keystore" to learn how to create a keystore key

·         "Step 4: Open (or Close) the Keystore" to learn how to open or a keystore

·         "Step 5: Create the Master Encryption Key" to create the master encryption key

2.       In Enterprise Manager, access the Database home page.

3.       From the Schema menu, select Database Objects, then Tables.

If the Database Login page appears, then log in as SYS with the SYSDBA administrative privilege.

4.       In the Tables page, do one of the following:

·         To create a new table, click Create, and then answer the questions in the subsequent page to start creating the table.

·         To modify an existing table, search for the table name by entering its schema name into the Schema field and the table name in the Object Namefield. (You can use the percent sign (%) wildcard character to search for a group of tables, for example O% to find all tables beginning with the letter O.) When the table is listed in the Tables page, select the table, and then click Edit.

In the Create Table or Edit Table page, you can set the encryption options.

 For example, to encrypt columns in the OE.ORDERS table, the Edit Table page appears as follows:

1. In the Create Table (or Edit Table) page, do the following:

a.       Select the column that you want to encrypt.

Do not select columns that are part of a foreign key constraint (primary or unique key columns). You cannot encrypt these columns. These columns are indicated with a key or check mark icon to the left of their names.

b.      Click Encryption Options to display the Encryption Options for the Table page.

c.       From the Encryption Algorithm list, select from the following options:

·         AES192: Sets the key length to 192 bits. AES is the abbreviation for Advanced Encryption Standard.

·         3DES168: Sets the key length to 168 bits. 3DES is the abbreviation for Triple Data Encryption Standard.

·         AES128: Sets the key length to 128 bits. This option is the default.

·         AES256: Sets the key length to 256 bits.

d.      Under Key Generation, select either Generate Key Randomly or Specify Key. If you select Specify Key, enter characters for the seed values in the Enter Key and Confirm Key fields.

The Generate Key Randomly setting enables salt. 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 the same text to appear different when encrypted. Salt removes one method attackers use to steal data, namely, matching patterns of encrypted text.

e.       Click Continue to return to the Create Table (or Edit Table) page.

f.       Enable encryption for the column by selecting its box under Encrypted.

2. Click Apply, and then click Return.

The Tables page appears.

While a table is being updated, read access is still possible. Afterward, existing and future data in the column is encrypted when it is written to the database file, and it is decrypted when an authorized user selects it. If data manipulation language (DML) statements are needed, you can use online redefinition statements.