ApsaraDB RDS:Configure column encryption rules by using SQL statements

The always-confidential database feature stores the data protection rule that you configured in the system tables of your RDS instance. Only the privileged account or authorized users can write data to the system tables in the RDS instance. The system tables are the mysql.encdb_sensitive_rules and mysql.encdb_auth_users tables.

Prerequisites

• The RDS instance runs MySQL 5.7 and a minor engine version of 20231031 or later. The RDS instance runs MySQL 8.0 and a minor engine version of 20240430 or later.

  Note

  For more information, see Update the minor engine version.

• The always-confidential database feature is enabled. For more information, see Use the always-confidential database feature.

• A privileged account is used to configure data protection rules.

Usage notes

• After you configure and enable a data protection rule, the rule takes effect on all databases on an RDS instance, and you do not need to repeatedly configure the rule.

• We recommend that you use separate database accounts to manage data protection rules and online applications. Do not grant management permissions on online applications unless necessary.

• Exercise caution when you grant the read and write permissions on the mysql.encdb_sensitive_rules and mysql.encdb_auth_users tables. The modification of the tables may allow attackers to bypass always-confidential protection.

Operations in SQL UDFs

• ADD: adds a rule.

  SELECT encdb_rule_op('add',<rule object json string> or <rules list json string>);

  Example 1: Add a rule named newrule.

  SELECT encdb_rule_op('add','{"name":"newrule","enabled":true,"meta":{"databases":["test"],"tables":["test"],"columns":["a","b"]}}');

  Example 2: Add rules named newrule1 and newrule2.

  SELECT encdb_rule_op('add','[{"name":"newrule1","enabled":true,"meta":{"databases":["test"],"tables":["test"],"columns":["a","b"]}},{"name":"newrule2","enabled":true,"meta":{"databases":["test2"],"tables":["test2"],"columns":["a","b"]}}]');

• UPDATE: updates a rule.

  SELECT encdb_rule_op('update',<rule object json string> or <rules list json string>);

  Example 1: Update the rule named newrule.

  SELECT encdb_rule_op('update','{"name":"newrule","enabled":true,"meta":{"databases":["test"],"tables":["test"],"columns":["a","b"]}}');

  Example 2: Update the rules named newrule1 and newrule2.

  SELECT encdb_rule_op('update','[{"name":"newrule1","enabled":true,"meta":{"databases":["test"],"tables":["test"],"columns":["a","b"]}},{"name":"newrule2","enabled":true,"meta":{"databases":["test2"],"tables":["test2"],"columns":["a","b"]}}]');

  Note

  If the rule that is specified by the name parameter exists, the rule is updated. If the rule that is specified by the name parameter does not exist, the rule is created. In this case, the UPDATE operation is equivalent to the ADD operation.

• DELETE: deletes a rule.

  SELECT encdb_rule_op('delete',<rule name json string> or <rule name list json string>);

  Example 1: Delete the rule named delrule.

  SELECT encdb_rule_op('delete','"delrule"');

  Example 2: Delete the rules named delrule1 and delrule1.

  SELECT encdb_rule_op('delete','["delrule1","delrule2"]');

  Note

  The value of the params parameter is in the JSON format. When you delete a rule, the rule name must be in the "xxx" format instead of the xxx format. For example, the value must be '"delrule"' instead of 'delrule'.

• GRANT: grants or revokes permissions.

  Note

  You must grant users the required permissions.

  Grant or revoke permissions.

  SELECT encdb_rule_op('grant',<users object json string>);

  Example 1: Grant the appuser and test_user users the permissions to access limited resources.

  SELECT encdb_rule_op('grant','{"restrictedAccess": ["appuser","test_user"]}'); 

  Example 2: Revoke the permissions of the illegal user.

  SELECT encdb_rule_op('grant','{"noneAccess": ["illegal"]}'); 

  Example 3: Grant the appuser and test_user users the permissions to access limited resources and revoke the permissions of the illegal user.

  SELECT encdb_rule_op('grant','{"restrictedAccess": ["appuser","test_user"], "noneAccess": ["illegal"]}'); 

  Note

  The users that are mentioned in this section must exist in the RDS instance.

  Grant users full access permissions for temporary use

  For ease of use, the always-confidential database feature allows you to grant users the fullAccess permission to temporarily maintain plaintext data in an efficient manner. If you grant users the permission, the users can access the plaintext database data of your RDS instance.

  • In this case, the configured data protection rule does not take effect on the users, and the users can directly query the plaintext database data in the RDS instance.

  • For security purposes, we recommend that you grant the permissions for a short period of time and grant the permissions only if necessary. Before you grant the fullAccess permission, you must understand your business requirements and take responsibilities for the related risks.

  • The value of the expired parameter must fall in the range of 1970-01-02 00:00:01 to 2038-01-18 03:14:07.

  Example: Grant the dba user the full access permissions for temporary use

  SELECT encdb_rule_op('grant','{"fullAccess": ["dba"], "expired": "2023-08-21 14:21:30"}');