This topic describes some common questions about column encryption.
What is the data type of a column that is inserted into a non-encrypted column by using an INSERT INTO ... SELECT ... statement?
- If the user who executes the
INSERT INTO ... SELECT ...statement has the permission to access the plaintext data, the data is inserted as plaintext. The data is returned as plaintext during queries (unless an encryption rule is applied to the table). - If the user who executes the
INSERT INTO ... SELECT ...statement does not have the permission 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 encryption rule is applied to the column regardless of the column name. Even if the column name is changed, the column is still protected by the encryption rule.
What is the data type of a column that is returned in a query, such as SELECT CAST(id AS CHAR), name, password FROM test_01;?
If the query includes a projection column that contains encrypted data, the entire projection 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 permission 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 between an encrypted column and a plaintext column?
If 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, the 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 permissions of the source table cannot be inherited. If the source table contains column encryption rules, we recommend that you evaluate whether to add 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.