Column encryption privilege management

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 column encryption in OceanBase Database. It will help you establish a comprehensive privilege control mechanism to ensure that sensitive data is only visible to authorized users.

Prerequisites

Before using sensitive rules, you must 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 requires 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. OceanBase Database added dedicated column encryption privileges to the MySQL privilege system. The column encryption feature includes two types of privileges: user-level privileges and column encryption rule-level privileges.

User-level privileges

Rule-level privilege for sensitive rules

Privilege management

Syntax and examples for granting privileges

1. Grant user-level privileges

   The syntax is as follows:

   -- Grant the CREATE SENSITIVE RULE privilege to a user or role. This privilege is at the user level.
   GRANT CREATE SENSITIVE RULE ON *.* TO <user_or_role_list>;
   
   -- Grant the PLAINACCESS privilege 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, indicating all databases and tables in the global scope.

   Here are some examples:

   -- Grant the CREATE SENSITIVE RULE privilege to user u1.
   GRANT CREATE SENSITIVE RULE ON *.* TO u1;
   
   -- You can grant privileges to multiple users or roles at once.
   -- Grant the CREATE SENSITIVE RULE privilege 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 PLAINACCESS privilege to user u1 at the user level. You can also grant this privilege to multiple users or roles at once.
   GRANT PLAINACCESS ON *.* TO u1;
   REVOKE PLAINACCESS ON *.* FROM u1;
   
   -- Grant PLAINACCESS privilege to user u1 for rule r1.
   -- You can also grant this privilege to multiple users or roles at once, but you can only grant privilege 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 CREATE SENSITIVE RULE privilege from a user or role. This privilege is at the user level.
   REVOKE CREATE SENSITIVE RULE ON *.* FROM <user_or_role_list>;
   
   -- Revoke the PLAINACCESS privilege 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, indicating all databases and tables in the global scope.

   Here are some examples:

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

View privileges

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 rules by name.

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

2. View user privileges

   Syntax:

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

   Example:

   -- View all privileges 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 the definition and attributes of sensitive-level rules, see DBA/CDB_OB_SENSITIVE_RULES.