■ Encrypting Columns in Existing Tables ■ Creating an Index on an Encrypted Column
■ Adding or Removing Salt from an Encrypted Column
■ Changing the Encryption Key or Algorithm for Tables with Encrypted Columns ■ Data Types That Can Be Encrypted with TDE Column Encryption
■ Restrictions on Using TDE Column Encryption
Creating Tables with Encrypted Columns
To create relational tables with encrypted columns, specify the SQL ENCRYPT clause when you define database columns with the CREATE TABLE statement.
This section contains the following topics:
■ Creating a Table with an Encrypted Column
■ Creating a Table with an Encrypted Column Using a Nondefault Algorithm and No Salt
■ Using the NOMAC Parameter to Save Disk Space and Improve Performance ■ Creating an Encrypted Column in an External Table
Creating a Table with an Encrypted Column 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, the column is encrypted using the AES192 algorithm. TDE adds salt to cleartext before encrypting it. This 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.
Example 8–3 creates a new table with an encrypted column. The column is encrypted using the default encryption algorithm (AES192). Salt and MAC are added by default.
Example 8–3 Creating a New Table with an Encrypted Column Using the Default Algorithm (AES192)
CREATE TABLE employee ( first_name VARCHAR2(128), last_name VARCHAR2(128), empID NUMBER,
salary NUMBER(6) ENCRYPT );
Creating a Table with an Encrypted Column Using a Nondefault Algorithm and No Salt By default, TDE adds salt to cleartext before encrypting it. This makes it harder for
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 other encrypted columns in the table use salt or not.
Using Transparent Data Encryption
attackers to steal data through a brute force attack. However, if you plan to index the encrypted column, you must use NO SALT.
TDE also enables you to specify a nondefault encryption algorithm. You can choose from one of the following algorithms:
■ 3DES168 ■ AES128
■ AES192 (default)
■ AES256
Example 8–4 shows how to specify the NO SALT parameter with the SQL ENCRYPT
clause (empID NUMBER ENCRYPT NO SALT). It also shows the syntax for specifying a different encryption algorithm (salary NUMBER(6) ENCRYPT USING '3DES168'). Note that the string which specifies the algorithm must be enclosed in single quotation marks (' ').
The empID and salary columns will both use the 3DES168 encryption algorithm. This is because all encrypted columns in a table must use the same encryption algorithm. The salary column will use salt by default. The empID column will not use salt as the
NO SALT option has been specified for it.
Example 8–4 Creating a New Table with an Encrypted Column Using 3DES168 and NO SALT
CREATE TABLE employee ( first_name VARCHAR2(128), last_name VARCHAR2(128), empID NUMBER ENCRYPT NO SALT,
salary NUMBER(6) ENCRYPT USING '3DES168' );
Using the NOMAC Parameter to Save Disk Space and Improve Performance The NOMAC
parameter enables you to skip the integrity check performed by TDE. This saves 20 bytes of disk space per encrypted value. If the number of rows and encrypted columns in the table is large, then this adds up to a significant amount of disk space.
The NOMAC parameter also reduces the performance overheads associated with TDE. Using the NOMAC parameter causes the integrity check to be skipped during encryption and decryption operations. This saves processing cycles and leads to faster
performance.
Example 8–5 creates a table with an encrypted column. The empID column is encrypted using the NOMAC parameter.
Note: TDE uses the SHA-1 integrity algorithm by default. All
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.
You can change the integrity algorithm used by all encrypted columns in a table using the ALTER TABLE....REKEY... command. See
Using Transparent Data Encryption
Securing Stored Data Using Transparent Data Encryption 8-11 Example 8–5 Using the NOMAC parameter in a CREATE TABLE statement
CREATE TABLE employee ( first_name VARCHAR2(128), last_name VARCHAR2(128),
empID NUMBER ENCRYPT 'NOMAC' NO SALT , salary NUMBER(6)
);
Example 8–6 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 8–6 Changing the Integrity Algorithm for a Table SQL> ALTER TABLE EMPLOYEE REKEY USING '3DES168' 'SHA-1'; Table altered.
SQL> ALTER TABLE EMPLOYEE REKEY USING '3DES168' 'NOMAC'; Table altered.
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 defining those columns. A system generated key is used to encrypt the columns. For example, the following definition encrypts the ssn column using the 3DES168 algorithm:
CREATE TABLE emp_ext ( first_name, ....
ssn ENCRYPT USING '3DES168', ....
... ...
If you plan to move your 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 encrypting the columns. After you move the data, you can use the same password to regenerate the key required to access encrypted column data at the new location.
Table partition exchange also requires a password-based table key.
Example 8–7 creates an external table using a password to create the table key.
Example 8–7 Creating a New External Table with a Password-Generated Table Key CREATE TABLE emp_ext (
first_name, last_name, empID, salary,
ssn ENCRYPT IDENTIFIED BY "xIcf3T9u"
See Also: Oracle Database Concepts for discussions on Schema Objects and Tables.
Using Transparent Data Encryption
) ORGANIZATION EXTERNAL (
TYPE ORACLE_DATAPUMP DEFAULT DIRECTORY "D_DIR" LOCATION('emp_ext.dat') )
REJECT LIMIT UNLIMITED AS SELECT * FROM EMPLOYEE;