Data encryption functions

You can use the SET ENCRYPTION PASSWORD statement with built-in SQL encryption functions that use Advanced Encryption Standard (AES) and Triple DES (3DES) encryption to secure your sensitive data. When you use encryption, only those users who have the correct password will be able to read, copy, or modify the data.

Use the SET ENCRYPTION PASSWORD statement with the following built-in encryption and decryption functions:
  • ENCRYPT_AES 
    ENCRYPT_AES(data-string-expression
[, password-string-expression [, hint-string-expression ]])
  • ENCRYPT_TDES 
    ENCRYPT_TDES (data-string-expression
 [, password-string-expression [, hint-string-expression ]])
  • DECRYPT_CHAR 
    DECRYPT_CHAR(EncryptedData [, PasswordOrPhrase])
  • DECRYPT_BINARY 
    DECRYPT_BINARY(EncryptedData [, PasswordOrPhrase])
  • GETHINT 
    GETHINT(EncryptedData)

If you have used the SET ENCRYPTION PASSWORD statement to specify a default password, then the database server applies that password in subsequent calls to encryption and decryption functions that you invoke in the same session.

Use ENCRYPT_AES and ENCRYPT_TDES to define encrypted data and use DECRYPT_CHAR and DECRYPT_BINARY to query encrypted data. Use GETHINT to display the password hint string, if set, on the server.

You can use these SQL built-in functions to implement column-level or cell-level encryption.
  • Use column-level encryption to encrypt all values in a given column with the same password.
  • Use cell-level encryption to encrypt data within the column with different passwords.
Tip: If you intend to select encrypted data from a large table, specify an unencrypted column on which to select the rows. You can create indexes or foreign-key constraints on columns that contain encrypted data, but to do so is an inefficient use of resources, because such indexes and foreign-key constraints are not used by the query optimizer.