Column encryption-PolarDB(PolarDB)-阿里云帮助中心

Version requirements

This feature is available only for PolarDB for MySQL 8.0.2, version 8.0.2.2.30 or later.

Background

In previous versions, PolarDB for MySQL used encryption functions such as aes_encrypt to perform field-level data encryption. However, this approach required you to manage keys, handle key rotation, and control access permissions on the client side. The PolarDB for MySQL column encryption feature integrates these operations into the database server. You simply set the encryption attribute for the columns you want to protect. Additionally, column encryption uses Online DDL to perform non-blocking key rotation.

Limitations

Performance impact

Write performance

Using column encryption incurs performance overhead. With the binary log disabled, column encryption adds approximately 3% overhead to single-threaded inserts. This overhead is lower when the binary log is enabled or if the table has a wide row structure, due to changes in system I/O patterns and resource allocation.
Read performance

Read performance overhead depends on whether a decryption function is used in the query. A single decryption function call in an SQL statement can degrade performance by approximately 6%.

Index usage

Only point queries can use an index for efficient retrieval. Operations such as a range query (for example, WHERE age>20) and pattern matching (for example, LIKE '%key%') do not use an index and result in a full table scan.

Table property limitations

Encryption is not supported for tables that contain a virtual column.
Only tables that use the InnoDB storage engine are supported.
Partitioned tables are not supported.
Encrypted columns must be of the varbinary type.
The column length must be at least 44 bytes.
You cannot add a new encrypted column directly. You must modify an existing, unencrypted column to encrypt it.

Usage

Note

Column encryption and the TDE feature both use Key Management Service (KMS) for key management.

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

Permission control

To use column encryption, your database account must have the ENCRYPTION_FUN_ADMIN privilege. A privileged account can grant this privilege.

-- Grant the privilege
GRANT ENCRYPTION_FUN_ADMIN ON *.* TO 'testman'@'%';
-- View privileges
SHOW GRANTS;

DDL

Create an encrypted column

Use the ENCRYPTION keyword to specify an encrypted column.

Specify the pan column as an encrypted column.

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`)
);

Note

You can create an encrypted column with a NULL default value, but NULL values are not encrypted.

You can use the MODIFY statement to convert a regular column to an encrypted column, or vice versa.
```
ALTER TABLE my_pan MODIFY `pan` VARBINARY(128) ENCRYPTION;
ALTER TABLE my_pan MODIFY `pan` VARBINARY(128) NOT ENCRYPTION;
```
Note
You cannot add a new encrypted column directly. You must use the MODIFY statement to convert an existing regular column.

Create an index

Creating an index on an encrypted column uses the same syntax as for a regular column.

CREATE index idx_pan ON my_pan(pan);

Note

The data in the index is encrypted. The index itself is sorted based on the order of the ciphertext values.

DML

The following are the encryption and decryption functions:

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

Insert data

Data inserted into an encrypted column is automatically encrypted, so you do not need to call the enhanced_aes_encrypt function.

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

Query data

# 1. Query by decrypting column values. This does not use an index and performs a full table scan.
SELECT id,enhanced_aes_decrypt(pan) FROM `my_pan` WHERE enhanced_aes_decrypt(pan) = 'my_password_10';
# 2. Point query using an index. The result is returned as 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 result is returned as ciphertext.
SELECT id, pan FROM `my_pan` WHERE pan = enhanced_aes_encrypt('my_password_10', "mydatabase", "my_pan");

Update data

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

Delete data

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

2. Key management

Authorize PolarDB to access KMS.
Enable the TDE feature.
Important
- Enabling TDE restarts the PolarDB cluster. Proceed with caution.
- TDE cannot be disabled once enabled.
- Enabling the TDE feature itself does not cause additional performance overhead to tables that are not TDE-encrypted and do not contain encrypted columns.
1. Log in to the PolarDB console. In the left-side navigation pane, click Clusters. Select your cluster's region, and then click the cluster ID to open its details page.
2. In the left-side navigation pane, click Settings and Management > Security.
3. On the TDE Settings tab, turn on the TDE Status switch and select a custom key.
  
  Note
  Before you delete a key in KMS, you must ensure that the key is no longer associated with any encrypted tables, including tables in historical backups. Otherwise, the PolarDB cluster may fail to restart.
Select a key. You can choose to Use Default Key CMK or Use Existing Custom Key.

In the Advanced options, you can enable automatic encryption. If enabled, all new tables are automatically encrypted.
(Optional) Perform key rotation.
Note
- PolarDB does not automatically rotate custom keys. You must rotate the key manually. For more information, see Key rotation.
- After key rotation, new encrypted tables use the new key. Data in existing encrypted tables remains encrypted with the original key.
- Data rotation involves decrypting data in a table and then re-encrypting it with the latest key version. This process consumes CPU and I/O resources. Data rotation is not mandatory, but you must ensure the availability of the keys used by your tables.
- Table locking during data rotation:
  - If Online Copy is disabled, the table is locked during data rotation.
  - If Online Copy DDL is enabled, the table is not locked during data rotation. Concurrent DML operations are allowed, but concurrent DDL operations are not.
To re-encrypt data in an existing encrypted table with the new key, you must manually run the following data rotation command:
```
ALTER TABLE <table_name> engine = innodb,algorithm=copy;
```
Query table keys.

To query key information for all encrypted tables in the system, run the following SQL statement:
```
SELECT * FROM information_schema.INNODB_COLUMN_ENCRYPTED_TABLE;
```

3. Parameter settings

Enable the column encryption feature by setting the loose_polar_enable_column_encryption parameter. For more information about how to set parameters, see Set cluster and node parameters. The following table describes the parameter.

Parameter

Description

loose_polar_enable_column_encryption

Controls whether to enable column encryption. Valid values:

ON: Enables the column encryption feature.
OFF (Default): Disables the column encryption feature.

4. (Optional) Enable Online Copy

When you use the COPY DDL syntax for data rotation (for example, to change table definitions across storage engines, adjust column properties, or convert between regular and partitioned tables), the table is locked by default, which can interrupt services. To prevent this, enable the Online Copy DDL optimization feature to perform table structure changes with no or minimal locking. For more information, see the Online Copy DDL documentation.