After you configure column encryption rules, proper permission management is crucial for ensuring data security. This topic will detail the permission system and management syntax for column encryption in OceanBase Database, helping you establish a robust permission control mechanism to ensure that sensitive data is only accessible to authorized users.
Prerequisites
Before you use sensitive rules, enable the Transparent Data Encryption (TDE) feature and create a master key:
-- Set the TDE method to internal or obcloud.
ALTER SYSTEM SET tde_method = '<encryption_method>';
ALTER INSTANCE ROTATE INNODB MASTER KEY; -- The first execution requires about 20 seconds for the key to take effect.
For more information about the tde_method parameter, see tde_method.
Permission Types
The column encryption feature uses rule-based permission types. It controls user access to sensitive columns by data protection rules. OceanBase Database adds dedicated permission types to the Oracle permission model to support column encryption, including system privileges and sensitive rule-level privileges.
System permissions
| Permission Name | Description | Default Owner | Upgrade Handling | Permission Restrictions | Scope of Functionality | Containment Relationship |
|---|---|---|---|---|---|---|
| CREATE SENSITIVE RULE | Allows the user to create and drop sensitive rules. | SYS user. | Automatically granted at system upgrade. | Cannot be revoked for SYS users. | Supported by CREATE/DROP SENSITIVE RULE statement. |
Included in ALL PRIVILEGES. |
| PLAINACCESS | Allows users to access all plain text data without being affected by data protection rules. | SYS users. | Automatically granted during system upgrades. | Cannot be revoked from SYS users. | Can access all plain text data and is not subject to any data protection rules. | Not included in ALL PRIVILEGES. |
Rule-level permissions
| Permission name | Description | Scope | Automatically assigned |
|---|---|---|---|
| PLAINACCESS | Allows a user to access the plaintext data for the columns associated with a specific rule. | A user with the PLAINACCESS permission on a rule can access the plaintext data for the columns associated with that rule. |
The creator of a rule does not automatically have the PLAINACCESS permission on that rule. |
Permission Management
Syntax and examples of granting privileges
The syntax is as follows:
-- Grants the CREATE SENSITIVE RULE privilege to a user or role. This privilege is at the system level.
GRANT CREATE SENSITIVE RULE TO <user_or_role_list>;
-- Grants the PLAINACCESS privilege at the system level to a user or role.
GRANT PLAINACCESS ANY SENSITIVE RULE TO <user_or_role_list>;
The parameters are described as follows:
| Parameter | Description | Example |
|---|---|---|
user_or_role_list |
A list of users or roles. Separate multiple users with commas. | u1, u2, r1 |
Here are some examples:
-- Grants the CREATE SENSITIVE RULE privilege at the system level to user u1.
GRANT CREATE SENSITIVE RULE TO u1;
-- You can grant privileges to multiple users or roles at a time.
-- Grants the CREATE SENSITIVE RULE privilege at the system level to user u2 and role r1.
GRANT CREATE SENSITIVE RULE TO u2, r1;
The syntax is as follows:
-- Grants rule-level privileges. Note that you must use the SENSITIVE RULE keyword.
GRANT PLAINACCESS ON SENSITIVE RULE <rule_name> TO <user_or_role_list>;
The parameters are described as follows:
| Parameter | Description | Example |
|---|---|---|
rule_name |
The name of the sensitive rule. | 'salary_encryption', 'credit_card_rule' |
user_or_role_list |
A list of users or roles. Separate multiple users with commas. | u1, u2, r1 |
Here are some examples:
-- Grants the PLAINACCESS privilege at the system level to user u1. You can also grant the privilege to multiple users or roles at a time.
GRANT PLAINACCESS ANY SENSITIVE RULE TO u1;
-- Grants the PLAINACCESS privilege to user u1 for rule r1.
-- You can also grant the privilege to multiple users or roles at a time. However, you can grant the privilege to only one rule at a time.
GRANT PLAINACCESS ON SENSITIVE RULE r1 TO u1;
Note that you cannot revoke the PLAINACCESS privilege at the table or column level. The following syntax will result in an error:
-- Incorrect example
GRANT PLAINACCESS ON <database>.<table> (<col_list>) TO <user_or_role_list>;
Syntax and examples of revoking privileges
The syntax is as follows:
-- Revokes the CREATE SENSITIVE RULE privilege from a user or role. This privilege is at the system level.
REVOKE CREATE SENSITIVE RULE FROM <user_or_role_list>;
-- Revokes the PLAINACCESS privilege at the system level from a user or role.
REVOKE PLAINACCESS ANY SENSITIVE RULE FROM <user_or_role_list>;
The parameters are described as follows:
| Parameter | Description | Example |
|---|---|---|
user_or_role_list |
A list of users or roles. Separate multiple users with commas. | u1, u2, r1 |
Here are some examples:
-- Revokes the CREATE SENSITIVE RULE privilege from user u1.
REVOKE CREATE SENSITIVE RULE FROM u1;
-- You can revoke privileges from multiple users or roles at a time.
-- Revokes the CREATE SENSITIVE RULE privilege from user u2 and role r1.
REVOKE CREATE SENSITIVE RULE FROM u2, r1;
The syntax is as follows:
-- Revokes rule-level privileges. Note that you must use the SENSITIVE RULE keyword.
REVOKE PLAINACCESS ON SENSITIVE RULE <rule_name> FROM <user_or_role_list>;
The parameters are described as follows:
| Parameter | Description | Example |
|---|---|---|
rule_name |
The name of the sensitive rule. | 'salary_encryption', 'credit_card_rule' |
user_or_role_list |
A list of users or roles. Separate multiple users with commas. | u1, u2, r1 |
Here are some examples:
-- Revokes the PLAINACCESS privilege at the system level from user u1. You can also revoke the privilege from multiple users or roles at a time.
REVOKE PLAINACCESS ANY SENSITIVE RULE FROM u1;
-- Revokes the PLAINACCESS privilege from user u1 for rule r1.
-- You can also revoke the privilege from multiple users or roles at a time. However, you can revoke the privilege from only one rule at a time.
REVOKE PLAINACCESS ON SENSITIVE RULE r1 FROM u1;
Note that you cannot revoke the PLAINACCESS privilege at the table or column level. The following syntax will result in an error:
-- Incorrect example
REVOKE PLAINACCESS ON <database>.<table> (<col_list>) FROM <user_or_role_list>;
View permissions
You can view all sensitive data protection rules in the current tenant or all sensitive rules associated with a specified table or user. You can also filter the rules by name using the LIKE clause.
Syntax:
SHOW SENSITIVE RULES opt_show_condition
| SHOW SENSITIVE RULES from_or_in relation_factor from_or_in USER user_factor opt_show_condition
| SHOW SENSITIVE RULES from_or_in USER user_factor opt_show_condition;
Parameters:
| Parameter | Description | Example |
|---|---|---|
opt_show_condition |
Optional display condition, such as the LIKE clause. |
LIKE '%rule1%' |
from_or_in |
Optional FROM or IN clause. |
FROM or IN |
relation_factor |
Table name. | tb1, employees |
user_factor |
Username. | u1, u2 |
Examples:
-- View all sensitive rules in the current tenant.
SHOW SENSITIVE RULES;
-- View all data protection rules associated with tb1.
SHOW SENSITIVE RULES FROM tb1;
-- View all data protection rules associated with u1.tb1.
SHOW SENSITIVE RULES FROM tb1 FROM u1;
-- View all data protection rules associated with u1.
SHOW SENSITIVE RULES FROM USER u1;
-- Filter the rules by name using the LIKE clause.
SHOW SENSITIVE RULES LIKE '%rule1%';
SHOW SENSITIVE RULES FROM tb1 LIKE '%rule1%';
SHOW SENSITIVE RULES FROM tb1 FROM u1 LIKE '%rule1%';
SHOW SENSITIVE RULES FROM USER u1 LIKE '%rule1%';
Example of returned result:
| Column | rule_id | rule_name | POLICY | METHOD | ENABLED | PROTECT_COLS |
|---|---|---|---|---|---|---|
| Example | 1 | 'ENCRYPT_COL' | 'ENCRYPTION' | 'AES-256-ECB' | 'YES' | 'user1.tbl1(col_1, col_2), user2.tbl2(col_3), user3.tbl4(col_4, col_5)' |
Field description:
| Field | Description | Example value |
|---|---|---|
rule_id |
Rule ID, which is automatically generated by the system. | 1, 2 |
rule_name |
Rule name. | 'ENCRYPT_COL' |
POLICY |
Policy type. | 'ENCRYPTION' |
METHOD |
Encryption method. | 'AES-256-ECB' |
ENABLED |
Whether the rule is enabled. | 'YES', 'NO' |
PROTECT_COLS |
Columns to be protected, in the user.table(columns) format. |
'user1.tbl1(col_1, col_2)'. Columns of different tables are separated by commas. |
In Oracle mode, you cannot use the SHOW GRANTS statement to view permission information. Instead, you can use the following views:
| View | Description |
|---|---|
| DBA_OB_SENSITIVE_RULE_PLAINACCESS_USERS | View all users with plaintext access permissions. |
ALL_OBJECTS, DBA_OBJECTS, USER_OBJECTS |
View the objects protected by sensitive rules. |
DBA_TAB_PRIVS, DBA_SYS_PRIVS, ALL_TAB_PRIVS, USER_TAB_PRIVS, ROLE_TAB_PRIVS |
View the granted plaintext access (PLAINACCESS) permissions at the rule level. |
USER_SYS_PRIVS, ROLE_SYS_PRIVS |
View the granted system-level create sensitive rule (CREATE SENSITIVE RULE) permissions and plaintext access (PLAINACCESS) permissions. |