:Configure column encryption rules with SQL

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 for MySQL instance runs MySQL 5.7 with a minor engine version of 20231031 or later, or MySQL 8.0 with a minor engine version of 20240430 or later. 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 specified by name already exists, it is updated. Otherwise, a new rule for name is added. In this case, the update operation is identical 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 must be in the JSON format. Therefore, when you delete a single rule, you must specify the rule name in the "xxx" format instead of the xxx format. For example, specify '"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 and to facilitate temporary plaintext data maintenance, the always-confidential database feature allows you to grant users fullAccess permission. A user with this permission can access the database in plaintext:

  • 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 be within the MySQL-supported range, which is from 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"}');