Declaration
ENHANCED_AES_ENCRYPT(plaintext[, iv])
Description
This function is primarily used for column encryption, which is controlled by the
tde_methodparameter. For more information, see tde_method.The function requires the
ENCRYPTprivilege.plaintext: The plaintext to be encrypted using AES.[, iv]: Optional. The initialization vector.The key used by this function is the current primary key of the tenant. The default encryption algorithm is AES-128-ECB. You can change the encryption algorithm by setting the value of the system variable block_encryption_mode. Only AES-based encryption algorithms are supported.
Encryption algorithms that require an initialization vector (IV), such as AES-128-CFB1. When using these algorithms for encryption or decryption, you must explicitly pass the IV as the second parameter to the function. The minimum length for an IV is 16 bytes.
Key rotation does not affect decryption operations.
Examples
The INSERT or UPDATE statement explicitly applies the encryption function to the columns that require encryption.
-- Encrypt the plaintext and write it to the cipher_col column of the tbl table.
-- If any value in the value list uses an encryption function, the corresponding permission is required.
INSERT INTO tbl(cipher_col, ...) VALUES(ENHANCED_AES_ENCRYPT(plaintext), ...);
-- Update Ciphertext Column
UPDATE tbl SET cipher_col = ENHANCED_AES_ENCRYPT(plaintext) WHERE ...;
The SELECT statement directly queries the ciphertext column and returns the ciphertext.
-- Query Ciphertext Columns
SELECT cipher_col FROM tbl;
The ciphertext is directly compared with its corresponding plaintext, which is expected to return false. The ciphertext needs to be decrypted, or the plaintext needs to be encrypted, before comparison.
-- cipher_col is the ciphertext column, and 12345 is the plaintext data, which cannot be matched with the corresponding ciphertext data.
SELECT ... FROM tbl WHERE cipher_col = '12345';
-- The LIKE statement behaves similarly and cannot match the corresponding ciphertext data.
SELECT ... FROM tbl WHERE cipher_col LIKE '123%';
-- After encrypting the plaintext data '12345', the expected ciphertext encrypted with the current key should be matched, allowing the use of the index.
SELECT ... FROM tbl WHERE cipher_col = ENHANCED_AES_ENCRYPT('12345');
-- Encrypted columns can be used for various computations, including comparisons, just like regular columns.
SELECT ... FROM tbl WHERE tbl.cipher_col = another_tbl.another_cipher_col;
SELECT ... FROM tbl GROUP BY cipher_col ORDER BY cipher_col;