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)
To create a database with table encryption (command prompt)
To create a database with table encryption using an existing database (SQL)
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)
To encrypt a table after it has been created (SQL)
See also