This topic introduces some common questions about column encryption.
If encrypted data is written to an unencrypted column using INSERT INTO ... SELECT ..., will the new column contain plaintext or ciphertext?
- If the user executing the
INSERT INTO ... SELECT ...statement has the privilege to access data in plaintext, then plaintext is inserted and queries will return plaintext (unless another encryption rule is applied to the table). - If the user does not have the privilege to access data in plaintext, this operation is not permitted.
Does renaming an encrypted column affect its encryption?
Renaming a column does not affect the application of column encryption rules. Even after the column is renamed, it remains protected by the encryption rules.
If an encrypted column is used in a function, such as SELECT CAST(id AS CHAR), name, password FROM test_01;, will the result be plaintext or ciphertext?
If the projection includes an encrypted column, the data is returned as ciphertext.
How do I set the value of an unencrypted column based on an encrypted column?
When executing a statement such as UPDATE test_01 SET age = id + 2;, if the user has privilege to access plaintext data, the value of the unencrypted column is processed as plaintext. Otherwise, this operation is not permitted.
What happens when an operation involves both encrypted and plaintext columns?
If an operation involves both encrypted and plaintext columns, such as SELECT CONCAT(id, name) FROM test_01;, the result is returned as ciphertext.
What happens to column encryption rules during backup and restore?
Column encryption rules are restored during tenant-level backup and restore. However, table-level restore only recovers independent objects and does not inherit the encryption rules and privileges from the source table. If the source table contains encryption rules, we recommend evaluating whether additional access controls are needed for the restored data.