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.

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.

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.

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:

• 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.
• 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.
• Step 3: Create the Software Password-Based Keystore
  To create the keystore, use the ADMINISTER KEY MANAGEMENT SQL statement.
• Step 4: Open (or Close) the Keystore
  You can manually open and close keystores. Auto-login keystores open automatically when they are accessed.
• Step 5: Create the Master Encryption Key
  The master encryption key, which stored in a keystore, protects the table keys and tablespace encryption keys.
• Step 6: Encrypt Data
  Next, you are ready to encrypt either individual table columns or an entire tablespace.

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:

• Finding the Type of Keystore That Was Created
  The V$ENCRYPTION_KEYS dynamic view lists the type of keystore that was created.
• Finding the Keystore Location
  The V$ENCRYPTION_WALLET dynamic view lists the location of a keystore.
• Checking Whether a Keystore Is Open or Closed
  The V$ENCRYPTION_WALLET dynamic view indicates if a keystore is open or closed.
• Checking Encrypted Columns of an Individual Table
  The DESC (for DESCRIBE) statement in SQL*Plus checks the encrypted columns in a database table.
• 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.
• Data Dictionary Views for Checking Encrypted Tablespaces
  Oracle Database provides data dictionary views that describe encrypted tablespaces.

SELECT TABLESPACE_NAME, ENCRYPTED FROM DBA_TABLESPACES;

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

SELECT * FROM V$ENCRYPTED_TABLESPACES;

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

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