Manage column encryption privileges

After you configure column encryption rules, proper privilege management is crucial for ensuring data security. This topic describes the privilege system and management syntax of OceanBase Database's column encryption feature. It aims to help you establish a comprehensive privilege control mechanism to ensure that sensitive data is only accessible to authorized users.

Prerequisites

Before using sensitive rules, you need to enable transparent data encryption (TDE) and create a master key:

-- Set the transparent data encryption method to internal or obcloud.
ALTER SYSTEM SET tde_method = '<encryption_method>';
ALTER INSTANCE ROTATE INNODB MASTER KEY;	-- The first time you run this statement, 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 access to sensitive columns through data protection rules. OceanBase Database introduces new privilege types in the MySQL privilege system to support column encryption, including user-level privileges and sensitive rule-level privileges.

User-level privileges

Sensitive rule-level privileges

Privilege management

Grant privileges: syntax and examples

1. Grant privileges to a user

   The syntax is as follows:

   -- Grant a user or a role the privilege to create sensitive rules (user-level only).
   GRANT CREATE SENSITIVE RULE ON *.* TO <user_or_role_list>;
   
   -- Grant a user or a role the privilege to access plaintext data.
   GRANT PLAINACCESS ON *.* TO  <user_or_role_list>;
   

   The following table describes the parameters.

   Parameter	Description	Example
   user_or_role_list	The list of users or roles, separated by commas.	u1, u2, r1
   *.*	All tables in all databases.	Fixed value

   Here is an example:

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

2. Grant rule-level privileges

   The syntax is as follows:

   -- For rule-level privileges, use the SENSITIVE RULE keyword.
   GRANT PLAINACCESS ON SENSITIVE RULE <rule_name> TO <user_or_role_list>;
   

   The following table describes the parameters.

   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, separated by commas.	u1, u2, r1

   Here is an example:

   -- Grant user u1 the privilege to access plaintext of a user (the same privilege can be granted to multiple users).
   GRANT PLAINACCESS ON *.* TO u1;
   REVOKE PLAINACCESS ON *.* FROM u1;
   
   -- Grant user u1 the privilege to access plaintext of rule r1.
   -- The same privilege can be granted to multiple users, but only one rule at a time.
   GRANT PLAINACCESS ON SENSITIVE RULE r1 TO u1;
   

Revoke privileges: syntax and examples

1. Revoke user-level privileges

   The syntax is as follows:

   -- Revoke the privilege to create sensitive rules. This privilege is only available at the user level.
   REVOKE CREATE SENSITIVE RULE ON *.* FROM <user_or_role_list>;
   
   -- Revoke the privilege to access plaintext of specific rules.
   REVOKE PLAINACCESS ON *.* FROM <user_or_role_list>;
   

   The parameters are described in the following table.

   Parameter	Description	Example
   user_or_role_list	The list of users or roles, separated by commas.	u1, u2, r1
   *.*	All tables in all databases.	Fixed value

   Here are some examples:

   -- Revoke the privilege.
   REVOKE CREATE SENSITIVE RULE ON *.* FROM u1;
   
   -- You can revoke the privilege 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 privileges at the rule level. Note that the SENSITIVE RULE keyword must be used.
   REVOKE PLAINACCESS ON SENSITIVE RULE <rule_name> FROM <user_or_role_list>;
   

   The parameters are described in the following table.

   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, separated by commas.	u1, u2, r1

   Here are some examples:

   -- Revoke the plaintext access privilege from user u1. You can revoke the privilege from multiple users or roles at the same time.
   REVOKE PLAINACCESS ON *.* FROM u1;
   
   -- Revoke the plaintext access privilege from user u1 on rule r1.
   You can revoke the privilege from multiple users or roles at the same time, but you can only revoke the privilege from one rule at a time.
   REVOKE PLAINACCESS ON SENSITIVE RULE r1 FROM u1;
   

View privilege

1. View sensitive rules

   You can view all sensitive data protection rules in the current tenant or all sensitive data protection rules associated with a specified table or database. You can also filter the rules by name using the LIKE clause.

   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;
   

   The parameters are described in the following table:

   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:

   -- 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 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 test LIKE '%rule1%';
   SHOW SENSITIVE RULES FROM DATABASE test LIKE '%rule1%';
   

   The return result is as follows:

   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)'

   The return result fields are described in the following table:

   Field	Description	Example value
   rule_id	The rule ID, which is automatically generated by the system.	1, 2
   rule_name	The name of the rule.	'ENCRYPT_COL'
   POLICY	The type of the strategy.	'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 in different tables are separated with commas (,).

2. View user privileges

   The syntax is as follows:

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

   Example:

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

   The return 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

• Overview of column encryption
• Create column encryption rules
• For more information about the definitions and attributes of sensitive-level rules, see DBA/CDB_OB_SENSITIVE_RULES.