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-compatible 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 the tenant. | |
| sensitive_field_list | String | The 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 (AES-256-ECB). You can also write USING ENCRYPTION = DEFAULT to specify the default encryption algorithm. |
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 you create a sensitive rule, note the following:
- The rule takes effect immediately after it is created.
- The rule name is globally unique within the tenant, but only for sensitive rules, not for objects. In other words, you can have both a table named
R1and a sensitive rule namedR1, but you cannot have two sensitive rules namedR1. - In Oracle-compatible mode, the rule name is stored in uppercase by default. If you need to distinguish between uppercase and lowercase names, you can enclose the name in double quotation marks. Rule names enclosed in double quotation marks are case-sensitive. For example,
R1and"r1"are considered two different rules and can both exist. - Sensitive rules provide data protection at the column level only. There is no table-level or database-level protection. In other words, 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 the column names.
- A single rule can protect one or more columns. Cross-database and cross-table protection is allowed.
When you create a sensitive rule, note the following restrictions:
- You can create data protection rules only 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';