After you configure column encryption rules, proper privilege management is key to ensuring data security. This topic describes the privilege system and management syntax of the column encryption feature of OceanBase AI Database, helping you establish a comprehensive privilege control mechanism to ensure that sensitive data is visible only to authorized users.
Prerequisites
Before you use sensitive rules, enable Transparent Data Encryption (TDE) and create a master key:
-- Set the TDE method for transparent tablespace encryption. For valid values, see tde_method (for example, internal, obcloud, and ocp).
ALTER SYSTEM SET tde_method = '<encryption_method>';
ALTER INSTANCE ROTATE INNODB MASTER KEY; -- The first execution requires about 20 seconds for the master key to take effect.
For more information about the tde_method configuration item, see tde_method.
Privilege types
The column encryption feature uses rule-based privilege types to control user access to sensitive columns through data protection rules. OceanBase AI Database has introduced dedicated privilege types in its privilege system to support column encryption, including user-level privileges and sensitive rule-level privileges.
User-level privileges
Permission name |
Description |
Default owner |
Upgrade handling |
Privilege restrictions |
Feature scope |
Inclusion relationship |
|---|---|---|---|---|---|---|
| CREATE SENSITIVE RULE | Allows a user to create and drop sensitive rules. | The root user. |
Automatically assigned during a system upgrade. | The privileges of the root user cannot be revoked by using REVOKE. |
You can use the CREATE/DROP SENSITIVE RULE syntax. |
Included in ALL PRIVILEGES. |
| PLAINACCESS | Allows a user to access all plaintext data without being affected by data protection rules. | The root user. |
Automatically assigned during a system upgrade. | The privileges of the root user cannot be revoked by using REVOKE. |
Grants access to all plaintext data, bypassing any data protection rules. | Not included in ALL PRIVILEGES. |
Sensitive rule-level privileges
Permission name |
Description |
Privilege scope |
Automatic authorization |
|---|---|---|---|
| PLAINACCESS | Allows a user to access the plaintext data of columns associated with a specific rule. | Users who have the PLAINACCESS privilege on a rule can access the plaintext data of the columns associated with the rule. |
The user who creates the rule does not automatically have the PLAINACCESS privilege on the rule. |
Privilege management
Syntax and examples for granting privileges
Grant user-level privileges
The syntax is as follows:
-- Grant the privilege to create sensitive rules to users or roles. This privilege is at the user level only. GRANT CREATE SENSITIVE RULE ON *.* TO <user_or_role_list>; -- Grant the user-level plaintext access privilege to users or roles. GRANT PLAINACCESS ON *.* TO <user_or_role_list>;The syntax and parameters are described as follows:
Syntax/ParameterDescriptionExampleuser_or_role_listThe list of users or roles. Separate multiple users with commas.
Example:u1, u2, r1*.*The fixed syntax for granting user-level (global) privileges, indicating all databases and tables globally. Here is an example:
-- Grant the privilege to create sensitive rules to the user u1. GRANT CREATE SENSITIVE RULE ON *.* TO u1; -- You can grant privileges to multiple users or roles at once. -- Grant the privilege to create sensitive rules to the user u2 and the role r1. GRANT CREATE SENSITIVE RULE ON *.* TO u2, r1;Grant rule-level privileges
The syntax is as follows:
-- Rule-level authorization. Note that the SENSITIVE RULE keyword is required. GRANT PLAINACCESS ON SENSITIVE RULE <rule_name> TO <user_or_role_list>;The parameters are described as follows:
ParameterDescriptionExamplerule_nameThe name of the sensitive rule. 'salary_encryption','credit_card_rule'user_or_role_listThe list of users or roles. Separate multiple users with commas. u1, u2, r1Here is an example:
-- Grant the user-level plaintext access privilege to the user u1. You can also grant privileges to multiple users or roles at the same time. GRANT PLAINACCESS ON *.* TO u1; REVOKE PLAINACCESS ON *.* FROM u1; -- Grant plaintext access privilege on rule r1 to the user u1. -- You can also grant privileges to multiple users or roles at the same time (but only one privilege on one rule at a time). GRANT PLAINACCESS ON SENSITIVE RULE r1 TO u1;
Syntax and examples for revoking privileges
Revoke user-level privileges
The syntax is as follows:
-- Revoke the privilege to create sensitive rules from users or roles. This privilege is at the user level only. REVOKE CREATE SENSITIVE RULE ON *.* FROM <user_or_role_list>; -- Revoke the user-level plaintext access privilege from users or roles. REVOKE PLAINACCESS ON *.* FROM <user_or_role_list>;The syntax and parameters are described as follows:
Syntax/ParameterDescriptionExampleuser_or_role_listThe list of users or roles. Separate multiple users with commas.
Example:u1, u2, r1*.*The fixed syntax for revoking user-level (global) privileges, indicating all databases and tables in the global scope. Here is an example:
-- Revoke the privilege. REVOKE CREATE SENSITIVE RULE ON *.* FROM u1; -- You can revoke privileges from multiple users or roles at the same time. -- Revoke the privilege to create sensitive rules from the user u2 and the role r1. REVOKE CREATE SENSITIVE RULE ON *.* FROM u2, r1;Revoke rule-level privileges
The syntax is as follows:
-- Privilege revocation at the rule level. Note that the SENSITIVE RULE keyword is required. REVOKE PLAINACCESS ON SENSITIVE RULE <rule_name> FROM <user_or_role_list>;The parameters are described as follows:
ParameterDescriptionExamplerule_nameThe name of the sensitive rule. 'salary_encryption','credit_card_rule'user_or_role_listThe list of users or roles. Separate multiple users with commas. u1, u2, r1Here is an example:
-- Revoke the user-level plaintext access privilege from the user u1. You can also revoke privileges from multiple users or roles at the same time. REVOKE PLAINACCESS ON *.* FROM u1; -- Revoke the plaintext access privilege on rule r1 from the user u1. -- You can also revoke privileges from multiple users or roles at the same time (but you can revoke only one privilege on one rule at a time). REVOKE PLAINACCESS ON SENSITIVE RULE r1 FROM u1;
View privileges
View sensitive rules
You can view all sensitive data protection rules under the current tenant, or view all sensitive rules associated with a specified table or database. You can use the
LIKEclause to filter rule names.The syntax is as follows:
SHOW SENSITIVE RULES opt_show_condition | SHOW SENSITIVE RULES from_or_in relation_factor opt_from_or_in_database_clause opt_show_condition | SHOW SENSITIVE RULES from_or_in DATABASE from_or_in database_factor opt_show_condition;Parameter description:
ParameterDescriptionExampleopt_show_conditionOptional display condition, such as a LIKEclauseLIKE '%rule1%'relation_factorTable name tb1,employeesdatabase_factorDatabase name test,hr_dbHere is an example where the
tb1table is in thetestdatabase:-- View all sensitive rules of 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 test.tb1 SHOW SENSITIVE RULES FROM tb1 FROM test; -- View all data protection rules associated with the test database. SHOW SENSITIVE RULES FROM DATABASE test; -- Filter rule names by using the LIKE clause SHOW SENSITIVE RULES LIKE '%rule1%'; SHOW SENSITIVE RULES FROM tb1 LIKE '%rule1%'; SHOW SENSITIVE RULES FROM tb1 FROM test LIKE '%rule1%'; SHOW SENSITIVE RULES FROM DATABASE test LIKE '%rule1%';The return result format is as follows:
ColumnRULE_NAMEPROTECTION_POLICYMETHODENABLEDPROTECTED_COLUMNSExample 'ENCRYPT_COL' 'ENCRYPTION' 'AES-256-ECB' 'YES' 'db1.tbl1(col_1, col_2), db2.tbl2(col_3), db3.tbl4(col_4, col_5)' The fields in the return result are described as follows:
FieldDescriptionExample valuerule_nameRule name 'ENCRYPT_COL'PROTECTION_POLICYPolicy type 'ENCRYPTION'METHODEncryption method 'AES-256-ECB'ENABLEDEnabled or not 'YES','NO'PROTECTED_COLUMNSProtected columns, in the format database.table(columns)'db1.tbl1(col_1, col_2)', separated by commas for different tables.View user privileges
The syntax is as follows:
-- View all privileges of a specified user. SHOW GRANTS FOR <user_or_role_list>; -- View the privileges of the current user. SHOW GRANTS;Here is an example:
-- View all privileges of user u1 SHOW GRANTS FOR u1;A sample query result is as follows:
+-----------------------------------------------------------+ | Grants for u1@% | +-----------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO u1 WITH GRANT OPTION | | GRANT CREATE SENSITIVE RULE ON *.* TO u1; | | GRANT PLAINACCESS ON *.* TO u1; | | GRANT PLAINACCESS ON `enc_rule1` TO u1; | | GRANT PLAINACCESS ON `enc_rule2` TO u1; | +-----------------------------------------------------------+
References
- Column encryption overview
- Create a column encryption rule
- For more information about the definitions and attributes of sensitive rules, see DBA/CDB_OB_SENSITIVE_RULES