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 instance, a PolarDB for MySQL instance, or a user-built MySQL database is registered in DMS.
  • You are assigned a required role for the MySQL database instance that is registered in DMS. The role varies based on the control mode of the instance. The following table describes the details.
    Control mode Role requirement
    Security Collaboration You must be a DMS administrator, a database administrator (DBA), or the owner of the relevant database instance.
    Stable Change No specific role is required.
    Flexible Management No specific role is required.

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 allow a user only to read data from, or read data from and write data to, a MySQL database. You can also allow a user to execute only data manipulation language (DML) or data definition language (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. Example:

  • 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 permission to execute SELECT statements on a table or the permission 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. Log on to the DMS console.
  2. In the search box at the top of the left-side navigation pane, enter the name of the MySQL database whose permissions you want to manage. From the matched result, find the instance to which the database belongs.
  3. Right-click the instance and select Account Management.
    Account Management
  4. On the Account Management page, click Create User in the upper-left corner.
  5. In the Create User dialog box, perform the following steps:
    1. On the Basic settings tab, set the parameters as described in the following table.
      Create User dialog box
      Parameter Description
      User name The name of the user to be created.
      Host The name of the host on which the user can log on to the databases in the current instance. Default value: %.
      Password The password that the user can use to log on to the databases in the current instance.
      Confirm Password The password that the user can use to log on to the databases in the current instance. Enter the same password again to confirm the password.
      Note After you set the preceding parameters, DMS automatically generates an SQL statement based on the parameters you set. Then, DMS executes the statement for each database in the current instance. The format 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 relevant parameters. You can grant specific permissions on specific objects as required.
      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
  6. In the Create User dialog box, click OK.
  7. In the Preview SQL Statement dialog box, click OK.
    Note If the database instance is in Security Collaboration mode, SQL statements can be generated based on the parameters you set. However, the SQL statements may fail to be executed due to security rules. In this case, you can perform operations as prompted or contact the DBA or DMS administrator.

Edit a user

  1. Log on to the DMS console.
  2. In the search box at the top of the left-side navigation pane, enter the name of the MySQL database whose permissions you want to manage. From the matched result, find the instance to which the database belongs.
  3. Right-click the instance and select Account Management.
  4. On the Account Management page, find the user whose information you want to edit and click Edit in the Operation column.
    Edit

Delete a user

  1. Log on to the DMS console.
  2. In the search box at the top of the left-side navigation pane, enter the name of the MySQL database whose permissions you want to manage. From the matched result, find the instance to which the database belongs.
  3. Right-click the instance and select Account Management.
  4. On the Account Management page, find the user who you want to delete and click Delete in the Operation column.
  5. In the Prompt message, click OK.

Supported global permissions

Permission Context Description
CREATE Databases, tables, or indexes Enables a user to create a database, a table, or an index.
DROP Databases, tables, or views Enables a user to delete a database, a table, or a view.
GRANT OPTION Databases, tables, or stored procedures Enables a user to grant the one or more permissions that are to be granted to the user to other users, or revoke the permissions from them.
REFERENCES Databases, tables, or fields Enables a user to create a foreign key to reference a database, a table, or a field.
LOCK TABLES Databases Enables a user to lock tables in a database.
EVENT Databases Enables a user to query, create, modify, or delete an event in a database.
ALTER Tables or views Enables a user to modify a table or a view, for example, add a field to the table, create an index, or modify a field in the table.
DELETE Tables Enables a user to delete data from a table.
INDEX Tables Enables a user to create or delete indexes for a table.
INSERT Tables or fields Enables a user to insert data into a table or a field.
SELECT Tables or fields Enables a user to query data in a table or a field.
UPDATE Tables or fields Enables a user to update data in a table or a field.
CREATE VIEW Views Enables a user to create a view.
SHOW VIEW Views Enables a user to check a view.
TRIGGER Triggers Enables a user to create, delete, execute, or display a trigger.
ALTER ROUTINE Stored procedures Enables a user to modify a stored procedure.
CREATE ROUTINE Stored procedures Enables a user to create a stored procedure.
EXECUTE Stored procedures Enables a user to execute a stored procedure.
FILE File access on a server host Enables a user to access the files on a server host.
CREATE TEMPORARY TABLES Server administration Enables a user to create a temporary table on a server.
CREATE USER Server administration Enables a user to create a user on a server.
PROCCESS Server administration Enables a user to query information about the threads that are running on a server.
RELOAD Server administration Enables 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 Enables a user to check the statuses of the primary and replica servers, and binlogs. 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 Enables a user to query updates that have been made to databases on a source server. 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 Enables a user to view the names of all the databases on a server.
SHUTDOWN Server administration Enables a user to shut down a server.
SUPER Server administration Enables a user to use KILL statements to terminate the threads that are running on a server.

Supported fine-grained permissions

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