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.

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

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

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

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

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

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

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.

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

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.

ALTER TABLE EMPLOYEE REKEY USING 'AES256' 'SHA-1';

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

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

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

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.

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

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

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