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 Oracle 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, which must be unique within a tenant. | |
| sensitive_field_list | String | The list of columns to be encrypted, in the format of user.table(col1, col2), user2.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 specified without parameters, the default encryption algorithm is used, which is aes-256, corresponding to the AES-256-ECB encryption algorithm. You can also specify USING ENCRYPTION = DEFAULT to achieve the same effect. |
The following table lists the allowed 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) |
When creating a sensitive rule, note the following:
- The rule takes effect immediately after it is created.
- The rule name is globally unique within a tenant, but only for sensitive rules, not for objects. In other words, a table and a sensitive rule can have the same name, such as
R1, but two sensitive rules cannot have the same name. - In Oracle mode, rule names are stored in uppercase by default. If you need to distinguish between uppercase and lowercase, you can enclose the rule name in double quotation marks. Rule names enclosed in double quotation marks are case-sensitive. For example,
R1and"r1"are considered different rules and can coexist. - Sensitive rules provide data protection at the column level, not at the table or database level. Therefore, you cannot create data protection rules for an entire table or database. If you want to encrypt all columns in 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, note the following limitations:
- You can only create data protection rules for columns in user tables (including views). You cannot create data protection rules for 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 columns a in tbl1, columns b and c in tbl2, and columns e and f in user2.tbl3.
-- Use the default encryption algorithm, AES-256-ECB.
CREATE SENSITIVE RULE r1 ON tbl1(a), tbl2(b, c), user2.tbl3(e,f)
USING ENCRYPTION;
-- Create a sensitive rule named r2 to protect columns x and y in tbl4, and column z in tbl5.
-- Use the SM4-CBC encryption algorithm.
CREATE SENSITIVE RULE r2 ON tbl4(x, y), tbl5(z)
USING ENCRYPTION = 'sm4-cbc';