create encryption key

Creates encryption keys. All the information related to keys and encryption is encapsulated by create encryption key, which allows you to specify the encryption algorithm and key size, the key’s default property, an optional user-specified password to encrypt the key, as well as the use of an initialization vector or padding during the encryption process.

The SAP ASE server uses Security Builder Crypto for key generation and encryption.

Syntax

Create the master key:

create encryption key [dual] master
	[for AES] with passwd char_literal

Create the service key:

create encryption key syb_extpasswdkey 
	[ with { static key | master key }]
create encryption key syb_syscommkey
		[ with { static key | master key }]

Create the column encryption key:

create encryption key [[database.][owner].]keyname
	[as default] 
	[for algorithm_name]
	[with [{{passwd {char_literal | system_encr_passwd} | master key}]
	[key_length num_bits]
	[init_vector {null | random}]
	[pad {null | random}]
	[[no] dual_control]}]

Create an encryption key for fully encrypted databases:

create encryption key keyname
    [for algorithm]
    for database encryption
    [with
        {[master key]
        [key_length 256]
        [init_vector random]
        [[no] dual_control]}

Parameters

keyname – must be unique in the user’s table, view, and procedure name space in the current database. Specify the database name if the key is in another database; specify the owner name if you are creating a key for another user. The default value for owner is the current user, and the default value for database is the current database. Only the system security officer can create keys for other users.
as default – allows the system security officer or the key custodian to create a database default key for encryption. The existence of a database default encryption key enables the table creator to specify encryption without using a keyname on create table, alter table, and select into. The SAP ASE server uses the default key from the same database. The default key may be changed. See alter encryption key.
for algorithm_name – specifies the algorithm you are using. Advanced Encryption Standard (AES) is the only algorithm supported. AES supports key sizes of 128 bits, 192 bits, and 256 bits, and a block size of 16 bytes.
for database encryption – indicates that you are creating an encryption key to encrypt an entire database, rather than a column.
for AES – uses the Advanced Encryption Standard (AES) encryption algorithm to encrypt data.
syb_extpasswdkey | syb_syscommkey –
- syb_extpasswdkey – all external passwords in sysattributes are reencrypted with the new key using strong encryption
- syb_syscommkey – any subsequent execution of sp_hidetext uses the new key with strong encryption. sp_hidetext must be executed on an existing database object for the object to be encrypted with the new key
static key | master key – indicates that you are creating an encryption key using a static or master key.
Specifying master key creates a master key in the master database, and indicates to SAP ASE to protect the database encryption key using that key. By default, SAP ASE uses this master key (if it exists) to protect database encryption keys.
keylength num_bits – the size, in bits, of the key to be created. For AES, valid key lengths are 128, 192, and 256 bits. The default key length is 128 bits.
The only valid length for a database encryption key is 256; you see an error message if you use any other size.
password_phrase – is a quoted alphanumeric string of up to 255 bytes in length that the SAP ASE server uses to generate the key used to encrypt the column encryption key (the key encryption key).
init_vector random – specifies use of an initialization vector during encryption. When an initialization vector is used by the encryption algorithm, the cipher text of two identical pieces of plain text are different, which prevents a cryptanalyst from detecting patterns of data. Use an initialization vector to increase the security of your data.
An initialization vector has some performance implications. Index creation, and optimized joins and searches, can be performed only on a column for which the encryption key does not specify an initialization vector.

The default is to use an initialization vector, that is, init_vector random. Use of an initialization vector implies using a cipher-block chaining (CBC) mode of encryption; setting init_vector null implies the electronic codebook (ECB) mode.
init_vector null – omits the use of an initialization vector when encrypting. This makes the column suitable for supporting an index.
Database encryption enforces stronger security than column encryption; if you specify init_vector null for database encryption as you can for creating a column encryption key, SAP ASE returns an error.
pad null – is the default, which omits random padding of data. You cannot use padding if the column must support an index.
pad random – data is automatically padded with random bytes before encryption. You can use padding instead of an initialization vector to randomize the cipher text. Padding is suitable only for columns whose plaintext length is less than half the block length. For the AES algorithm, the block length is 16 bytes.
[no] dual_control – Indicates whether the new key must be encrypted using dual controls. By default, dual control is not configured. Both the master key and dual master key must exist in the master database to use dual control.

Examples

Example 1 – Specifies a 256-bit key called “safe_key” as the database default key. The system security officer enters:
```
create encryption key safe_key as default for AES with keylength 256
```
Example 2 – Creates a 128-bit key called “salary_key” for encrypting columns using random padding:
```
create encryption key salary_key for AES with init_vector null pad random
```
Example 3 – Creates a 192-bit key named “mykey” for encrypting columns using an initialization vector:
```
create encryption key mykey for AES with keylength 192 init_vector random
```
Example 4 – Ceates a key that is protected by a user-specified password:
```
create encryption key key1 with passwd 'Worlds1Biggest6Secret'
```
You must enter user-specified passwords that protect keys before accessing a column encrypted by the key. See set.
Example 5 – Specifies a 256-bit key called “safe_key” as the database default key. Because the key does not specify a password, the SAP ASE server uses the database-level master key as the key encryption key for safe_key. If there is no master key, the SAP ASE server uses the system encryption password:
```
create encryption key safe_key as default for AES with keylength 256
```

Example 6 – Encrypts CEK k3 with a combination of the master key and “Whybother”:

create encryption key k3 with passwd 'Whybother' dual_control
create encryption key k1 with keylength 192

Example 7 – Creates a master key to protect "testkey":

create encryption key testkey for database encryption
    with master key

Example 8 – Creates a dual master key to protect "testkey":

create encryption key testkey for database encryption
    with dual_control

Example 9 – Creates both a master key and dual master key to protect "testkey":
```
create encryption key testkey for database encryption
    with master key dual_control
```
Example 10 – Creates a master key to protect "testkey", while explicitly excluding the dual master key:
```
create encryption key testkey for database encryption
    with master key no dual_control
```
Example 11 – Creates a master key to protect "testkey" while explicitly excluding a dual master key:
```
create encryption key testkey for database encryption
    with no dual control
```

Example 12 –

sp_configure 'enable encrypted columns', 1
create encryption key master with passwd "testpassword"
set encryption passwd 'testpassword' for key master
create encryption key dbkey for database encryption

Usage

The SAP ASE server does not save the user-specified password. It saves a string of validating bytes known as the “salt” in sysencryptkeys.eksalt, which allows the SAP ASE server to recognize whether a password used on a subsequent encryption or decryption operation is legitimate for a key. You must supply the password to the SAP ASE server before you can access any column encrypted by keyname.

For fully encrypted databases:

The database encryption key does not support the pad option in create encryption key command.
The database encryption key cannot be the default key for column encryption.
Successfully created database encryption keys are stored in the sysencryptkeys table of the master database and are indicated by this key type:
```
#define EK_DBENCKEY       0x1000
```

For information about auditing, see Auditing Encrypted Columns in the Encrypted Columns Users Guide.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

The permission checks for create encryption key differ based on your granular permissions settings.

Setting	Description
Enabled	With granular permissions enabled, you must have the following privilege or privileges based on the encryption key type: column encryption key – `create encryption key` or `manage column encryption key` master key – `manage master key` service key – `manage service key` database encryption key – `manage database encryption key` You must have the `manage any encryption key` privilege to create an encryption key for another user.
Disabled	With granular permissions disabled, you must be a user with sso_role, keycustodian_role, or have create encryption key privilege to create an encryption key. You must have sso_role to create an encryption key for another user.

Setting

Description

Enabled

With granular permissions enabled, you must have the following privilege or privileges based on the encryption key type:

column encryption key – create encryption key or manage column encryption key
master key – manage master key
service key – manage service key
database encryption key – manage database encryption key

You must have the manage any encryption key privilege to create an encryption key for another user.

Disabled

With granular permissions disabled, you must be a user with sso_role, keycustodian_role, or have create encryption key privilege to create an encryption key. You must have sso_role to create an encryption key for another user.