When you issue a select, insert, update, or delete command against an encrypted column, Adaptive Server automatically encrypts or decrypts the data using the encryption key associated with the encrypted column.
When you issue an insert or update on an encrypted column:
If you do not have insert or update permission on the encrypted column, the command fails.
If the column is encrypted by a key with a user-specified password, Adaptive Server expects the password to be available. If the user-specified password has not been set, the command fails. See “Accessing encrypted data with user password”
Adaptive Server decrypts the encryption key.
Adaptive Server encrypts the data using the column’s encryption key.
Adaptive Server inserts the varbinary cipher text data into the table.
After the insert or update, Adaptive Server clears the memory holding the plain text. At the end of the statement, it clears the memory holding the raw encryption keys.
When you issue a select command on data from an encrypted column:
The command fails if you do not have select permission on the encrypted column.
If the encryption key is associated with a column encrypted with a user-specified password, Adaptive Server expects the password to be available. If the user-specified password has not been set, the select statement fails. See “Accessing encrypted data with user password”. Otherwise, Adaptive Server decrypts the encryption key.
The decryption of the selected data succeeds if you have decrypt permission on the column, and Adaptive Server returns plain text data to the user.
If a decrypt default has been declared on the encrypted column and if you do not have decrypt permission on the column, Adaptive Server returns the decrypt default value.
When you include encrypted columns in a where clause:
If you do not have decrypt permission on the column, and the column includes a decrypt default, the where clause predicate evaluates to false. See “Decrypt default columns and query qualifications”.
When possible, Adaptive Server makes the comparison without decrypting the data if:
The where clause joins an encrypted column with another column encrypted by the same key without use of an initialization vector or random pad
The column data is being matched with an equality or an inequality condition to a constant value
