Manage column encryption privileges

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 OceanBase Database 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 TDE encryption 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 waiting 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 is based on rule-based access control (RBAC). Access to sensitive columns can be controlled by using data protection rules. To support column encryption, OceanBase Database has introduced the following new types of permissions in the MySQL access control model: user-level permissions and rule-level permissions.

User-level permissions

Rule-level sensitive privileges

Permission Management

Syntax and examples for granting privileges

1. Grant user-level privileges

   The syntax is as follows:

   -- Grant the privilege to create sensitive rules to a user or role. This privilege is user-level.
   GRANT CREATE SENSITIVE RULE ON *.* TO <user_or_role_list>;
   
   -- Grant plain-text access privileges to a user or role at the user level.
   GRANT PLAINACCESS ON *.* TO  <user_or_role_list>;
   

   The syntax and parameters are described as follows:

   Syntax/Parameter	Description	Example
   user_or_role_list	The list of users or roles. Separate multiple users with commas. Example: u1, u2, r1
   *.*	The fixed syntax for granting user-level (global) privileges. It indicates all databases and tables in the global scope.

   Here are some examples:

   -- Grant the privilege to create sensitive rules to user u1.
   GRANT CREATE SENSITIVE RULE ON *.* TO u1;
   
   -- You can grant privileges to multiple users or roles at the same time.
   -- Grant the privilege to create sensitive rules to user u2 and role r1.
   GRANT CREATE SENSITIVE RULE ON *.* TO u2, r1;
   

2. Grant rule-level privileges

   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 plain-text access privileges to user u1 at the user level. 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 plain-text access privileges to user u1 for rule r1.
   -- You can also grant privileges to multiple users or roles at the same time for a rule. However, you can only grant privileges for one rule at a time.
   GRANT PLAINACCESS ON SENSITIVE RULE r1 TO u1;
   

Syntax and examples for revoking privileges

1. Revoke user-level privileges

   The syntax is as follows:

   -- Revoke the privilege to create sensitive rules from a user or role. This privilege is user-level.
   REVOKE CREATE SENSITIVE RULE ON *.* FROM <user_or_role_list>;
   
   -- Revoke plain-text access privileges from a user or role at the user level.
   REVOKE PLAINACCESS ON *.* FROM <user_or_role_list>;
   

   The syntax and parameters are described as follows:

   Syntax/Parameter	Description	Example
   user_or_role_list	The list of users or roles. Separate multiple users with commas. Example: u1, u2, r1
   *.*	The fixed syntax for revoking user-level (global) privileges. It indicates all databases and tables in the global scope.

   Here are some examples:

   -- Revoke the privilege to create sensitive rules from user u1.
   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 user u2 and role r1.
   REVOKE CREATE SENSITIVE RULE ON *.* FROM u2, r1;
   

2. Revoke rule-level privileges

   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 plain-text access privileges from user u1 at the user level. You can also revoke privileges from multiple users or roles at the same time.
   REVOKE PLAINACCESS ON *.* FROM u1;
   
   -- Revoke plain-text access privileges from user u1 for rule r1.
   -- You can also revoke privileges from multiple users or roles at the same time for a rule. However, you can only revoke privileges for one rule at a time.
   REVOKE PLAINACCESS ON SENSITIVE RULE r1 FROM u1;
   

View permissions

1. View sensitive rules

   You can view all sensitive data protection rules in the current tenant or all sensitive rules associated with a specified table or database. You can use the LIKE clause to filter the rule names.

   Syntax:

   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:

   Parameter	Description	Example
   opt_show_condition	Optional display condition, such as the LIKE clause	LIKE '%rule1%'
   relation_factor	Table name	tb1, employees
   database_factor	Database name	test, hr_db

   Example: The tb1 table is in the test database.

   -- View all sensitive rules in the current tenant.
   SHOW SENSITIVE RULES;
   
   -- View all data protection rules associated with the tb1 table.
   SHOW SENSITIVE RULES FROM tb1;
   
   -- View all data protection rules associated with the test.tb1 table.
   SHOW SENSITIVE RULES FROM tb1 FROM test;
   
   -- View all data protection rules associated with the test database.
   SHOW SENSITIVE RULES FROM DATABASE test;
   
   -- Filter the 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 result set is similar to the following example:

   Column	rule_id	rule_name	POLICY	METHOD	ENABLED	PROTECT_COLS
   Example	1	'ENCRYPT_COL'	'ENCRYPTION'	'AES-256-ECB'	'YES'	'db1.tbl1(col_1, col_2), db2.tbl2(col_3), db3.tbl4(col_4, col_5)'

   Field description:

   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 database.table(columns) format.	'db1.tbl1(col_1, col_2)'. Columns of different tables are separated with commas.

2. View user permissions

   Syntax:

   
   -- View all permissions of a specified user.
   SHOW GRANTS FOR <user_or_role_list>;
   
   -- View the permissions of the current user.
   SHOW GRANTS;
   

   Example:

   -- View all permissions of user u1.
   SHOW GRANTS FOR u1;
   

   The result set is similar to the following example:

   +-----------------------------------------------------------+
   | 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

• Overview of column encryption
• Create a column encryption rule
• For more information about the definition and attributes of a sensitive level rule, see DBA/CDB_OB_SENSITIVE_RULES.