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
andUPDATE
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 executeUPDATE
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
Edit a user
Delete a user
- Log on to the DMS console.
- 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.
- Right-click the instance and select Account Management.
- On the Account Management page, find the user who you want to delete and click Delete in the Operation column.
- 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. |