Table encryption

Table encryption allows you to encrypt tables or materialized views with sensitive data without the performance impact that encrypting the entire database might cause. When table encryption is enabled, table pages for the encrypted table, associated index pages, and temporary file pages are encrypted. The transaction log pages that contain transactions on encrypted tables are also encrypted.

For information about encrypting materialized views, see Encrypt and decrypt materialized views.

To encrypt tables in your database, you must have table encryption enabled. Enabling table encryption must be done at database initialization. To see whether table encryption is enabled, query the EncryptionScope database property using the DB_PROPERTY function, as follows:

SELECT DB_PROPERTY( 'EncryptionScope' );

If the return value is TABLE, table encryption is enabled.

To see the encryption algorithm in effect for table encryption, query the Encryption database property using the DB_PROPERTY function, as follows:

SELECT DB_PROPERTY( 'Encryption' );

For a list of supported encryption algorithms, see Encrypting and decrypting a database.

Performance impact of table encryption

For encrypted tables, each table page is encrypted when written to the disk, and is decrypted when read in from the disk. This process is invisible to applications. However, there may be a slight negative impact on performance when reading from, or writing to, encrypted tables. Encrypting or decrypting existing tables can take a long time, depending on the size of the table.

Index pages for indexes on columns in an encrypted table are also encrypted, as are transaction log pages containing transactions on the encrypted table, and all pages in the temporary file for the database. All other database and transaction log pages are unencrypted.

Encrypted tables can contain compressed columns. In this case, the data is compressed before it is encrypted.

Encrypting tables does not impact storage requirements.

Starting a database that has table encryption enabled

Starting a database that has table encryption enabled is the same as starting an encrypted database. For example, if the database is started with the -ek option, a key must be specified. If the database is started with the -ep option, you are prompted for the key. See Initialization utility (dbinit).

Enabling table encryption in the database

Table encryption must be enabled and configured at database creation time. You must re-create the database with table encryption enabled if your database does not have table encryption enabled, or if you have database encryption in effect.

To create a database with table encryption (SQL)

Create a database with the CREATE DATABASE statement, and specify a key and an encryption algorithm.

The following command creates the database new.db with strong encryption enabled for tables using the key abc, and the AES256_FIPS encryption algorithm:
CREATE DATABASE 'new.db' ENCRYPTED TABLE KEY 'abc' ALGORITHM 'AES256_FIPS';
Later, when you encrypt a table in this database, the AES256_FIPS algorithm and abc key are used.

To create a database with table encryption (command prompt)

Create a database with the dbinit -et and -ek options, and specify a key and an encryption algorithm.

The following command creates the database new.db with strong encryption enabled for tables using the key abc and the AES256_FIPS encryption algorithm:
dbinit new.db -et -ek abc -ea AES256_FIPS
Later, when you encrypt a table in this database, the AES256_FIPS algorithm and abc key are used.

To create a database with table encryption using an existing database (SQL)

Create an encrypted copy of the database with the CREATE ENCRYPTED TABLE DATABASE statement, and specify a key.

The following example creates a database called contacts2 from an existing database called contacts1. The new database supports encrypted tables.

CREATE ENCRYPTED TABLE DATABASE 'contacts2.db'
   FROM 'contacts1.db'
   KEY 'Sd8f6654'
   OLD KEY 'Sc8e5543';

Later, when you encrypt a table in this database, the AES algorithm and Sd8f6654 key are used.

Encrypting a table

To encrypt tables in your database, table encryption must already be enabled in the database. See Enabling table encryption in the database.

When you encrypt a table, the encryption algorithm and key that were specified at database creation time are used.

To encrypt a table at table creation (SQL)

Create a table using the ENCRYPTED clause of the CREATE TABLE statement.

The following command creates an encrypted table named MyEmployees:
CREATE TABLE MyEmployees ( MemberID CHAR(40), CardNumber INTEGER ) ENCRYPTED;

To encrypt a table after it has been created (SQL)

Encrypt a table with the ENCRYPTED clause of the ALTER TABLE statement.

The following statements create a table called MyEmployees2 and then encrypt it.

CREATE TABLE MyEmployees2 (
   MemberID CHAR(40),
   CardNumber INTEGER );
ALTER TABLE MyEmployees2 
   ENCRYPTED;