This topic answers some frequently asked questions about column encryption.
What happens when you write encrypted column data into a non-encrypted column using an INSERT INTO ... SELECT ... statement? Is the data returned as ciphertext or plaintext?
- If the user executing the
INSERT INTO ... SELECT ...statement has the plaintext access privilege for the relevant data, the inserted data will be plaintext. When querying, the data will also be plaintext, unless there are additional encryption rules on the table. - If the user does not have the plaintext access privilege for the relevant data, the operation is not allowed.
Does renaming an encrypted column affect the encryption rules applied to it?
Renaming an encrypted column does not affect the encryption rules applied to it. The column will still be protected by the encryption rules even after the name is changed.
What happens when you query an encrypted column within a function, such as SELECT CAST(id AS CHAR), name, password FROM test_01;? Is the data returned as ciphertext or plaintext?
In a query, if the projected columns include an encrypted column, the entire projected column will be returned as ciphertext.
Setting values from an encrypted column to a non-encrypted column
When setting values from an encrypted column to a non-encrypted column, such as in UPDATE test_01 SET age = id + 2;, if the user executing the statement has the plaintext access privilege, the non-encrypted column's value will be handled as plaintext. Otherwise, the user cannot execute this statement.
Results of calculations involving encrypted and plaintext columns
When calculating results involving encrypted and plaintext columns, such as in SELECT CONCAT(id, name) FROM test_01;, the result will be returned as ciphertext.
Impact of backup and restore on column encryption rules
During tenant-level backup and restore, column encryption rules are restored. However, table-level restore, which recovers independent objects, does not inherit the source table's column encryption rules and privileges. Therefore, if the source table contains column encryption rules, it is recommended to assess whether to add separate access controls for the restored data.
How are IV strings generated?
When using ENCRYPTION rules, if the encryption algorithm requires an initialization vector (IV) string, the database generates a random one during the encryption phase. Each column (projected column, not data column) uses a different random IV. Users cannot explicitly specify a fixed IV.