5 Encrypting Columns in Tables

You can use Transparent Data Encryption to encrypt individual columns in database tables.

• About Encrypting Columns in Tables
  You can encrypt individual columns in tables.
• Data Types That Can Be Encrypted with TDE Column Encryption
  Oracle Database supports a specific set of data types that can be used with TDE column encryption.
• Restrictions on Using TDE Column Encryption
  TDE column encryption is performed at the SQL layer. Oracle Database utilities that bypass the SQL layer cannot use TDE column encryption services.
• Creating Tables with Encrypted Columns
  Oracle Database provides a selection of different algorithms that you can use to define the encryption used in encrypted columns.
• Encrypting Columns in Existing Tables
  You can encrypt columns in existing tables. As with new tables, you have a choice of different algorithms to use to definite the encryption.
• Creating an Index on an Encrypted Column
  You can create an index on an encrypted column.
• Adding Salt to an Encrypted Column
  Salt, which is a random string added to data before encryption, is a way to strengthen the security of encrypted data.
• Removing Salt from an Encrypted Column
  You can use the ALTER TABLE SQL statement to remove salt from an encrypted column.
• Changing the Encryption Key or Algorithm for Tables with Encrypted Columns
  You can use the ALTER TABLE SQL statement to change the encryption key or algorithm used in encrypted columns.

5.1 About Encrypting Columns in Tables

You can encrypt individual columns in tables.

Whether you choose to encrypt individual columns or entire tablespaces depends on the data types that the table has. There are also several features that do not support TDE column encryption.

5.2 Data Types That Can Be Encrypted with TDE Column Encryption

Oracle Database supports a specific set of data types that can be used with TDE column encryption.

You can encrypt data columns that use a variety of different data types.

Supported data types are as follows:

• BINARY_DOUBLE

• BINARY_FLOAT

• CHAR

• DATE

• INTERVAL DAY TO SECOND

• INTERVAL YEAR TO MONTH

• NCHAR

• NUMBER

• NVARCHAR2

• RAW (legacy or extended)

• TIMESTAMP (includes TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE)

• VARCHAR2 (legacy or extended)

If you want to encrypt large binary objects (LOBs), then you can use Oracle SecureFiles. Oracle SecureFiles enables you to store LOB data securely. To encrypt a LOB using SecureFiles, you use the CREATE TABLE or ALTER TABLE statements.

You cannot encrypt a column if the encrypted column size is greater than the size allowed by the data type of the column.

Table 5-1 shows the maximum allowable sizes for various data types.

Table 5-1 Maximum Allowable Size for Data Types

Data Type	Maximum Size
CHAR	1932 bytes
VARCHAR2 (legacy)	3932 bytes
VARCHAR2 (extended)	32,699 bytes
NVARCHAR2 (legacy)	1966 bytes
NVARCHAR2 (extended)	16,315 bytes
NCHAR	966 bytes
RAW (extended)	32,699 bytes

Note:

TDE tablespace encryption does not have these data type restrictions.

5.3 Restrictions on Using TDE Column Encryption

TDE column encryption is performed at the SQL layer. Oracle Database utilities that bypass the SQL layer cannot use TDE column encryption services.

Do not use TDE column encryption with the following database features:

• Index types other than B-tree

• Range scan search through an index

• Synchronous change data capture

• Transportable tablespaces

• Columns that have been created as identity columns

In addition, you cannot use TDE column encryption to encrypt columns used in foreign key constraints.

Applications that must use these unsupported features can use the DBMS_CRYPTO PL/SQL package for their encryption needs.

Transparent Data Encryption protects data stored on a disk or other media. It does not protect data in transit. Use the network encryption solutions discussed in Oracle Database Security Guide to encrypt data over the network.

5.4 Creating Tables with Encrypted Columns

Oracle Database provides a selection of different algorithms that you can use to define the encryption used in encrypted columns.

• About Creating Tables with Encrypted Columns
  You can use the CREATE TABLE SQL statement to create a table with an encrypted column.
• Creating a Table with an Encrypted Column Using the Default Algorithm
  By default, TDE uses the AES encryption algorithm with a 192-bit key length (AES192).
• Creating a Table with an Encrypted Column Using No Algorithm or a Non-Default Algorithm
  You an use the CREATE TABLE SQL statement to create a table with an encrypted column.
• Using the NOMAC Parameter to Save Disk Space and Improve Performance
  You can bypass checks that Transparent Data Encryption (TDE) performs. This can save up to 20 bytes of disk space per encrypted value.
• Example: Using the NOMAC Parameter in a CREATE TABLE Statement
  You can use the CREATE TABLE SQL statement to encrypt a table column using the NOMAC parameter.
• Example: Changing the Integrity Algorithm for a Table
  You can use the ALTER TABLE SQL statement in different foregrounds to convert different offline tablespaces in parallel.
• Creating an Encrypted Column in an External Table
  The external table feature enables you to access data in external sources as if the data were in a database table.

5.4.1 About Creating Tables with Encrypted Columns

You can use the CREATE TABLE SQL statement to create a table with an encrypted column.

To create relational tables with encrypted columns, you can specify the SQL ENCRYPT clause when you define database columns with the CREATE TABLE SQL statement.

5.4.2 Creating a Table with an Encrypted Column Using the Default Algorithm

By default, TDE uses the AES encryption algorithm with a 192-bit key length (AES192).

If you encrypt a table column without specifying an algorithm, then the column is encrypted using the AES192 algorithm. 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. (Starting with Oracle Database release 21c, SHA-1 is deprecated. If you use TDE column encryption, then Oracle recommends that you implement TDE tablespace encryption instead.)

• 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 keystore is open and a master encryption key is set.

  Note:

  If there are multiple encrypted columns in a table, then all of these columns must use the same pair of encryption and integrity algorithms.

  Salt is specified at the column level. This means that an encrypted column in a table can choose not to use salt irrespective of whether or not other encrypted columns in the table use salt.

5.4.3 Creating a Table with an Encrypted Column Using No Algorithm or a Non-Default Algorithm

You an use the CREATE TABLE SQL statement to create a table with an encrypted column.

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.

5.4.4 Using the NOMAC Parameter to Save Disk Space and Improve Performance

You can bypass checks that Transparent Data Encryption (TDE) performs. This can save up to 20 bytes of disk space per encrypted value.

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. (Starting with Oracle Database release 21c, SHA-1 is deprecated. If you use TDE column encryption, then Oracle recommends that you implement TDE tablespace encryption instead.) 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.

5.4.5 Example: Using the NOMAC Parameter in a CREATE TABLE Statement

You can use the CREATE TABLE SQL statement to encrypt a table column using the NOMAC parameter.

Example 5-1 creates a table with an encrypted column. The empID column is encrypted using the NOMAC parameter.

Example 5-1 Using the NOMAC parameter in a CREATE TABLE statement

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

5.4.6 Example: Changing the Integrity Algorithm for a Table

You can use the ALTER TABLE SQL statement in different foregrounds to convert different offline tablespaces in parallel.

Example 5-2 shows how to change the integrity algorithm for encrypted columns in a table. The encryption algorithm is set to 3DES168 and the integrity algorithm is set to SHA-1. The second ALTER TABLE statement sets the integrity algorithm to NOMAC.

Example 5-2 Changing the Integrity Algorithm for a Table

ALTER TABLE EMPLOYEE REKEY USING '3DES168' 'SHA-1';

ALTER TABLE EMPLOYEE REKEY USING '3DES168' 'NOMAC';

5.4.7 Creating an Encrypted Column in an External Table

The external table feature enables you to access data in external sources as if the data were in a database table.

External tables can be updated using the ORACLE_DATAPUMP access driver.

• 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-protected TDE table key.

  The following example creates an external table using a password to create the TDE table key:

  CREATE TABLE emp_ext (
       first_name,
       last_name,
       empID,
       salary,
       ssn ENCRYPT IDENTIFIED BY password
  )  ORGANIZATION EXTERNAL
     (
      TYPE ORACLE_DATAPUMP
      DEFAULT DIRECTORY "D_DIR"
      LOCATION('emp_ext.dat')
      )
      REJECT LIMIT UNLIMITED
  AS SELECT * FROM EMPLOYEE;

5.5 Encrypting Columns in Existing Tables

You can encrypt columns in existing tables. As with new tables, you have a choice of different algorithms to use to definite the encryption.

• About Encrypting Columns in Existing Tables
  The ALTER TABLE SQL statement enables you to encrypt columns in an existing table.
• Adding an Encrypted Column to an Existing Table
  You can encrypt columns in existing tables, use a different algorithm, and use NO SALT to index the column.
• Encrypting an Unencrypted Column
  You can use the ALTER TABLE MODIFY statement to encrypt an existing unencrypted column.
• Disabling Encryption on a Column
  You may want to disable encryption for reasons of compatibility or performance.

5.5.1 About Encrypting Columns in Existing Tables

The ALTER TABLE SQL statement enables you to encrypt columns in an existing table.

To add an encrypted column to an existing table, or to encrypt or decrypt an existing column, you use the ALTER TABLE SQL statement with the ADD or MODIFY clause.

5.5.2 Adding an Encrypted Column to an Existing Table

You can encrypt columns in existing tables, use a different algorithm, and use NO SALT to index the column.

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

  The following example 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.

  ALTER TABLE employee ADD (ssn VARCHAR2(11) ENCRYPT);
  

5.5.3 Encrypting an Unencrypted Column

You can use the ALTER TABLE MODIFY statement to encrypt an existing unencrypted column.

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

5.5.4 Disabling Encryption on a Column

You may want to disable encryption for reasons of compatibility or performance.

• To disable column encryption, use the ALTER TABLE MODIFY command with the DECRYPT clause.
  The following example decrypts the first_name column in the employee table.

  ALTER TABLE employee MODIFY (first_name DECRYPT);

5.6 Creating an Index on an Encrypted Column

You can create an index on an encrypted column.

The column being indexed must be encrypted without salt. If the column is encrypted with salt, then the ORA-28338: cannot encrypt indexed column(s) with salt error is raised.

• To create an index on an encrypted column, use the CREATE INDEX statement with the ENCRYPT NO SALT clause.
  The following example shows how to create an index on a column that has been encrypted without salt.

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

5.7 Adding Salt to an Encrypted Column

Salt, which is a random string added to data before encryption, is a way to strengthen the security of encrypted data.

Salt ensures that the same plaintext data does not always translate to the same encrypted text. Salt removes the one common method that intruders use to steal data, namely, matching patterns of encrypted text. Adding salt requires an additional 16 bytes of storage per encrypted data value.

• To add or remove salt from encrypted columns, use the ALTER TABLE MODIFY SQL statement.
  For example, suppose you want to encrypt the first_name column using salt. If the first_name column was encrypted without salt earlier, then the ALTER TABLE MODIFY statement reencrypts it using salt.

  ALTER TABLE employee MODIFY (first_name ENCRYPT SALT);
  

5.8 Removing Salt from an Encrypted Column

You can use the ALTER TABLE SQL statement to remove salt from an encrypted column.

• To remove salt from an encrypted column, use the ENCRYPT NO SALT clause in the ALTER TABLE SQL statement.
  For example, suppose you wanted to remove salt from the first_name column. If you must index a column that was encrypted using salt, then you can use this statement to remove the salt before indexing.

  ALTER TABLE employee MODIFY (first_name ENCRYPT NO SALT);

5.9 Changing the Encryption Key or Algorithm for Tables with Encrypted Columns

You can use the ALTER TABLE SQL statement to change the encryption key or algorithm used in encrypted columns.

Each table can have only one TDE table key for its columns. You can regenerate the TDE table key with the ALTER TABLE statement. This process generates a new key, decrypts the data in the table using the previous key, reencrypts the data using the new key, and then updates the table metadata with the new key information. You can also use a different encryption algorithm for the new TDE table key.

• To change the encryption key or algorithm for tables that contain encrypted columns, use the ALTER TABLE SQL statement with the REKEY or REKEY USING clause.
  For example:

  ALTER TABLE employee REKEY;
  

  The following example regenerates the TDE table key for the employee table by using the 3DES168 algorithm.

  ALTER TABLE employee REKEY USING '3DES168';