Syntax
ENHANCED_AES_ENCRYPT(plaintext[, iv])
Purpose
This function is mainly used for AES encryption of data. The functionality is controlled by the
tde_methodparameter. For more information, see tde_method.The required privilege is
ENCRYPT.plaintextis the plaintext to be encrypted.[, iv]is an optional parameter that specifies the initialization vector.The key used by this function is the current tenant's primary key. The default encryption algorithm is AES-128-ECB, which can be changed by setting the system variable block_encryption_mode. Only AES encryption algorithms are supported.
This function supports encryption algorithms that require an initialization vector
iv, such as AES-128-CFB1. When using these algorithms for encryption or decryption, you must explicitly provide the initialization vectorivas the second parameter of the function. The minimum length ofivis 16 bytes.Key rotation does not affect decryption operations.
Examples
Use the INSERT / UPDATE statement to explicitly use the encryption function for the encrypted column.
-- Encrypt the plaintext and write it to the cipher_col column in the tbl table.
-- If any value in the value list uses the encryption function, the corresponding privilege is required.
INSERT INTO tbl(cipher_col, ...) VALUES(ENHANCED_AES_ENCRYPT(plaintext), ...);
-- Update the ciphertext column.
UPDATE tbl SET cipher_col = ENHANCED_AES_ENCRYPT(plaintext) WHERE ...;
Use the SELECT statement to directly query the ciphertext column and return the ciphertext.
-- Query the ciphertext column.
SELECT cipher_col FROM tbl;
Direct comparison between ciphertext and plaintext is not supported. You must decrypt the ciphertext or encrypt the plaintext before performing the comparison.
-- cipher_col is the ciphertext column, and 12345 is the plaintext data. It cannot match the corresponding ciphertext data.
SELECT ... FROM tbl WHERE cipher_col = '12345';
-- LIKE statements behave similarly and cannot match the corresponding ciphertext data.
SELECT ... FROM tbl WHERE cipher_col LIKE '123%';
-- Encrypt the plaintext data '12345' and expect it to match the ciphertext data encrypted with the current key. You can use the index.
SELECT ... FROM tbl WHERE cipher_col = ENHANCED_AES_ENCRYPT('12345');
-- You can use the encrypted column for various calculations, including comparisons.
SELECT ... FROM tbl WHERE tbl.cipher_col = another_tbl.another_cipher_col;
SELECT ... FROM tbl GROUP BY cipher_col ORDER BY cipher_col;