Syntax
ENHANCED_AES_ENCRYPT(plaintext[, iv])
Purpose
This function is mainly used to encrypt data using the AES algorithm. You can enable this feature by setting the
tde_methodparameter. For more information, see tde_method.This function requires the
ENCRYPTprivilege.plaintextspecifies the plaintext to be encrypted.[, iv]is an optional parameter that specifies the initialization vector.This function uses the current tenant-level primary key. By default, the encryption algorithm is AES-128-ECB. You can change the encryption algorithm by setting the value of the block_encryption_mode system variable. Only AES encryption algorithms are supported.
This function supports encryption algorithms that require an initialization vector, such as AES-128-CFB1. When you use such algorithms for encryption or decryption, you must explicitly specify the initialization vector
ivas the second parameter of the function. The minimum length ofivis 16 bytes.Key rotation does not affect decryption operations.
Examples
You can explicitly use the encryption function in INSERT or UPDATE statements to encrypt the values of the specified columns.
-- Encrypt the plaintext and write it to the cipher_col column of the tbl table.
-- If any value in the values list uses the encryption function, you must have the corresponding privileges.
INSERT INTO tbl(cipher_col, ...) VALUES(ENHANCED_AES_ENCRYPT(plaintext), ...);
-- Update the encrypted column.
UPDATE tbl SET cipher_col = ENHANCED_AES_ENCRYPT(plaintext) WHERE ...;
You can directly query the encrypted column in SELECT statements to return the ciphertext.
-- Query the encrypted column.
SELECT cipher_col FROM tbl;
You cannot directly compare the ciphertext with its corresponding plaintext. You must either decrypt the ciphertext or encrypt the plaintext before performing the comparison.
-- cipher_col is the encrypted column, and 12345 is the plaintext data. The plaintext data 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', you can match it with the ciphertext data encrypted using the current key. You can also use indexes.
SELECT ... FROM tbl WHERE cipher_col = ENHANCED_AES_ENCRYPT('12345');
-- You can use the encrypted column in 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;