3 Configuring Transparent Data Encryption

• To create a software keystore on a regular file system, use the following format when you edit the sqlnet.ora file:

  ENCRYPTION_WALLET_LOCATION=
   (SOURCE=
    (METHOD=FILE)
     (METHOD_DATA=
      (DIRECTORY=path_to_keystore)))
  

1. Ensure that you complete the procedure described in "Step 1: Set the Software Keystore Location in the sqlnet.ora File".

2. Log in to the database instance as a user who has been granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege.

   In a multitenant environment, log in to the root. For example:

   sqlplus c##sec_admin as syskm
   Enter password: password
   Connected.
   

   If SQL*Plus is already open and you had modified the sqlnet.ora file during this time, then reconnect to SQL*Plus. The database session must be changed before the sqlnet.ora changes can take effect.

3. Run the ADMINISTER KEY MANAGEMENT SQL statement to create the keystore.

   The syntax is as follows:

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

   In this specification:

   • keystore_location is the path to the keystore directory location of the password-based keystore for which you want to create the auto-login keystore (for example, /etc/ORACLE/WALLETS/orcl). Enclose the keystore_location setting in single quotation marks (' '). To find this location, you can query the WRL_PARAMETER column of the V$ENCRYPTION_WALLET view. (If the keystore was not created in the default location, then the STATUS column of the V$ENCRYPTION_WALLET view is NOT_AVAILABLE.)

   • software_keystore_password is the password of the keystore that you, the security administrator, creates.

   For example, to create the keystore in the /etc/ORACLE/WALLETS/orcl directory:

   ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/etc/ORACLE/WALLETS/orcl' IDENTIFIED BY password;
   
   keystore altered.
   

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

Both of these keystores have system-generated passwords. They are also PKCS#12-based files. The auto-login software keystore can be opened from different computers from the computer where this keystore resides, but the local auto-login software keystore can only be opened from the computer on which it was created. Both the auto-login and local auto-login keystores are created from the password-based software keystores.

1. Ensure that you complete the procedure described in "Step 1: Set the Software Keystore Location in the sqlnet.ora File".

2. Log in to the database instance as a user who has been granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege.

   In a multitenant environment, log in to the root. For example:

   sqlplus c##sec_admin as syskm
   Enter password: password
   Connected.
   

   If SQL*Plus is already open and you had modified the sqlnet.ora file during this time, then reconnect to SQL*Plus. The database session must be changed before the sqlnet.ora changes can take effect.

3. Create a password-based software keystore, as described in "Creating a Password-Based Software Keystore".

4. Run the ADMINISTER KEY MANAGEMENT SQL statement to create the keystore.

   The syntax is as follows:

   ADMINISTER KEY MANAGEMENT CREATE [LOCAL] AUTO_LOGIN KEYSTORE FROM KEYSTORE 'keystore_location' IDENTIFIED BY software_keystore_password;
   

   In this specification:

   • LOCAL enables you to create a local auto-login software keystore. Otherwise, omit this clause if you want the keystore to be accessible by other computers.

   • keystore_location is the path to the directory location of the password-based keystore for which you want to create the auto-login keystore (for example, /etc/ORACLE/WALLETS/orcl). Enclose this setting in single quotation marks (' '). To find this location, query the WRL_PARAMETER column of the V$ENCRYPTION_WALLET view.

   • software_keystore_password is the password-based keystore for which you want to create the auto-login keystore.

   For example, to create an auto-login software keystore of the password-based keystore that is located in the/etc/ORACLE/WALLETS/orcl directory:

   ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '/etc/ORACLE/WALLETS/orcl' IDENTIFIED BY password;
   
   keystore altered.
   

   After you run this statement, the cwallet.sso file appears in the keystore location. The ewallet.p12 file is the password-based wallet.

Transparent Data Encryption uses an auto login keystore only if it is available at the correct location (ENCRYPTION_WALLET_LOCATION, WALLET_LOCATION, or the default keystore location), and the SQL statement to open an encrypted keystore has not already been executed. (Note that auto-login keystores are encrypted, because they have system-generated passwords.)

Note that if the keystore is open but you have not created a TDE master encryption key yet (described next), the STATUS column of the V$ENCRYPTION_WALLET view reminds you with an OPEN_NO_MASTER_KEY status.

1. For password software keystores, ensure that you complete the procedure described in "Step 3: Open the Software Keystore" to open the key.

   Auto-login or local auto-login software keys are opened automatically after you create them. Password-based software keystores must be open before you can set the TDE master encryption key. If the auto-login software keystore is open, then you must close it and open the password-based software keystore. If both the password-based keystore and auto-login keystores are present in the configured location and the password-based keystore is open, then the TDE master encryption key is automatically written to the auto-login keystore as well.

2. Log in to the database instance as a user who has been granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege.

   In a multitenant environment, log in to the root or to the PDB. For example, to log in to a PDB:

   sqlplus sec_admin@hrpdb as syskm
   Enter password: password
   Connected.
   

   To find the available PDBs, query the DBA_PDBS data dictionary view. To check the current PDB, run the show con_name command.

3. Ensure that the database is open in READ WRITE mode.

   You can set the TDE master encryption key if OPEN_MODE is set to READ WRITE. To find the status, for a non-multitenant environment, query the OPEN_MODE column of the V$DATABASE dynamic view. If you are using a multitenant environment, then query the V$PDBS view. (If you cannot access these views, then connect as SYSDBA and try the query again. In order to connect as SYSKM for this type of query, you must create a password file for it. See Oracle Database Administrator's Guide for more information.)

4. Connect using the SYSKM administrative privilege and then run the ADMINISTER KEY MANAGEMENT SQL statement to set the software management keystore.

   ADMINISTER KEY MANAGEMENT SET KEY [USING TAG 'tag'] IDENTIFIED BY password [WITH BACKUP [USING 'backup_identifier']] [CONTAINER = ALL | CURRENT];
   

   In this specification:

   • tag is the associated attributes and information that you define. Enclose this setting in single quotation marks (' ').

   • password is the mandatory keystore password that you created when you created the keystore in "Step 2: Create the Software Keystore".

   • WITH BACKUP creates a backup of the keystore. You must use this option for password-based keystores. Optionally, you can use the USING clause to add a brief description of the backup. Enclose this description in single quotation marks (' '). This identifier is appended to the named keystore file (for example, ewallet_time_stamp_emp_key_backup.p12, with emp_key_backup being the backup identifier). Follow the file naming conventions that your operating system uses.

   • CONTAINER is for use in a multitenant environment. Enter ALL to set the key in all of the PDBs in this CDB, or CURRENT for the current PDB.

   For example:

   ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY password WITH BACKUP USING 'emp_key_backup';
   
   keystore altered.
   

To open a hardware keystore, use the ADMINISTER KEY MANAGEMENT statement with the SET KEYSTORE OPEN clause.

1. Ensure that you complete the procedure described in "Step 2: Configure the Hardware Security Module".

2. Log in to the database instance as a user who has been granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege.

   In a multitenant environment, you must open the keystore first in the root before you can open it in a PDB. For example, to log in to the root:

   sqlplus sec_admin as syskm
   Enter password: password
   Connected.
   

   To find the available PDBs, query the DBA_PDBS data dictionary view. To check the current PDB, run the show con_name command.

   If SQL*Plus is already open and you had modified the sqlnet.ora file during this time, then reconnect to SQL*Plus. The database session must be changed before the sqlnet.ora changes can take effect.

3. Run the ADMINISTER KEY MANAGEMENT SQL statement using the following syntax:

   ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "user_id:password" [CONTAINER = ALL | CURRENT];
   

   In this specification:

   • user_id is the user ID created for the database using the HSM management interface

   • password is the password created for the user ID using the HSM management interface.

     Enclose the user_id:password string in double quotation marks (" ") and separate user_id and password with a colon (:).

   • CONTAINER is for use in a multitenant environment. Enter ALL to set the keystore in all of the PDBs in this CDB, or CURRENT for the current PDB.

   For example:

   ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "psmith:password";
   
   keystore altered.
   

4. Repeat this procedure each time you restart the database instance.

In a multitenant environment, you can create and manage the TDE master encryption key from either the root or the PDB.

1. Ensure that you complete the procedure described in "Step 3: Open the Hardware Keystore".

2. Log in to the database instance as a user who has been granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege.

   In a multitenant environment, log in to the root or to the PDB. For example:

   sqlplus sec_admin@hrpdb as syskm
   Enter password: password
   Connected.
   

   To find the available PDBs, query the DBA_PDBS data dictionary view. To check the current PDB, run the show con_name command.

3. Ensure that the database is open in READ WRITE mode.

   You can set the TDE master encryption key if OPEN_MODE is set to READ WRITE. To find the status, for a non-multitenant environment, query the OPEN_MODE column of the V$DATABASE dynamic view. If you are in a multitenant environment, then query the V$PDBS view. (If you cannot access these views, then connect as SYSDBA and try the query again. In order to connect as SYSKM for this type of query, you must create a password file for it. See Oracle Database Administrator's Guide for more information.)

4. Run the following SQL statement:

   ADMINISTER KEY MANAGEMENT SET KEY [USING TAG 'tag'] IDENTIFIED BY "user_id:password" [CONTAINER = ALL | CURRENT];
   

   In this specification:

   • tag is the associated attributes and information that you define. Enclose this setting in single quotation marks (' ').

   • user_id is the user ID created for the database using the HSM management interface.

   • password is the password created for the user ID using the HSM management interface. Enclose the user_id:password string in double quotation marks (" ") and separate user_id and password with a colon (:).

   • CONTAINER is for use in a multitenant environment. Enter ALL to set the keystore in all of the PDBs in this CDB, or CURRENT for the current PDB.

   For example:

   ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "psmith:password";
   
   keystore altered.
   

TDE adds salt to plaintext before encrypting it. Adding salt makes it harder for attackers to steal data through a brute force attack. TDE also adds a Message Authentication Code (MAC) to the data for integrity checking. The SHA-1 integrity algorithm is used by default.

• To create a table that encrypts a column, use the CREATE TABLE SQL statement with the ENCRYPT clause.

  For example, to encrypt a table column using the default algorithm:

  CREATE TABLE employee (
       first_name VARCHAR2(128),
       last_name VARCHAR2(128),
       empID NUMBER,
       salary NUMBER(6) ENCRYPT);
  

  This example creates a new table with an encrypted column (salary). The column is encrypted using the default encryption algorithm (AES192). Salt and MAC are added by default. This example assumes that the wallet is open and a master key is set.

By default, TDE adds salt to plaintext before encrypting it. Adding salt makes it harder for attackers to steal data through a brute force attack. However, if you plan to index the encrypted column, then you must use the NO SALT parameter.

• To create a table that uses an encrypted column that is a non-default algorithm or no algorithm, run the CREATE TABLE SQL statement as follows:

  • If you do not want to use any algorithm, then include the ENCRYPT NO SALT clause.

  • If you want to use a non-default algorithm, then use the ENCRYPT USING clause, followed by one of the following algorithms enclosed in single quotation marks:

    • 3DES168

    • AES128

    • AES192 (default)

    • AES256

The following example shows how to specify encryption settings for the empID and salary columns.

CREATE TABLE employee (
     first_name VARCHAR2(128),
     last_name VARCHAR2(128),
     empID NUMBER ENCRYPT NO SALT,
     salary NUMBER(6) ENCRYPT USING '3DES168');

In this example:

• The empID column is encrypted and does not use salt. Both the empID and salary columns will use the 3DES168 encryption algorithm, because all of the encrypted columns in a table must use the same encryption algorithm.

• The salary column is encrypted using the 3DES168 encryption algorithm. Note that the string that specifies the algorithm must be enclosed in single quotation marks (' '). The salary column uses salt by default.

If the number of rows and encrypted columns in the table is large, then bypassing TDE checks can add up to a significant amount of disk space. In addition, this saves processing cycles and reduces the performance overhead associated with TDE.

TDE uses the SHA-1 integrity algorithm by default. All of the encrypted columns in a table must use the same integrity algorithm. If you already have a table column using the SHA-1 algorithm, then you cannot use the NOMAC parameter to encrypt another column in the same table.

• To bypass the integrity check during encryption and decryption operations, use the NOMAC parameter in the CREATE TABLE and ALTER TABLE statements.

• To encrypt specific columns in an external table, use the ENCRYPT clause when you define those columns:

  A system-generated key encrypts the columns. For example, the following CREATE TABLE SQL statement encrypts the ssn column using the 3DES168 algorithm:

  CREATE TABLE emp_ext (
      first_name,
      ....
      ssn ENCRYPT USING '3DES168',
      ....
  
  

If you plan to move an external table to a new location, then you cannot use a randomly generated key to encrypt the columns. This is because the randomly generated key will not be available at the new location.

For such scenarios, you should specify a password while you encrypt the columns. After you move the data, you can use the same password to regenerate the key required to access the encrypted column data at the new location.

Table partition exchange also requires a password-based TDE table key.

Example 3-3 creates an external table using a password to create the TDE table key.

• To add an encrypted column to an existing table, use the ALTER TABLE ADD statement, specifying the new column with the ENCRYPT clause.

Example 3-4 adds an encrypted column, ssn, to an existing table, called employee. The ssn column is encrypted with the default AES192 algorithm. Salt and MAC are added by default.

• To encrypt an existing unencrypted column, use the ALTER TABLE MODIFY statement, specifying the unencrypted column with the ENCRYPT clause.

The following example encrypts the first_name column in the employee table. The first_name column is encrypted with the default AES192 algorithm. Salt is added to the data, by default. You can encrypt the column using a different algorithm. If you want to index a column, then you must specify NO SALT. You can also bypass integrity checks by using the NOMAC parameter.

ALTER TABLE employee MODIFY (first_name ENCRYPT);

The following example encrypts the first_name column in the employee table using the NOMAC parameter.

ALTER TABLE employee MODIFY (first_name ENCRYPT 'NOMAC');

• To disable column encryption, use the ALTER TABLE MODIFY command with the DECRYPT clause.

Example 3-5 decrypts the first_name column in the employee table.

1. Log in to the database instance.

   In a multitenant environment, log in to the PDB. For example:

   sqlplus sec_admin@hrpdb
   Enter password: password
   Connected.
   

   To find the available PDBs, query the DBA_PDBS data dictionary view. To check the current PDB, run the show con_name command.

2. Check the current setting of the COMPATIBLE parameter.

   For example:

   SHOW PARAMETER COMPATIBLE
    
   NAME                                 TYPE        VALUE
   ------------------------------------ ----------- ------------------------------
   compatible                           string      12.0.0.0
   noncdbcompatible                     BOOLEAN     FALSE
   

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. In SQL*Plus, connect as a user who has the SYSDBA administrative privilege, and then shut down the database.

   For example:

   CONNECT /AS SYSDBA
   SHUTDOWN
   

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

   For example:

   COMPATIBLE = 12.2.0.0
   

7. In SQL*Plus, ensure that you are connected as a user who has the SYSDBA administrative privilege, and then start the database.

   For example:

   CONNECT /AS SYSDBA
   STARTUP
   

   If tablespace encryption is in use, then open the keystore at the database mount. The keystore must be open before you can access data in an encrypted tablespace.

   STARTUP MOUNT;
   ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY password;
   ALTER DATABASE OPEN;
   

1. Log in to the database instance as a user who has been granted the CREATE TABLESPACE system privilege.

   In a multitenant environment, log in to the PDB. For example:

   sqlplus sec_admin@hrpdb as syskm
   Enter password: password
   Connected.
   

   To find the available PDBs, query the DBA_PDBS data dictionary view. To check the current PDB, run the show con_name command.

2. Run the CREATE TABLESPACE statement, using its encryption clauses.

   For example:

   CREATE TABLESPACE encrypt_ts
     DATAFILE '$ORACLE_HOME/dbs/encrypt_df.dbf' SIZE 1M
     ENCRYPTION USING 'AES256'
     DEFAULT STORAGE (ENCRYPT);
   

   In this specification:

   • ENCRYPTION USING 'AES256' specifies the encryption algorithm and the key length for the encryption. Enclose this setting in single quotation marks (' '). The key lengths are included in the names of the algorithms. If you do not specify an encryption algorithm, then the default encryption algorithm, AES128, is used. Choose from the following algorithms:

     • 3DES168

     • AES128

     • AES192

     • AES256

   • ENCRYPT in the DEFAULT STORAGE clause encrypts the tablespace.