A column encryption key must exist before a table owner can mark a column for encryption on a new or existing table. When you set up keys for the first time, consider:
Key owner or custodian assignment – the system security officer (SSO) must grant create encryption key permission to create keys. By default, the sso_role and the keycustodian_role have create encryption key permission. See “Role of the key custodian”.
Whether keys should be created in a separate key database – Sybase recommends that you use a separate database for keys, especially if keys are encrypted by the system encryption password.
The number of keys needed – you can create a separate key for each encrypted column, or you can use the same key to encrypt columns across multiple tables. From a performance standpoint, encrypted columns that join with equivalent columns in other tables should share the same key. For security purposes, unrelated columns should use different keys.
Column encryption in Adaptive Server uses the Advanced Encryption Standard (AES) symmetric key encryption algorithm, with available key sizes of 128, 192, and 256 bits. Random-key generation and cryptographic functionality is provided by the FIPS 140-2 compliant modules.
To securely protect key values, Adaptive Server uses a 256-bit key-encrypting key (KEK), which may be a master key, or an internal key derived from either the system encryption password or a user-specified password. See Chapter 3, “Using Database-Level Master and Dual Master Keys.” Adaptive Server encrypts the new key (the column encryption key) and stores the result in sysencryptkeys.
Figure 2-1: Encrypting column encryption keys using KEK
By default, Adaptive Server creates 256-bit key-encryption keys. For compatibility with versions earlier than 15.7, it uses a 128-bit key if the KEK is derived from the system encryption password.
The syntax for create column encryption key is:
create encryption key [[database.][owner].]keyname
[as default] [for algorithm]
[with
{[key_length num_bits]
[{passwd 'passwd_phrase' | passwd system_encr_passwd |
master key}]
[init_vector {null | random}]
[pad {null | random}]
[[no] dual_control]
}]
where:
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, and specify the owner’s name if more than one key of that name exists in the database. 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.
You cannot create temporary key names that start with “#”.
as default – allows the system security officer or key custodian to create a database default key for encryption. This enables the table creator to specify encryption without using a keyname on create table, alter table, and select into. Adaptive Server uses the default key from the same database. The default key may be changed.
for algorithm – Advanced Encryption Standard (AES) is the only algorithm supported. AES supports key sizes of 128, 192, and 256 bits, and a block size of 16 bytes. The block size is the number of bytes in an encryption unit. Large data is subdivided for encryption.
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 keylength is 128 bits.
passwd password_phrase – indicates to ASE to protect the CEK using the user password password_phrase, which can be a quoted alphanumeric string up to 255 bytes in length.
passwd system_encr_passwd – indicates to ASE to protect the CEK using the system encryption password.
master key – indicates to ASE to protect the CEK using the master key. By default, Adaptive Server uses the master key (if it exists) to protect column encryption keys. See “Key protection”.
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 detection of data patterns. Using an initialization vector can add to the security of your data.
Use of an initialization vector implies using a cipher-block chaining (CBC) mode of encryption, where each block of data is combined with the previous block before encryption, with the first block being combined with the initialization vector.
However, initialization vectors have some performance implications. You can create indexes and optimize joins and searches only on columns where the encryption key does not specify an initialization vector. See Chapter 10, “Performance Considerations.”
null – omits the use of an initialization vector when encrypting. This makes the column suitable for supporting an index.
The default is to use an initialization vector, that is, init_vector random.
Setting init_vector null implies the electronic codebook (ECB) mode, where each block of data is encrypted independently.
To encrypt one column using an initialization vector and another column without using an initialization vector, create two separate keys—one that specifies use of an initialization vector and another that specifies no initialization vector.
pad
null – the default, omits random padding of data.
You cannot use padding if the column must support an index.
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 plain text length is less than half the block length. For the AES algorithm the block length is 16 bytes.
dual control – indicates whether the new key must be encrypted using dual control. By default, dual control is not configured. See Chapter 3, “Using Database-Level Master and Dual Master Keys.”
These examples use various encryption attributes when creating a column encryption key, and many assume you have already created the master key or set the system encryption password (see “Key protection”).
Example 1 – specifies a 256-bit key called “safe_key” as the database default key. Because the key does not specify a password, Adaptive Server uses the database-level master key as the KEK for safe_key. If there is no master key, Adaptive Server uses the system encryption password:
create encryption key safe_key as default for AES with keylength 256
Only the system security officer or a user with the keycustodian_role can create a default key.
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 – creates a key protected by a user-specified password:
create encryption key key1 with passwd 'Worlds1Biggest6Secret'
If a key is protected by a user-specified password, that password must be entered a column encrypted by the key can be accessed. See Chapter 7, “Protecting Data Privacy from the Administrator,” for information about using keys with explicit passwords.
Example 5 – creates a key protected by dual-control:
create encryption key dualprotectedkey with passwd "Pass4Tomorrow" dual_control
Key “dualprotectedkey” is protected by the master key and a user password (in dual control). To access the key, you must enter both the user password for the key and the password for the master key.
The sso_role and keycustodian_role implicitly have permission to create encryption keys. The system security officer or the key custodian uses this syntax to grant create encryption key permissions to others:
grant create encryption key to user_name | role_name | group_name
For example:
grant create encryption key to key_admin_role
To revoke key creation permission, use:
revoke create encryption key
{to | from} user_name | role_name | group_name
grant all does not grant create
encryption key permission to the user. It must be explicitly
granted by the system security officer.
