1. 2 Day + Security Guide
  2. Encrypting Data with Oracle Transparent Data Encryption

4 Encrypting Data with Oracle Transparent Data Encryption

Transparent Data Encryption enables you to disguise data in table columns and in an entire tablespace.

Topics:

4.1 About Encrypting Sensitive Data

Encrypted data is data that has been disguised so that only an authorized recipient can read it.

You use encryption (Transparent Data Encryption, or TDE) 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 the V$ENCRYPTED_TABLESPACES data dictionary view for tablespaces and the DBA_ENCRYPTED_COLUMNS view for encrypted columns.

See Also:

Oracle Database Advanced Security Guide for detailed information about TDE

4.2 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.

4.3 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.

See Also:

Oracle Database Advanced Security Guide for detailed information about using Transparent Data Encryption

4.4 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.

Topics:

4.4.1 Step 1: Configure the Keystore Location

When you create a software password-based keystore, you must designate the directory location for the keystore in the sqlnet.ora file.

You perform this step only 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.2.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: 

    ENCRYPTION_WALLET_LOCATION=
 (SOURCE=
  (METHOD=file)
   (METHOD_DATA=
    (DIRECTORY=C:\oracle\product\12.2.0\db_1\ORA_KEYSTORES)))

  4. Save and close the sqlnet.ora file.

4.4.2 Step 2: Check the COMPATIBLE Initialization Parameter Setting

To configure the full set of tablespace encryption features, you must set the COMPATIBLE initialization parameter for the database to 11.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. Ideally, you should set COMPATIBLE to accommodate the most current release of Oracle Database.

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.2.0.0

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

    sqlplus sys as sysdba
Enter password: password

  7. Restart the Oracle Database instance.

    For example: 

    SHUTDOWN IMMEDIATE
STARTUP

  8. Do not log out of SQL*Plus.

4.4.3 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: 

    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.2.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.2.0\db_1\ORA_KEYSTORES directory: 

    ADMINISTER KEY MANAGEMENT CREATE KEYSTORE 'c:\oracle\product\12.2.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.

4.4.4 Step 4: Open (or Close) the Keystore

You can manually open and close keystores. Auto-login keystores open automatically when they are accessed.

Topics:

  • Opening a Keystore
    After you create a software password-based keystore, you must manually open it before you can use Transparent Data Encryption.
  • Closing a Keystore
    You can close a keystore to disable access to the master key and prevent access to the encrypted columns.
4.4.4.1 Opening a Keystore

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 a 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: 

    ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY software_keystore_password;

keystore altered.

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

4.4.4.2 Closing a Keystore

You can close a keystore to disable access to the master key and prevent access to the encrypted columns.

In most cases, leave the keystore open unless you have a reason for closing it. The keystore must be open for Transparent Data Encryption to work. To reopen the keystore, use the ADMINISTER KEY MANAGEMENT statement.

To close a 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:

    ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY software_keystore_password;

4.4.5 Step 5: Create the Master Encryption Key

The master encryption key, which stored in a keystore, 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: 

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

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';

4.4.6 Step 6: Encrypt Data

Next, you are ready to encrypt either individual table columns or an entire tablespace.

Topics:

4.4.6.1 Encrypting Individual Table Columns

Oracle provides guidelines that you should follow before you encrypt columns, such as checking the column data types.

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:

    Data Types B-L Data Types N-V

    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:

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

    See Oracle Database 2 Day DBA for more information.

  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 Name field. (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:

    Description of encrypt_cols.gif follows
    Description of the illustration encrypt_cols.gif

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

    1. 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.

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

    3. 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.

    4. 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.

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

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

  6. 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.

4.4.6.2 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 the CREATE 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, see Oracle Database 2 Day DBA.

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.

    See Oracle Database 2 Day DBA for more information.

  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:

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

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

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

    4. 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.

      See "Available Methods" under Step 5 in Configuring Network Encryption for more information about these encryption algorithms.

    5. Click Continue.

  6. In the Create Tablespace page, click OK.

    The new tablespace appears in the list of existing tablespaces. Remember that you cannot encrypt an existing tablespace.

See Also:

4.5 Checking Existing Encrypted Data

You can query the database for the data that you have encrypted.

You can check for individually encrypted columns, all tables in the current database instance that have encrypted columns, or all tablespaces that are encrypted.

Topics:

4.5.1 Finding the Type of Keystore That Was Created

The V$ENCRYPTION_KEYS dynamic view lists the type of keystore that was created.

To find the type of keystore that was created:

  • In SQL*Plus, query the V$ENCRYPTION_KEYS view as follows: 

    SELECT KEYSTORE_TYPE FROM V$ENCRYPTION_KEYS;

    The keystore location appears, similar to the following:

    KEYSTORE_TYPE
-----------------------------
SOFTWARE KEYSTORE

4.5.2 Finding the Keystore Location

The V$ENCRYPTION_WALLET dynamic view lists the location of a keystore.

To find the keystore location:

  • In SQL*Plus, query the V$ENCRYPTION_WALLET view as follows: 

    SELECT WRL_PARAMETER FROM V$ENCRYPTION_WALLET;

    The keystore location appears, similar to the following:

    WRL_PARAMETER
-----------------------------
C:\oracle\product\12.2.0\db_1

4.5.3 Checking Whether a Keystore Is Open or Closed

The V$ENCRYPTION_WALLET dynamic view indicates if a keystore is open or closed.

To check whether a keystore is open or closed:

  • In SQL*Plus, query the V$ENCRYPTION_WALLET view as follows: 

    SELECT STATUS FROM V$ENCRYPTION_WALLET;

    The keystore status appears, similar to the following:

    STATUS  
-------
OPEN

4.5.4 Checking Encrypted Columns of an Individual Table

The DESC (for DESCRIBE) statement in SQL*Plus checks the encrypted columns in a database table.

To check the encrypted columns of an individual table:

  • In SQL*Plus, run the DESC statement using the following syntax. 

    DESC tablename;

    For example: 

    DESC OE.ORDER_ITEMS;

    A description of the table schema appears. The following output shows that the QUANTITY column is encrypted: 

    Name                                      Null?     Type
----------------------------------------  --------  --------------------------
ORDER_ID                                  NOT NULL  NUMBER(12)
LINE_ITEM_ID                              NOT NULL  NUMBER(3)
PRODUCT_ID                                NOT NULL  NUMBER(6)
UNIT_PRICE                                          NUMBER(8,2)
QUANTITY                                            NUMBER(8) ENCRYPT

4.5.5 Checking All Encrypted Table Columns in the Current Database Instance

The DBA_ENCRYPTED_COLUMNS data dictionary view lists all encrypted table columns in the current instance.

To check all encrypted table columns in the current database instance:

  • In SQL*Plus, select from the DBA_ENCRYPTED_COLUMNS view:

    For example: 

    SELECT * FROM DBA_ENCRYPTED_COLUMNS;

    This SELECT statement lists all tables and column in the database that contain columns encrypted using Oracle Transparent Data Encryption. For example: 

    OWNER    TABLE_NAME    COLUMN_NAME    ENCRYPTION_ALG     SALT   INTEGRITY_ALG
-------  ----------    -----------    ----------------   ----   -------------
OE       CUSTOMERS     INCOME_LEVEL   AES 128 bits key   YES    SHA-1
OE       UNIT_PRICE    ORADER_ITEMS   AES 128 bits key   YES    SHA-1
HR       EMPLOYEES     SALARY         AES 192 bits key   YES    SHA-1

See Also:

Oracle Database Reference for more information about the DBA_ENCRYPTED_COLUMNS view

4.5.6 Data Dictionary Views for Checking Encrypted Tablespaces

Oracle Database provides data dictionary views that describe encrypted tablespaces.

Table 4-1 lists data dictionary views that you can use to check encrypted tablespaces.

Table 4-1 Data Dictionary Views for Encrypted Tablespaces

Data Dictionary View Description

DBA_TABLESPACES

Describes all tablespaces in the database. For example, to determine if the tablespace has been encrypted, enter the following: 

SELECT TABLESPACE_NAME, ENCRYPTED FROM DBA_TABLESPACES;

TABLESPACE_NAME              ENC
---------------------------- ----
SYSTEM                       NO
SYSAUX                       NO
UNCOTBS1                     NO
TEMP                         NO
USERS                        NO
EXAMPLE                      NO
SECURESPACE                  YES

USER_TABLESPACES

Describes the tablespaces accessible to the current user. It has the same columns as DBA_TABLESPACES, except for the PLUGGED_IN column.

V$ENCRYPTED_TABLESPACES

Displays information about the tablespaces that are encrypted. For example: 

SELECT * FROM V$ENCRYPTED_TABLESPACES;

        TS#  ENCRYPTIONALG  ENCRYPTEDTS
-----------  -------------  -----------
         6   AES128         YES

The list includes the tablespace number, its encryption algorithm, and whether its encryption is enabled or disabled.

If you want to find the name of the tablespace, use the following join operation:

SELECT NAME, ENCRYPTIONALG ENCRYPTEDTS
FROM V$ENCRYPTED_TABLESPACES, V$TABLESPACE
WHERE V$ENCRYPTED_TABLESPACES.TS# = V$TABLESPACE.TS#;

See Also:

Oracle Database Reference for more information about data dictionary views