The Password and Hint Specifications

The SET ENCRYPTION statement or an encryption function can define a password and hint for the current session. The password must be specified as a character expression that returns at least 6 bytes, but no more than 128. The optional hint is specified as a character expression that returns no more than 32 bytes.

The purpose of the hint is to help users to remember the password. When you call ENCRYPT_AES or ENCRYPT_TDES with a hint argument, it is encrypted and embedded in the encrypted_data, from which GETHINT can retrieve it. But if you define hint as NULL, or omit hint when SET ENCRYPTION specified no default hint for the session password, no hint is embedded in the encrypted_data.

The password used for encryption and decryption is either the password argument to the function, or if you omit this argument, it is the session password specified in the last SET ENCRYPTION statement executed before you invoke the function.

The DECRYPT_CHAR, DECRYPT_BINARY, or GETHINT function call fails with an error if the encrypted_data argument is not in an encrypted format, or if the password argument to a decryption function is omitted when no session password value was set by SET ENCRYPTION. An error also results if the password used for decryption is not the same password used for encryption.

Encryption key management, which is critical to the secure operation of the database, is delegated entirely to the application. This implementation means that the password itself is not stored in the database. Without help from the user through the application, the database server cannot decrypt the encrypted data.

If you invoke any of these functions from a UDR, you might prefer to set a session password in the SET ENCRYPTION statement. Otherwise, password will be visible to users who can view the sysprocbody.data column in the system catalog.