PolarDB:Column encryption

Performance overhead

Index usage

The index on an encrypted column sorts by ciphertext, not plaintext. Because comparisons in the encrypted value space are meaningless for ranges, only point queries (equality comparisons) can use the index. Range queries such as WHERE age > 20 and pattern matching such as LIKE '%key%' cannot use the index and result in a full table scan.

If your queries require range comparisons on a sensitive column, consider applying encryption at the application layer instead, where you have more control over the query logic.

Table property limitations

• Tables with virtual columns are not supported.

• Only the InnoDB storage engine is supported.

• Partitioned tables are not supported.

• Encrypted columns must use the VARBINARY type.

• The field length must be at least 44 bytes.

• You cannot add an encrypted column directly with ADD COLUMN. Use MODIFY to convert an existing standard column to an encrypted column.

Step 1: Authorize PolarDB to access KMS

Authorize PolarDB to access KMS before proceeding.

Step 2: Enable TDE

Column encryption uses TDE for key management. Enable TDE in the PolarDB console.

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

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

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

   

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

   

Before deleting a key in KMS, confirm that it is disassociated from all encrypted tables, including historical versions. Otherwise, the PolarDB cluster may become unavailable after a restart.

Step 3: Enable the column encryption parameter

Set the loose_polar_enable_column_encryption parameter to ON. For instructions, see Set cluster and node parameters.

Step 4: Grant the required privilege

Your database account must have the ENCRYPTION_FUN_ADMIN privilege to use column encryption functions. A privileged account can grant this privilege.

-- Grant the privilege
GRANT ENCRYPTION_FUN_ADMIN ON *.* TO 'testman'@'%';

-- Verify the privilege
SHOW GRANTS;

Create a table with an encrypted column

Use the ENCRYPTION keyword to mark a column as encrypted.

USE mydatabase;
CREATE TABLE IF NOT EXISTS `my_pan` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `pan` VARBINARY(128) NOT NULL ENCRYPTION,
    `desc` blob Default NULL,
    KEY `idx_pan` (`pan`),
    PRIMARY KEY (`id`)
);

A column with a default value of NULL can be encrypted, but NULL values are not encrypted.

Convert an existing column

Use MODIFY to convert a standard column to an encrypted column, or to remove encryption:

-- Convert to encrypted
ALTER TABLE my_pan MODIFY `pan` VARBINARY(128) ENCRYPTION;

-- Remove encryption
ALTER TABLE my_pan MODIFY `pan` VARBINARY(128) NOT ENCRYPTION;

Build an index on an encrypted column

The syntax is the same as for a standard column. Index data is stored as ciphertext and sorted by encrypted values.

CREATE INDEX idx_pan ON my_pan(pan);

Insert data

Data inserted into an encrypted column is automatically encrypted. Calling enhanced_aes_encrypt on insert is not required.

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

Query data

The two functions for encryption and decryption are:

-- Encrypt a value (for use in WHERE clauses)
enhanced_aes_encrypt(data, db_name, table_name)

-- Decrypt a value (for use in SELECT or WHERE clauses)
enhanced_aes_decrypt(data)

Three common query patterns:

-- 1. Point query without index (range query style): decrypts every row, full table scan
SELECT id, enhanced_aes_decrypt(pan) FROM `my_pan`
WHERE enhanced_aes_decrypt(pan) = 'my_password_10';

-- 2. Point query using index, returns 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 index, returns ciphertext
SELECT id, pan FROM `my_pan`
WHERE pan = enhanced_aes_encrypt('my_password_10', "mydatabase", "my_pan");

Update and delete data

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

-- Update 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 using the primary key index
DELETE FROM `my_pan` WHERE id = 1;

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

View key usage

Run the following statement to view the key in use for all encrypted tables:

SELECT * FROM information_schema.INNODB_COLUMN_ENCRYPTED_TABLE;

Rotate keys

PolarDB does not automatically update the master key version for custom keys. To rotate a key manually, see Key rotation.

After key rotation:

• New encrypted tables use the new key.

• Existing encrypted tables remain encrypted with the original key.

Re-encrypt existing tables

Data rotation re-encrypts all data in a table using the latest key version. This operation consumes CPU and I/O resources and is not mandatory — but all keys used by existing data tables must remain available.

ALTER TABLE <table_name> ENGINE = innodb, ALGORITHM = COPY;

By default, ALGORITHM = COPY locks the table during data rotation. To avoid service interruption, enable the Online Copy feature (see Enable Online Copy).

Locking behavior during data rotation: