After configuring column encryption rules, effective privilege management is crucial for ensuring data security. This document provides a detailed overview of the privilege system and management syntax for OceanBase column encryption, helping you establish a comprehensive privilege control mechanism to ensure that sensitive data is only visible to authorized users.
Prerequisites
Before you use sensitive rules, enable Transparent Data Encryption (TDE) 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 will wait about 20 seconds for the key to take effect.
For more information about the tde_method parameter, see tde_method.
Privilege types
The column encryption feature uses rule-based privilege types to control user access to sensitive columns based on data protection rules. OceanBase Database has added specific privilege types to the Oracle privilege system to support column encryption, including system privileges and sensitive rule-level privileges.
System privileges
| Privilege name | Description | Default owner | Upgrade handling | Limitations | Applicable scope | Inclusion relationships |
|---|---|---|---|---|---|---|
| CREATE SENSITIVE RULE | This privilege enables the user to create and drop sensitive rules. | SYS user. | Automatically granted during system upgrades. | The SYS user privilege cannot be revoked. | You can use the CREATE/DROP SENSITIVE RULE syntax. |
This privilege is included in ALL PRIVILEGES. |
| PLAINACCESS | Allow user access to all plain-text data without restrictions from data protection rules. | SYS user. | Granted automatically during system upgrades. | Cannot be revoked for SYS users. | Access to all plain-text data is not restricted by any data protection rules. | Not included in ALL PRIVILEGES. |
Rule-level privileges
| Privilege name | Description | Scope | Automatically granted |
|---|---|---|---|
| PLAINACCESS | Allows users to access the plaintext data of the columns associated with a rule. | A user who has the PLAINACCESS privilege on a rule can access the plaintext data of the columns associated with that rule. |
The owner of a rule does not automatically have the PLAINACCESS privilege on that rule. |
Privilege management
Granting and revoking privileges
The syntax is as follows:
-- Grant the privilege to create sensitive rules to a user or role. This privilege is at the system level.
GRANT CREATE SENSITIVE RULE TO <user_or_role_list>;
-- Grant plaintext access privileges 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 |
The list of users or roles. Separate multiple users with commas. | u1, u2, r1 |
Here are some examples:
-- Grant the privilege to create sensitive rules to user u1.
GRANT CREATE SENSITIVE RULE TO u1;
-- You can grant privileges to multiple users or roles at a time.
-- Grant the privilege to create sensitive rules to user u2 and role r1.
GRANT CREATE SENSITIVE RULE TO u2, r1;
The syntax is as follows:
-- Grant 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 |
The list of users or roles. Separate multiple users with commas. | u1, u2, r1 |
Here are some examples:
-- Grant plaintext access privileges at the system level to user u1. You can also grant privileges to multiple users or roles at a time.
GRANT PLAINACCESS ANY SENSITIVE RULE TO u1;
-- Grant plaintext access privileges to user u1 for rule r1.
-- You can also grant privileges to multiple users or roles at a time. However, you can only grant privileges for one rule at a time.
GRANT PLAINACCESS ON SENSITIVE RULE r1 TO u1;
Note that you cannot grant the PLAINACCESS privilege at the table or column level. Using the following syntax will result in an error:
-- Incorrect example
GRANT PLAINACCESS ON <database>.<table> (<col_list>) TO <user_or_role_list>;
Revoking privileges
The syntax is as follows:
-- Revoke the privilege to create sensitive rules from a user or role. This privilege is at the system level.
REVOKE CREATE SENSITIVE RULE FROM <user_or_role_list>;
-- Revoke plaintext access privileges 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 |
The list of users or roles. Separate multiple users with commas. | u1, u2, r1 |
Here are some examples:
-- Revoke the privilege to create sensitive rules from user u1.
REVOKE CREATE SENSITIVE RULE FROM u1;
-- You can revoke privileges from multiple users or roles at a time.
-- Revoke the privilege to create sensitive rules from user u2 and role r1.
REVOKE CREATE SENSITIVE RULE FROM u2, r1;
The syntax is as follows:
-- Revoke 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 |
The list of users or roles. Separate multiple users with commas. | u1, u2, r1 |
Here are some examples:
-- Revoke plaintext access privileges at the system level from user u1. You can also revoke privileges from multiple users or roles at a time.
REVOKE PLAINACCESS ANY SENSITIVE RULE FROM u1;
-- Revoke plaintext access privileges from user u1 for rule r1.
-- You can also revoke privileges from multiple users or roles at a time. However, you can only revoke privileges for 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. Using the following syntax will result in an error:
-- Incorrect example
REVOKE PLAINACCESS ON <database>.<table> (<col_list>) FROM <user_or_role_list>;
View privileges
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 use the LIKE clause to filter the names of rules.
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 clause | FROM or IN |
relation_factor |
Table name | tb1, employees |
user_factor |
Username | u1, u2 |
Here are some 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 names of rules by 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%';
Here is an example of the return 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 descriptions:
| Field | Description | Example value |
|---|---|---|
rule_id |
The rule ID, which is automatically generated by the system. | 1, 2 |
rule_name |
The rule name. | 'ENCRYPT_COL' |
POLICY |
The policy type. | 'ENCRYPTION' |
METHOD |
The encryption method. | 'AES-256-ECB' |
ENABLED |
Indicates whether the rule is enabled. | 'YES', 'NO' |
PROTECT_COLS |
The protected columns, in the user.table(columns) format. |
'user1.tbl1(col_1, col_2)'. Columns of different tables are separated with commas. |
In Oracle-compatible mode, you cannot use the SHOW GRANTS statement to view privilege information. Instead, you can query the following views:
| View | Description |
|---|---|
| DBA_OB_SENSITIVE_RULE_PLAINACCESS_USERS | View all users with plaintext access privilege. |
ALL_OBJECTS, DBA_OBJECTS, and USER_OBJECTS |
View the protected objects of sensitive rules. |
DBA_TAB_PRIVS, DBA_SYS_PRIVS, ALL_TAB_PRIVS, USER_TAB_PRIVS, and ROLE_TAB_PRIVS |
View the granted rule-level plaintext access (PLAINACCESS) privilege. |
USER_SYS_PRIVS and ROLE_SYS_PRIVS |
View the granted system-level CREATE SENSITIVE RULE and PLAINACCESS privilege. |