1. Transparent Data Encryption Guide
  2. Encrypting Columns in Tables

5 Encrypting Columns in Tables

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

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.

Related Topics

Parent topic: Encrypting Columns in Tables

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.

Related Topics

Parent topic: Encrypting Columns in Tables

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.

Related Topics

Parent topic: Encrypting Columns in Tables

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.

Parent topic: Encrypting Columns in Tables

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.

Parent topic: Creating Tables with Encrypted Columns

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

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

If you encrypt a table column without specifying an algorithm, then the column is encrypted using the AES256 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 (AES256). 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.

Parent topic: Creating Tables with Encrypted Columns

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 (default)

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

      In this example:

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

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

Parent topic: Creating Tables with Encrypted Columns

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.

Related Topics

Parent topic: Creating Tables with Encrypted Columns

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

Parent topic: Creating Tables with Encrypted Columns

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 AES256 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 'AES256' 'SHA-1';

ALTER TABLE EMPLOYEE REKEY USING 'AES256' 'NOMAC';

Parent topic: Creating Tables with Encrypted Columns

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 AES192 algorithm:
    CREATE TABLE emp_ext (
    first_name,
    ....
    ssn ENCRYPT USING 'AES192',
    ....

    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;

Parent topic: Creating Tables with Encrypted Columns

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.

Parent topic: Encrypting Columns in Tables

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.

Parent topic: Encrypting Columns in Existing Tables

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 AES256 algorithm. Salt and MAC are added by default. 

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

Parent topic: Encrypting Columns in Existing Tables

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

Parent topic: Encrypting Columns in Existing Tables

5.5.4 Decrypting an Application Table Column

You may want to decrypt an encrypted application table column, for example after encrypting the tablespace that contains the application table.

  • To decrypt a table column, 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);

Parent topic: Encrypting Columns in Existing Tables

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

CREATE INDEX employee_idx on employee (empID);

Parent topic: Encrypting Columns in Tables

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

Parent topic: Encrypting Columns in Tables

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

Parent topic: Encrypting Columns in Tables

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 AES256 algorithm. 

    ALTER TABLE employee REKEY USING 'AES256';

Parent topic: Encrypting Columns in Tables

5.10 Migrating the Algorithm to the Latest Supported Algorithm for Tables

Re-encrypting an already encrypted table column enables you to migrate an earlier algorithm (for example, 3DES168) to the latest supported algorithm.

  1. Query the DBA_ENCRYPTED_COLUMNS data dictionary viiew to find the encryption algorithms that the tables currently use. 
    SELECT TABLE_NAME, ENCRYPTION_ALG 
FROM DBA_ENCRYPTED_COLUMNS 
WHERE ENCRYPTION_ALG = '3 Key Triple DES 168 bits key';

    Output similar to the following appears:

    TABLE_NAME           ENCRYPTION_ALG
-------------------- -----------------------------
EMPS                 3 Key Triple DES 168 bits key
  2. Re-encrypt each table that is listed in the output.
    For example, to migrate to the AES256 algorithm: 
    ALTER TABLE TEST REKEY USING 'AES256';

    You can write a PL/SQL procedure to put all the tables in a list, and then iterate over the list and re-encrypt the tables in the procedure, or you can manually re-encrypt all tables.

  3. To verify the re-encryption:
    The following example queries the DBA_ENCRYPTED_COLUMNS data dictionary view:
    SELECT TABLE_NAME, ENCRYPTION_ALG 
FROM DBA_ENCRYPTED_COLUMNS 
WHERE TABLE_NAME = 'EMPS';

TABLE_NAME           ENCRYPTION_ALG
-------------------- -----------------------------
EMPS                 AES 256 bits key

Parent topic: Encrypting Columns in Tables