ENCRYPT_DATAKEY scalar function
The ENCRYPT_DATAKEY function returns a value that is the result of encrypting the first argument using the specified key label and algorithm.
Authorization
The primary authorization ID used to invoke the ENCRYPT_DATAKEY function must be authorized to use the key label that is specified in key-label-name. Authorization for use of the key label is checked when the function is invoked. When no ACEE is available for the primary authorization ID, Db2 creates one to check for the required authorization when the ENCRYPT_DATAKEY function executes. For more information, see When Db2 creates an ACEE.
Syntax
The schema is SYSIBM.
- expression
- An expression that contains the data to be encrypted. The expression must return an INTEGER, BIGINT, DECIMAL, CHAR, VARCHAR, GRAPHIC, VARGRAPHIC, CLOB, or DBCLOB built-in data type.
- key-label-name
- An expression that contains the ICSF key label name.
A key label is the public name of a protected key and it must be defined in the ICSF key store. The primary authorization ID used to invoke the ENCRYPT_DATAKEY function must be authorized to use the key label.
- AES256R
- Specifies that the 256-bit AES CBC algorithm with a random initialization vector (IV) is used to encrypt the data. This algorithm results in a non-deterministic encrypted value and provides the highest level of protection. A non-deterministic encrypted value is returned for every input value. The resulting text strings cannot be compared even when the encrypted values to be compared originated from the same unencrypted value. An encrypted value must be decrypted before using it in a comparison.
- AES256D
- Specifies that the 256-bit AES CBC algorithm with a fixed initialization vector (IV) will be used to encrypt the data. This algorithm results in a deterministic encrypted value. The resulting text value can be used in equal comparisons against the encrypted text strings. Identical input values encrypted using this mode all result in the same encrypted value. Equality comparisons can be performed on the encrypted values without decrypting the values.
|
Data type of the first argument |
Data type of the result |
|---|---|
| BIGINT, INTEGER, DECIMAL, CHAR, VARCHAR, GRAPHIC, VARGRAPHIC | VARBINARY |
| CLOB, DBCLOB | BLOB |
The length attribute and actual length of the result are determined as follows:
- Length attribute of result in bytes = (((length attribute of source data in bytes + 16 - 1) / 16) * 16) + 15-byte fixed header + 64 bytes for length attribute of key label.
- Actual length of result in bytes = (((actual length of source data in bytes + 16 - 1) / 16) * 16) + 15-byte fixed header + variable length key label in bytes. If expression is an empty string, the actual length of the result in bytes = 15-byte fixed header + variable length key label in bytes.
The actual length of the result can be longer than the length of expression. Therefore, when assigning a resulting encrypted value, ensure that the target is defined with a length attribute that can contain the entire encrypted value.
The result can be null; if expression is null, the result is the null value.
Notes
- Administration of encrypted data
-
A replicated server must have access to the same security label and same decryption function for Db2 to be able to decrypt the data.
If AES256R is specified, the ENCRYPT_DATAKEY function is a non-deterministic function.
If AES256R and AES256D are mixed within the same column or a different key label is used for each row, the encrypted values must be decrypted before being used in any sort of comparison.
Example
Encrypt the values in the character column SSN and insert the resulting values into the PROTECTED_SSN column in the CUSTOMER table: The PROTECTED_SSN column is defined as VARBINARY. Use 'MYKEYLABEL' as the key label, and the AES256D encryption algorithm.
UPDATE CUSTOMER
SET PROTECTED_SSN = ENCRYPT_DATAKEY(SSN,'MYKEYLABEL', AES256D);