Data Management:Manage user permissions on MySQL databases

Prerequisites

Before you begin, ensure that you have:

• A supported MySQL-compatible database: ApsaraDB RDS for MySQL, PolarDB for MySQL, AnalyticDB for MySQL, a self-managed MySQL database, a native MariaDB database, or ApsaraDB for MariaDB

• A DMS system role of DMS administrator, database administrator (DBA), or regular user (such as instance owner)

Overview

The RDS and PolarDB consoles let you assign coarse-grained permissions to MySQL databases—for example, read-only or read/write access, or the ability to run DML or DDL statements. When you need more flexible, finer-grained control, use the DMS account management feature instead.

With DMS account management, you can:

• Grant a user global permissions to run SELECT and UPDATE statements across all databases in an instance

• Grant a user SELECT permissions on a specific table, or UPDATE permissions on a specific field

Global permissions apply to all databases in an instance. Fine-grained permissions can apply to specific databases or all databases in an instance.

For the complete list of supported permissions, see Supported global permissions and Supported fine-grained permissions.

Create a database account

1. Log on to the DMS console V5.0.DMS console V5.0

2. In the left-side navigation pane, right-click the instance you want to manage and select Account Management.

   In simple mode, click Database instances in the left-side navigation pane. In the instance list, right-click the target instance and select Account Management.

3. On the Account Management page, click Create User in the upper-left corner.

4. In the Create User dialog box, configure the Basic settings tab: DMS automatically generates and runs CREATE USER 'Username'@'Hostname' IDENTIFIED BY 'Password'; for each database in the instance. To set resource limits for the account, click Advanced Options. DMS generates and runs the following statement based on your settings:

   Parameter	Description
   User name	The name used to log on to the database.
   Host	Restricts the IP address from which the account can connect. Enter % to allow connections from any IP address (for example, all clients can connect). Enter a specific IP or subnet (for example, 10.5.10.%) to restrict access. The default value is %.
   Password	The password for logging on to databases in this instance.
   Confirm Password	Re-enter the password to confirm.

   GRANT USAGE ON *.* TO 'Username'@'Hostname' WITH MAX_QUERIES_PER_HOUR 100 MAX_UPDATES_PER_HOUR 200 MAX_CONNECTIONS_PER_HOUR 300 MAX_USER_CONNECTIONS 400;

   

   

5. Click the Global permissions tab and select the permissions to grant.

   Global permissions apply to all databases in the instance. If a permission is missing, either the instance does not support it or your account is not authorized to grant it. If you are using a standard account, log on with a privileged account and retry.

   

6. Click the Object permissions tab to assign fine-grained permissions on specific databases, tables, or fields. For example, to allow the account to query and insert data in rds_db and delete data from rds_db.rds_table, DMS generates and runs:

   Object permissions (fine-grained permissions) apply to specific objects within an instance. For most accounts, grant object-level permissions rather than global permissions to follow the principle of least privilege.

   GRANT SELECT,INSERT ON `rds_db`.* TO 'Username'@'Hostname';
   GRANT DELETE ON `rds_db`.`rds_table` TO 'Username'@'Hostname';

   

7. Click Confirm.

8. In the Preview SQL Statement message, review the generated SQL and click Confirm.

   If the instance is managed in Security Collaboration mode, the SQL statements may be blocked by security rules. Follow the on-screen instructions, or contact a DBA or DMS administrator. To modify security rules, see FAQ.

Edit or delete a database account

You can modify the username, password, global permissions, and fine-grained permissions of any account you are authorized to manage. You can also revoke permissions or delete accounts.

1. Log on to the DMS console V5.0.

2. In the left-side navigation pane, right-click the instance you want to manage and select Account Management.

   In simple mode, click Database instances in the left-side navigation pane. In the instance list, right-click the target instance and select Account Management.

3. On the Account Management page, find the account:

   • Click Edit in the Operation column to modify the account.

   • Click Delete in the Operation column to delete the account.

Supported global permissions

Global permissions apply to all databases in an instance.

Supported fine-grained permissions

Fine-grained permissions apply to specific databases, tables, fields, or other objects within an instance.

FAQ

Why does DMS block my `CREATE USER` statement with a security rule error?

This happens when the instance is managed in Security Collaboration mode and the active security rule set does not allow DCL statements to run directly in SQL Console. To fix this, add CREATE_USER to the All DCL can execute directly in SQLConsole rule:

1. In the left-side navigation pane, right-click the instance and select View Details to find the security rule set assigned to the instance.

2. Log on to the DMS console V5.0. In the top navigation bar, choose Security and disaster recovery (DBS) > Security Rules.

3. On the Security Rules tab, find the rule set and click Edit in the Actions column.

4. In the left-side navigation pane, click SQL Correct.

5. Set Checkpoints to SQL execution rules.

6. Find the All DCL can execute directly in SQLConsole rule and click Edit in the Actions column.

7. In the Change Rule - SQL Correct dialog box, add CREATE_USER in the SQL editor.

8. Click Submit.