Purpose
This statement is used to create a sensitive data protection rule.
Privilege requirements
To execute the CREATE SENSITIVE RULE statement, the current user must have the CREATE SENSITIVE RULE privilege. For more information about OceanBase Database privileges, see Privilege types in MySQL-compatible mode.
Syntax
CREATE SENSITIVE RULE <rule_name>
ON <sensitive_field_list>
USING ENCRYPTION [ = <encryption_method>];
Parameters
The parameters are described as follows:
| Parameter | Data type | Default value | Description |
|---|---|---|---|
| rule_name | String | The name of the encryption rule, which must be unique within a tenant. | |
| sensitive_field_list | String | The columns to be encrypted, in the format of db.table(col1, col2), db2.table2(col3, col4), .... Each sensitive item specifies one or more columns in a table, with columns separated by commas. Sensitive items are separated by commas, and the rule specifies the columns to be protected. A single rule can protect multiple columns across multiple users and tables. Parameter description:
|
|
| encryption_method | String | If USING ENCRYPTION is used without parameters, the default encryption algorithm is used, which is aes-256 by default. This refers to the AES-256-ECB encryption algorithm. It can also be written as USING ENCRYPTION = DEFAULT, with the same meaning. |
The allowed values for encryption_method are as follows:
| 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) |
When creating a sensitive rule, the following considerations apply:
- The rule takes effect immediately after creation.
- The rule name is globally unique within the tenant, but only for sensitive rules, not for objects. That is, a table named
R1and a sensitive rule namedR1can coexist, but two sensitive rules namedR1cannot. - Data protection is only at the column level, not at the table or database level. That is, you cannot create data protection rules for entire tables or databases. If you want to encrypt all columns of a table, you must explicitly specify all column names.
- A single rule can protect one or more columns. Cross-database and cross-table protection is allowed.
When creating a sensitive rule, the following limitations apply:
- Data protection rules can only be applied to columns of user tables (including views). They cannot be applied to columns of system tables.
- A column can only be protected by one rule.
Examples
Here are some examples:
-- Create a sensitive rule named r1 to protect columns a of tbl1, columns b and c of tbl2, and columns e and f of db2.tbl3.
-- Use the default encryption algorithm AES-256-ECB.
CREATE SENSITIVE RULE r1 ON tbl1(a), tbl2(b, c), db2.tbl3(e,f)
USING ENCRYPTION;
-- Create a sensitive rule named r2 to protect columns x and y of tbl4 and column z of tbl5.
-- Use the SM4-CBC encryption algorithm.
CREATE SENSITIVE RULE r2 ON tbl4(x, y), tbl5(z)
USING ENCRYPTION = 'sm4-cbc';