PolarDB:Column encryption

1. Syntax (access control, DDL, and DML)

-- Grant permission
GRANT ENCRYPTION_FUN_ADMIN ON *.* TO 'testman'@'%';
-- View permissions
SHOW GRANTS;

CREATE index idx_pan ON my_pan(pan);

-- Encryption function
enhanced_aes_encrypt(data, db_name, table_name)

-- Decryption function
enhanced_aes_decrypt(data)

INSERT INTO `my_pan` VALUES(1,"my_passwd", "aaa");

# 1. Point query without an index (range query)
SELECT id,enhanced_aes_decrypt(pan) FROM `my_pan` WHERE enhanced_aes_decrypt(pan) = 'my_password_10';

# 2. Point query using an index. The retrieved data is in plaintext.
SELECT id,enhanced_aes_decrypt(pan) FROM `my_pan` WHERE pan = enhanced_aes_encrypt('my_password_10', "mydatabase", "my_pan");

# 3. Point query using an index. The retrieved data is in ciphertext.
SELECT id, pan FROM `my_pan` WHERE pan = enhanced_aes_encrypt('my_password_10', "mydatabase", "my_pan");

# Update data using the primary key index.
UPDATE `my_pan` SET pan='new6-password', DESC='Senior6' WHERE id = 1;

# Update data using the encrypted column index.
UPDATE `my_pan` SET pan='new6-password', DESC='Senior6' WHERE pan=enhanced_aes_encrypt('my_password_6', "mydatabase", "my_pan");

# Delete data using the primary key index.
DELETE FROM `my_pan` WHERE id = 1;

# Delete data using the encrypted column index.
DELETE FROM `my_pan` WHERE pan = enhanced_aes_encrypt('my_password_2', "mydatabase", "my_pan");

2. Key management

1. Authorize PolarDB to access KMS.

2. Enable the TDE feature.

   Important

   • Enabling TDE causes the PolarDB cluster to restart. Perform this operation with caution.

   • After TDE is enabled, it cannot be disabled.

   • Enabling the TDE feature itself does not incur additional performance overhead for non-TDE tables or tables that do not use column encryption.

   1. Log on to the PolarDB console. In the navigation pane on the left, click Clusters. Select the region of the cluster, and then click the cluster ID.

   2. In the navigation pane on the left, click Settings and Management >  Security.

   3. On the TDE Settings tab, turn on TDE Status and specify a custom key.

      Note

      Before you delete a key in KMS, confirm that the key is disassociated from all encrypted tables. This includes historical versions. Otherwise, the PolarDB cluster may become unavailable after a restart.

      

3. Select a key.

   Note

   You can select Use an existing custom key or Select a key in the pop-up window. Keys can be generated by the KMS service.

   

4. (Optional) Key rotation.

   Note

   • PolarDB does not automatically update the master key version of custom keys. You can update the key version manually. For more information, see Key rotation.

   • After key rotation, new encrypted tables use the new key for encryption. The data in existing encrypted tables remains encrypted with the original key.

   • Data rotation decrypts the data in a table and then re-encrypts it with the latest key version. Note that this operation consumes CPU and I/O resources. Data rotation is not mandatory, but you must ensure that all keys used by the data tables remain available.

   • Table locking during data rotation

     • If the Online Copy feature is not enabled, the table is locked during data rotation.

     • If the Online Copy DDL feature is enabled, the table is not locked during data rotation. Concurrent DML operations are allowed, but concurrent DDL operations are not allowed.

   To re-encrypt existing tables with a new key, run the following data rotation command:

   ALTER TABLE <table_name> engine = innodb,algorithm=copy;

5. Query table keys.

   You can run the following SQL statement to query the key usage for all encrypted tables in the system.

   SELECT * FROM information_schema.INNODB_COLUMN_ENCRYPTED_TABLE;

3. Parameter settings

You can enable the column encryption feature by setting the loose_polar_enable_column_encryption parameter. For more information, see Set cluster and node parameters. The following table describes this parameter.

4. (Optional) Enable the Online Copy feature

When you use the `COPY` DDL syntax for data rotation, such as modifying table definitions across engines, adjusting column properties, or converting between standard and partitioned tables, the table is locked by default. This can block your services. To resolve this issue, you can enable the Online Copy optimization feature. This feature lets you change the table schema with no locks or with minimal locks. The following table describes the related parameters.