Purpose
This statement is used to create a sensitive data protection rule.
Note
For OceanBase Database V4.3.5, this statement is supported starting from V4.3.5 BP3.
Privilege requirements
To execute the CREATE SENSITIVE RULE statement, the current user must have the CREATE SENSITIVE RULE privilege. For more information about the privileges of OceanBase Database, see Privilege classification in MySQL mode.
Syntax
CREATE SENSITIVE RULE <rule_name>
ON <sensitive_field_list>
USING ENCRYPTION [ = <encryption_method>]
;
Parameters
The following table describes the parameters.
| Parameter | Data type | Default value | Description |
|---|---|---|---|
| rule_name | String | The name of the encryption rule. It must be unique within a tenant. | |
| sensitive_field_list | String | The list of columns to be encrypted. The format is db.table(col1, col2), db2.table2(col3, col4), .... Each sensitive item specifies one or more columns in a table, and columns are separated by commas. Sensitive items are separated by commas. If db is not specified, the current database is used. |
|
| encryption_method | String | The encryption method. If USING ENCRYPTION is specified without parameters, the default encryption algorithm is used, which is aes-256. You can also specify USING ENCRYPTION = DEFAULT to indicate the default encryption algorithm. |
The following table describes the supported values of encryption_method.
| Value (used in SQL syntax) | Actual encryption algorithm | Key length | Mode | Description |
|---|---|---|---|---|
| aes-256 | AES-256-ECB | 256 bits | ECB | Default algorithm |
| aes-128 | AES-128-ECB | 128 bits | ECB | |
| aes-192 | AES-192-ECB | 192 bits | ECB | |
| aes-128-gcm | AES-128-GCM | 128 bits | GCM | High security level. Provides authenticated encryption (AEAD). |
| aes-192-gcm | AES-192-GCM | 192 bits | GCM | High security level. Provides authenticated encryption (AEAD). |
| aes-256-gcm | AES-256-GCM | 256 bits | GCM | High security level. Provides authenticated encryption (AEAD). |
| sm4-cbc | SM4-CBC | CBC | High security level | |
| sm4-GCM | SM4-GCM | GCM | High security level. Provides authenticated encryption (AEAD). |
Constraints:
- You can create a data protection rule only for columns in user tables (including views). You cannot create a data protection rule for columns in system tables.
- The name of a rule must be unique within a tenant.
- A rule can protect one or more columns. Cross-database and cross-table protection is allowed.
- A column can be protected by only one rule.
Examples
Here are some examples:
-- Create a sensitive rule named r1 to protect the columns a of tbl1, b and c of tbl2, and e and f of db2.tbl3.
-- Use the default encryption algorithm.
CREATE SENSITIVE RULE r1 ON tbl1(a), tbl2(b, c), db2.tbl3(e,f)
USING ENCRYPTION;
-- Create a sensitive rule named r2 to protect the columns x and y of tbl4 and z of tbl5.
-- Use the SM4-CBC encryption algorithm.
CREATE SENSITIVE RULE r2 ON tbl4(x, y), tbl5(z)
USING ENCRYPTION = 'sm4-cbc';