Syntax
ENHANCED_AES_ENCRYPT(plaintext[, iv])
Purpose
This function is primarily used for AES encryption, which can be enabled by using the
tde_methodparameter. For more information, see tde_method.The required privilege for this function is
ENCRYPT.plaintextis the plaintext to be encrypted using AES.[, iv]is an optional parameter that represents the initialization vector.The function uses the current primary key of the tenant for encryption. The default encryption algorithm is AES-256-CBC. You can change the encryption algorithm by using the block_encryption_mode parameter. Currently, only encryption algorithms of the AES type are supported.
Encryption algorithms that require an initialization vector
iv, such as AES-128-CFB1, are supported. When using such algorithms for encryption and decryption, the initialization vectorivmust be explicitly provided as the second parameter of the function. The minimum length ofivis 16 bytes.Key rotation does not affect decryption operations.
Examples
The INSERT and UPDATE statements explicitly use the encryption function for columns that need encryption.
-- Encrypt plaintext and insert it into the cipher_col column of 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 encrypted column.
UPDATE tbl SET cipher_col = ENHANCED_AES_ENCRYPT(plaintext) WHERE ...;
The SELECT statement directly queries the encrypted column and returns the ciphertext.
-- Query the encrypted column.
SELECT cipher_col FROM tbl;
Direct comparison between ciphertext and its corresponding plaintext is not supported. You need to decrypt the ciphertext or encrypt the plaintext before performing the comparison.
-- cipher_col is an encrypted column, and 12345 is plaintext data, which cannot match the corresponding ciphertext.
SELECT ... FROM tbl WHERE cipher_col = '12345';
-- LIKE statements behave similarly and cannot match the corresponding ciphertext.
SELECT ... FROM tbl WHERE cipher_col LIKE '123%';
-- After encrypting the plaintext '12345', it is expected to match the ciphertext encrypted by the current key. Indexes can be utilized.
SELECT ... FROM tbl WHERE cipher_col = ENHANCED_AES_ENCRYPT('12345');
-- Encrypted columns can be treated as regular columns 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;