This topic describes some common questions about column encryption.
When encrypted column data is written to a non-encrypted column by using SQL such as INSERT INTO ... SELECT ..., is the new column ciphertext or plaintext during queries?
- If the user who executes the
INSERT INTO ... SELECT ...statement has the privilege to access the plaintext data, the data is inserted as plaintext. The data is returned as plaintext during queries (unless another encryption rule is applied to the table). - If the user who executes the
INSERT INTO ... SELECT ...statement does not have the privilege to access the plaintext data, the statement cannot be executed.
Is a column that is renamed after encryption still protected by the encryption rule?
The application of column encryption rules is not affected by column name changes. Even if the column name is changed, the column is still protected by the encryption rule.
When an encrypted column is used in a function, such as in SELECT CAST(id AS CHAR), name, password FROM test_01;, is the result ciphertext or plaintext during queries?
In a query, if a projected column contains an encrypted column, the entire projected column is returned as encrypted data.
How to set the value of a non-encrypted column from an encrypted column
When you set the value of a non-encrypted column from an encrypted column, such as in the UPDATE test_01 SET age = id + 2; statement, if the user who executes the statement has the privilege to access the plaintext data, the value of the non-encrypted column is processed as plaintext. Otherwise, the statement cannot be executed.
What is the result of a calculation involving an encrypted column and a plaintext column?
When you calculate the result of an encrypted column and a plaintext column, such as in the SELECT CONCAT(id, name) FROM test_01; statement, the result is returned as encrypted data.
How does backup and restore affect column encryption rules?
When you perform a tenant-level backup and restore, column encryption rules are restored. However, during a table-level restore, the restored objects are independent of the source table. Therefore, the column encryption rules and privileges of the source table cannot be inherited. If the source table contains column encryption rules, we recommend that you evaluate whether to add separate access control to the data restored by using the table-level restore.
How is the IV string generated?
When you use an ENCRYPTION rule, if the encryption algorithm requires an initialization vector (IV) string, the database generates a random IV string during encryption. Each column (projection column, not data column) uses a different random IV string. You cannot explicitly specify a fixed IV string.