Enabling table encryption in a database (SQL)

Create a database with table encryption by using the CREATE DATABASE statement, or enable table encryption in an existing database by using the CREATE ENCRYPTED TABLE DATABASE statement.

Prérequis

By default, you must have the SERVER OPERATOR system privilege to execute the CREATE DATABASE statement and the CREATE ENCRYPTED TABLE DATABASE statement. The required privileges can be changed by using the -gu database server option.

Contexte et remarques

Table encryption must be enabled and configured at database creation time. If your database does not have table encryption enabled, or if you have database encryption in effect, using the CREATE ENCRYPTED TABLE DATABASE statement creates a copy of the database with table encryption enabled, and does not overwrite the original database file.

Task

Create a database with table encryption, or enable table encryption on an existing database.

Option	Action
Create a database with table encryption	Create a database with the CREATE DATABASE statement, and specify a key and an encryption algorithm.
Enable table encryption for an existing database	Create a copy of the database with the CREATE ENCRYPTED TABLE DATABASE statement, and specify a key.

Résultat

Table encryption is enabled.

Suivant

You create an encrypted table by using the CREATE TABLE statement, or by altering an existing table to be encrypted by using the ALTER TABLE statement. When you encrypt a table, the key and/or algorithm specified when enabling table encryption is used.

Exemple

The following command creates the database mynewdemo.db with strong encryption enabled for tables using the key abc, and the AES256_FIPS encryption algorithm. It also creates a DBA user ID with password sql.

CREATE DATABASE 'C:\temp\mynewdemo.db'
   DBA USER 'DBA' DBA PASSWORD 'sql'
   ENCRYPTED TABLE
   KEY 'abc'
   ALGORITHM 'AES256_FIPS';

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

CREATE ENCRYPTED TABLE DATABASE 'C:\temp\contacts2.db'
   FROM 'C:\temp\contacts1.db'
   KEY 'Sd8f6654'
   OLD KEY 'Sc8e5543';

Enabling table encryption in a database (SQL)

Task

See also