Data Management (DMS) supports fine-grained permission control for MySQL databases. For example, you can manage permissions on databases, tables, fields, and views. This topic describes how to manage user permissions on MySQL databases in DMS.

Prerequisites

  • An ApsaraDB RDS for MySQL, PolarDB for MySQL, AnalyticDB for MySQL, self-managed MySQL, native MariaDB, or ApsaraDB RDS for MariaDB TX database is used.
  • You are a DMS administrator, a database administrator (DBA), or a regular user such as the owner of an instance. For more information, see System roles.

Overview

You can manage coarse-grained permissions on MySQL databases in ApsaraDB RDS for MySQL instances or PolarDB for MySQL instances in the corresponding console. For example, you can grant other users the read-only permissions or the read and write permissions on a MySQL database. You can also grant other users the permissions to execute DML or DDL statements on the MySQL database. However, you may need more flexible and finer-grained permission management. In this case, you can use the user permission management feature of DMS. Examples:

  • You can grant User A the global permissions to execute SELECT and UPDATE statements.
    Note Global permissions apply to all databases in a database instance. For more information, see Supported global permissions.
  • You can grant User B the permissions to execute SELECT statements on a table or the permissions to execute UPDATE statements on a field.
    Note Fine-grained permissions can apply to specific or all databases in a database instance. For more information, see Supported fine-grained permissions.

Create a user

  1. Go to the DMS console V5.0.
  2. In the left-side navigation pane of the DMS console, right-click the instance that you want to manage 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, perform the following steps:
    1. On the Basic settings tab, set the parameters that are described in the following table.
      Create User dialog box
      Parameter Description
      User name The name of the user that you can use to log on to the database.
      Host The name of the host on which you can log on to the databases in the current instance. You can specify more than one IP address. Separate multiple IP addresses with commas (,).
      Note If you do not specify this parameter, the user is not restricted to access the authorized databases from specific IP addresses. The default value of this parameter is %.
      Password The password that you can use to log on to the databases in the current instance.
      Confirm Password Enter the password again to confirm the password.
      Note After you set the preceding parameters, DMS automatically generates an SQL statement based on the parameters that you set. Then, DMS executes the statement for each database in the current instance. The syntax of the SQL statement is CREATE USER 'Username'@'Hostname' IDENTIFIED BY 'Password';.

      You can click Advanced Options to complete more configurations.

      For example, you can set specific upper limits for the user, as shown in the following figure. In this case, DMS generates and executes the following SQL statement:
      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;
      Advanced Options
    2. Click the Global permissions tab and select one or more permissions that you want to grant to the user.
      Global permissions tab
    3. Click the Object permissions tab and set the parameters to grant fine-grained permissions to the user.
      For example, you can allow the user to query or insert data in a database, and delete data from a table in the database, as shown in the following figure. In this case, DMS generates and executes the following SQL statements:
      GRANT SELECT,INSERT ON `rds_db`.* TO 'Username'@'Hostname'; 
       GRANT DELETE ON `rds_db`.`rds_table` TO 'Username'@'Hostname';
      Object permissions tab
  5. Click Confirm.
  6. In the Preview SQL Statement message, click Confirm.
    Note If the database instance is managed in Security Collaboration mode, SQL statements can be generated based on the parameters that you set. However, the SQL statements may fail to be executed due to security rules. In this case, you can perform operations by following the on-screen instructions or contact a database administrator (DBA) or DMS administrator.

Modify or delete a user

  1. Go to the DMS console V5.0.
  2. In the left-side navigation pane of the DMS console, right-click the instance that you want to manage and select Account Management.
  3. On the Account Management page, find the user that you want to manage and click Edit in the Operation column to modify the information about the user, or click Delete in the Operation column to delete the user.

Supported global permissions

Permission Object Description
CREATE Databases, tables, or indexes Allows a user to create a database, a table, or an index.
DROP Databases, tables, or views Allows a user to delete a database, a table, or a view.
GRANT OPTION Databases, tables, or stored procedures Allows a user to grant or revoke one or more permissions for other users.
REFERENCES Databases, tables, or fields Allows a user to create a foreign key to reference a database, a table, or a field.
LOCK TABLES Databases Allows a user to lock tables in a database.
EVENT Databases Allows a user to query, create, modify, or delete an event in a database.
ALTER Tables or views Allows a user to modify a table or a view. For example, a user can be allowed to add a field to the table, create an index, or modify a field in the table.
DELETE Tables Allows a user to delete data from a table.
INDEX Tables Allows a user to create or delete indexes for a table.
INSERT Tables or fields Allows a user to insert data into a table or a field.
SELECT Tables or fields Allows a user to query data in a table or a field.
UPDATE Tables or fields Allows a user to update data in a table or a field.
CREATE VIEW Views Allows a user to create a view.
SHOW VIEW Views Allows a user to check a view.
TRIGGER Triggers Allows a user to create, delete, execute, or display a trigger.
ALTER ROUTINE Stored procedures Allows a user to modify a stored procedure.
CREATE ROUTINE Stored procedures Allows a user to create a stored procedure.
EXECUTE Stored procedures Allows a user to execute a stored procedure.
FILE File access on a server host Allows a user to access the files on a server host.
CREATE TEMPORARY TABLES Server administration Allows a user to create a temporary table on a server.
CREATE USER Server administration Allows a user to create a user on a server.
PROCESS Server administration Allows a user to query information about the threads that are running on a server.
RELOAD Server administration Allows a user to run commands on a server, such as the FLUSH-HOSTS, FLUSH-LOGS, FLUSH-PRIVILEGES, FLUSH-STATUS, FLUSH-TABLES, FLUSH-THREADS, REFRESH, and RELOAD commands.
REPLICATION CLIENT Server administration Allows a user to check the statuses of the primary and replica servers and binary logs. This permission is required for replication. Grant this permission to a user who is created to connect a replica server to the source server.
REPLICATION SLAVE Server administration Allows a user to check the statuses of the primary and replica servers and binary logs. This permission is required for replication. Grant this permission to a user who is created to connect a replica server to the source server.
SHOW DATABASES Server administration Allows a user to view the names of all the databases on a server.
SHUTDOWN Server administration Allows a user to shut down a server.
SUPER Server administration Allows a user to execute KILL statements to terminate the threads that are running on a server.

Supported fine-grained permissions

Permission Object Description
CREATE Databases, tables, or indexes Allows a user to create a database, a table, or an index.
DROP Databases, tables, or views Allows a user to delete a database, a table, or a view.
GRANT OPTION Databases, tables, or stored procedures Allows a user to grant or revoke one or more permissions for other users.
REFERENCES Databases, tables, or fields Allows a user to create a foreign key to reference a database, a table, or a field.
LOCK TABLES Databases Allows a user to lock tables in a database.
EVENT Databases Allows a user to query, create, modify, or delete an event in a database.
ALTER Tables or views Allows a user to modify a table or a view. For example, a user can be allowed to add a field to the table, create an index, or modify a field in the table.
DELETE Tables Allows a user to delete data from a table.
INDEX Tables Allows a user to create or delete indexes for a table.
INSERT Tables or fields Allows a user to insert data into a table or a field.
SELECT Tables or fields Allows a user to query data in a table or a field.
UPDATE Tables or fields Allows a user to update data in a table or a field.
CREATE VIEW Views Allows a user to create a view.
SHOW VIEW Views Allows a user to check a view.
TRIGGER Triggers Allows a user to create, delete, execute, or display a trigger.