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 classification in MySQL mode.
Syntax
CREATE SENSITIVE RULE rule_name
ON sensitive_field_list
USING ENCRYPTION [ = { DEFAULT | encryption_method } ];
Parameters
The following table describes the parameters:
| Parameter | Data type | Default value | Description |
|---|---|---|---|
| rule_name | String | The name of the encryption rule, which must be unique within the 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. This parameter specifies the columns to be protected by the sensitive rule. 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. You can also write USING ENCRYPTION = DEFAULT to achieve the same effect. |
The allowed values of 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, note the following:
- The rule takes effect immediately after it is created.
- The rule name is globally unique at the tenant level, but only for sensitive rules, not at the object level. In other words, you can have a table named
R1and a sensitive rule namedR1at the same time, but you cannot have two sensitive rules namedR1. - Sensitive rules provide data protection only at the column level, not at the table or database level. In other words, you cannot create data protection rules for entire tables or databases. If you want to encrypt all columns in a table, you must explicitly specify all the column names.
- A single rule can protect one or more columns. Cross-database and cross-table protection is allowed.
When creating a sensitive rule, note the following limitations:
- You can apply data protection rules only to columns in user tables (including views). You cannot apply data protection rules to columns in system tables.
- A column can be protected by only one rule.
Examples
Here are some examples:
-- Create a sensitive rule named r1 to protect tbl1(a), tbl2(b, c), and db2.tbl3(e,f).
-- 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 tbl4(x, y) and tbl5(z).
-- Use the SM4-CBC encryption algorithm.
CREATE SENSITIVE RULE r2 ON tbl4(x, y), tbl5(z)
USING ENCRYPTION = 'sm4-cbc';