Purpose
This statement is used to create sensitivity data protection rules.
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, you 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 following table lists the parameters.
| Parameter | Data type | Default value | Description | |--|--|--|--| | rule_name | character | | The name of the encryption rule, which must be unique within the tenant. | | sensitive_field_list | character | | Specifies the columns to be encrypted, in the format db.table(col1, col2), db2.table2(col3, col4), .... Each sensitive item specifies one or more columns in a table, separated by commas. Multiple sensitive items are separated by commas. db can be omitted, in which case the current connected database is used by default. | | encryption_method | character | | USING ENCRYPTION can be used without parameters, indicating the use of the default encryption algorithm, which is aes-256. It can also be written as USING ENCRYPTION = DEFAULT, which means the same thing. |
The allowed values for encryption_method are as follows.
| Value (in SQL syntax) | Encryption algorithm | Key length | Mode | Description |
|---|---|---|---|---|
| aes-256 | AES-256-ECB | 256-bit | ECB | Default algorithm |
| aes-128 | AES-128-ECB | 128-bit | ECB | Good compatibility |
| aes-192 | AES-192-ECB | 192-bit | ECB | Medium security level |
| aes-128-gcm | AES-128-GCM | 128-bit | GCM | Authenticated encryption |
| aes-256-gcm | AES-256-GCM | 256 | GCM | High security level, offers authenticated encryption |
| sm4-cbc | SM4-CBC | CBC | High security level | |
| sm4-GCM | SM4-GCM | GCM | High security level |
Limitations:
- Data protection rules can only be applied to columns of user tables (including views). They cannot be applied to columns of system tables.
- Rule names must be globally unique within a tenant.
- A single rule can specify the protection of one or more columns, and it is allowed to span multiple databases and tables.
- Each column can only be protected by a single rule.
Example
Here is an example:
-- Create sensitive rule r1 to protect tbl1(a), tbl2(b, c), db2.tbl3(e, f).
-- Use the default encryption algorithm.
CREATE SENSITIVE RULE r1 ON tbl1(a), tbl2(b, c), db2.tbl3(e,f)
USING ENCRYPTION;
-- Create sensitive rule r2 to protect tbl4(x, y), tbl5(z).
-- Use the sm4-cbc encryption algorithm.
CREATE SENSITIVE RULE r2 ON tbl4(x, y), tbl5(z)
USING ENCRYPTION = 'sm4-cbc';