CREATE ENCRYPTED DATABASE statement

Creates an encrypted copy of an existing database, including all transaction logs and dbspaces; or creates a copy of an existing database with table encryption enabled.

Syntax

Create an encrypted copy of a database

CREATE ENCRYPTED DATABASE newfile
FROM oldfile
[ KEY newkey ] 
[ ALGORITHM algorithm ]
[ KEY DERIVATION ITERATIONS number ]
[ OLD KEY oldkey ]

algorithm : 
   'SIMPLE'
   | 'AES' 
   | 'AES256' 
   | 'AES_FIPS'
   | 'AES256_FIPS'

Create a copy of a database with table encryption enabled

CREATE ENCRYPTED TABLE DATABASE newfile
FROM oldfile
[ KEY newkey ] 
[ ALGORITHM algorithm ]
[ KEY DERIVATION ITERATIONS number ]
[ OLD KEY oldkey ]

Parameters

CREATE ENCRYPTED DATABASE clause
Specifies the name for the new database.
CREATE ENCRYPTED TABLE DATABASE clause
Specifies the name for the new database. The new database is not encrypted, but has table encryption enabled.
FROM clause
Specifies the name of the original database file (oldfile).
KEY clause
Specifies the encryption key for newfile. The key can be either a string or a variable name. Not required for ALGORITHM 'SIMPLE'.
ALGORITHM clause
Specifies the encoding algorithm to use for newfile. You may choose between SIMPLE obfuscation or some form of AES encryption. If you specify a KEY clause but do not specify the ALGORITHM clause, AES (128-bit encryption) is used by default. If you specify 'SIMPLE' for algorithm, you do not specify a KEY clause.
KEY DERIVATION ITERATIONS
Specifies the number of times that the encryption key is hashed. Specify a whole number between 1 and 1000. The default value is 2, which is 2000 iterations. The higher the number, the better security. If you are running the database on a slow computer, then a very high number of iterations could result in the database taking longer to start (once the database is running, there is no performance impact).
OLD KEY clause
Use this clause to specify the encryption key for oldfile. The key can be either a string or a variable name. This clause is only required if oldfile is encrypted using some form of AES encryption.

Remarks

Use this statement to create an encrypted copy of an existing database, including all transaction logs and dbspaces.

You can also use this statement to create a copy of a database and enable table encryption in the copy.

The database file oldfile can be an unencrypted database, an encrypted database, or a database with table encryption enabled.

Creating an encrypted copy of a database takes an existing database, oldfile, and creates an encrypted copy of it, newfile.

Creating a copy of a database with table encryption enabled takes an existing database, oldfile, and creates a copy of it, newfile, with table encryption enabled. When you use this syntax, any tables encrypted in oldfile are encrypted in newfile as well. If no tables were encrypted in oldfile, but you want to encrypt them, then execute an ALTER TABLE...ENCRYPTED statement on each table you want to encrypt.

Neither syntax replaces or removes oldfile.

If oldfile uses transaction log or transaction log mirror files, they are renamed newfile.log and newfile.mlg respectively.

If oldfile contains dbspace files, an E (for encrypted) is added to the file name. For example, when you execute the CREATE ENCRYPTED DATABASE statement, the file mydbspace.dbs is changed to mydbspace.dbsE.

You can use this statement to change the encryption algorithm and key for a database. However, the CREATE ENCRYPTED DATABASE statement produces a new file (newfile), and does not replace or remove the previous version of the file (oldfile).

When encrypting the database or enabling table encryption in the database, you must specify an encryption key. The software uses Password-Based Key Derivation Function #2 (PBKDF2), which is part of the PKCS#5 standard to protect the key from brute-force attacks. The software repeatedly applies a cryptographic hash to the encryption key. Use the KEY DERIVATION ITERATIONS clause to specify the number of times to apply the hash.

CREATE ENCRYPTED DATABASE and CREATE ENCRYPTED TABLE DATABASE cannot be executed against a database that requires recovery. You must use the CREATE ENCRYPTED FILE statement instead.

These statements are not supported in procedures, triggers, events, or batches.

You cannot be connected to the database you are encrypting. You must be connected to a different database. For example, connect to the utility database. The database that you are encrypting must not be running.

You can also encrypt an existing database or change an existing encryption key by unloading and reloading the database using the dbunload -an option with either -ek or -ep.

You can also create an encrypted database, or a database with table encryption enabled, using the CREATE DATABASE statement.

If disk sandboxing is enabled, then database operations are limited to the directory where the main database file is located.

Privileges

Your ability to execute this statement depends on the setting for the -gu database option, and whether you have the SERVER OPERATOR system privilege.

Side effects

None.

Standards

ANSI/ISO SQL Standard
Not in the standard.

Example

The following example creates an encrypted copy of the sample database called demoEnc.db. The new database is encrypted with AES256 encryption.

CREATE ENCRYPTED DATABASE 'demoEnc.db'
   FROM 'C:\\Users\\Public\\Documents\\SQL Anywhere
          17\\Samples\\sample.db'
   KEY 'Sd8f6654*Mnn'
   ALGORITHM 'AES256';

The following example creates a copy of the sample database called demoTableEnc.db. Table encryption is enabled on the new database. Since a key was specified with no algorithm, AES encryption is used.

CREATE ENCRYPTED TABLE DATABASE 'demoTableEnc.db'
   FROM 'C:\\Users\\Public\\Documents\\SQL Anywhere
          17\\Samples\\sample.db'
   KEY 'Sd8f6654';