Oracle PL/SQL for DBAs

Adding TDE to Existing Tables

In the example in the previous section, you saw how to use TDE while creating a new table. You can encrypt a column of an existing table, as well. To encrypt the column SSN of the table ACCOUNTS, specify:

    ALTER TABLE accounts MODIFY (ssn ENCRYPT);

This operation does two things:

• It creates a key for the column SSN.

• It converts all values in the column to encrypted format.

The encryption is then performed inside the database. By default, the AES (with 192-bit key) algorithm is used for the encryption. You can choose a different algorithm by specifying it in the command. For instance, to choose 128-bit AES encryption, you would specify:

    ALTER TABLE accounts MODIFY (ssn ENCRYPT USING 'AES128');

You can choose AES128, AES256, or 3DES168 (168-bit Triple DES algorithm) as parameters. After encrypting a column, let’s look at the table:

    SQL> DESC accounts
     Name      Null? Type
     --------- ----- ------------
     ACC_NO          NUMBER
     ACC_NAME        VARCHAR2(30)
     SSN             VARCHAR2(9) ENCRYPT

Note the clause ENCRYPT after the datatype. To find the encrypted columns in the database, search the new data dictionary view DBA_ENCRYPTED_COLUMNS.

What about the performance impact of TDE? There is no overhead when working with non-encrypted columns. You can expect to see a small amount of overhead when accessing encrypted columns. If encryption is no longer required, you can turn it off for that column by specifying:

    ALTER TABLE account MODIFY (ssn DECRYPT);