DocsICP FilingConsole
Documentation
Home Page Data Management User Guide Data assets Instance management Account permission management Manage database accounts and permissions

Manage database accounts and permissions

Updated at:
Copy as MD
Product
My Favorites

Data Management (DMS) lets you create database accounts for MySQL, MongoDB, and PostgreSQL databases, customize combinations of permission types, and grant table-level permissions. This topic describes how to create, edit, and delete database accounts and manage account permissions in DMS.

Note

DMS supports account management only for MySQL, PostgreSQL, and MongoDB databases. To manage accounts of other database engines, go to the console of the corresponding service.

Prerequisites

  • The database is one of the following types:

    • MySQL: ApsaraDB RDS for MySQL, PolarDB for MySQL, AnalyticDB for MySQL, a self-managed MySQL database, a self-managed MariaDB database, or ApsaraDB for MariaDB.

    • ApsaraDB for MongoDB.

      Note

      If you create a user in the Admin database, make sure that at least one existing user has the permission to create users, such as dbAdminAnyDatabase, userAdmin, or userAdminAnyDatabase.

    • PostgreSQL: ApsaraDB RDS for PostgreSQL or AnalyticDB for PostgreSQL.

  • Your system role is DMS administrator, DBA, or regular user (instance owner). For more information, see System roles.

  • The Database account and Database password of the target database are obtained.

  • If you want to create accounts in the DMS console, make sure that the Database account used to log on to DMS has the permission to create users.

Examples of account permission management

  • Grant account A the global SELECT and UPDATE permissions.

    Note

    Global permissions apply at the database instance level. For more information about global permission types, see Permission reference.

  • Grant account B the SELECT permission on a single table or the UPDATE permission on a specific column.

    Note

    Object permissions can apply to all database objects or to one or more specified database objects. For more information about object permission types, see Permission reference.

Manage database accounts

Create a database account

Note

Database accounts created through DMS are standard accounts.

A DMS administrator is a RAM user that manages access to the DMS console and approves operations. A privileged database account is defined at the database instance level and has database operation permissions such as CREATE USER and GRANT. The two belong to different permission systems: to run database administration operations such as CREATE USER in DMS, you must use a privileged database account. A DMS administrator does not have these database-level permissions.

If you encounter the following error when you run CREATE USER in DMS:

execute create_user failed: you need (at least one of) the create user privilege for this operation

Troubleshoot the issue as follows:

  1. Create a privileged account in the ApsaraDB RDS console, log on to DMS with that account, and then retry the operation.

  2. Alternatively, create the database user directly on the Account Management page of the ApsaraDB RDS console.

  3. If the DMS instance has security hosting mode enabled, you may not be able to switch the database account in DMS. In this case, create the account in the ApsaraDB RDS console, or adjust the security hosting settings of the instance before you retry.

Create a MySQL database account

  1. Log in to DMS 5.0.

  2. Log on to the destination database. For more information, see Log on to a database.

  3. On the home page, click the database instance in the navigation pane on the left. In the instance list, right-click the destination instance name and click Database Account Management.

  4. On the Database Account Management page, click Create Database Account in the upper-left corner.

  5. In the dialog box that appears, configure the following parameters.

    1. Click the Basic settings tab and configure the parameters.

      Parameter

      Description

      Database account

      The name of the account used to log on to the database.

      Host

      The IP address from which the account is allowed to access the database.

      Note

      If you leave this parameter empty, the account is not restricted to a specific IP address. The default value is %.

      Password

      The logon password.

      Confirm Password

      Enter the logon password again.

      Note

      The SQL statement generated from the preceding parameters is in the following format: CREATE USER `username`@`host` IDENTIFIED BY `password`;.

      To configure Advanced Options, click the Advanced Options button and configure the options.

      For example, the SQL statement generated from the following settings is:

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

    2. Click the Global permissions tab and select the target permissions. For more information about global permissions, see MySQL global permissions.

      Note

      If you use a standard account and cannot find the target permission in the permission type list, the account may not have the required permission or the instance type may not support the permission. If the issue is caused by insufficient account permissions, retry with a privileged account.

    3. Click the Object permissions tab and configure the parameters. For more information about object permissions, see MySQL object permissions.

      For example, the SQL statements generated from the following settings are:

      GRANT SELECT,INSERT ON `rds_db`.* TO 'username'@'host'; 
 GRANT DELETE ON `rds_db`.`rds_table` TO 'username'@'host';

  6. Click Confirm.

  7. In the Preview SQL Statement dialog box, click Confirm.

    Note

    Database instances in Security Collaboration mode are restricted by security rules. If the operation cannot be performed, follow the on-screen instructions or contact a DBA or DMS administrator. For more information about how to adjust security rules, see FAQ.

Create a MongoDB database account

  1. Log in to DMS 5.0.

  2. Log on to the target database. For more information, see Log on to a database.

    Important

    For a MongoDB replica set instance, log on to the primary node.

  3. On the home page, click the database instance in the navigation pane on the left. In the instance list, right-click the destination instance name and click Database Account Management.

  4. Click Create User in the upper-left corner of the page and configure the following information.

    1. Configure the user information.

      In the Create User dialog box, the Current Database Permissions tab provides the following roles for you to select:

      • Normal operation role: read (permission to query data in the current database) and readWrite (permission to insert, delete, modify, and query data in the current database)

      • Administrator operation role: dbAdmin (management operations on database objects, without read and write permission on the database) and userAdmin (permission to create users in the current database)

      Parameter

      Description

      Target database

      Select the database to which the user belongs from the drop-down list.

      Note

      • If you select a database other than admin, the created user is a standard user.

      • If you select the admin database, the created user is a privileged user.

      Username

      The name of the user.

      • Chinese characters are not supported.

      • Letters, digits, and special characters are supported.

      • Special characters include: !#$%^&*()_+-=

      Password

      The password of the user.

      To ensure data security, the password must be 8 to 32 characters in length and contain at least three of the following character types:

      • Uppercase letters

      • Lowercase letters

      • Digits

      • Special characters (!#$%^&*()_+-=)

      Confirm Password

      Enter the password again.

    2. Configure the permissions of the user.

      Note

      • If you select the admin database as the target database:

        On the Current Database Permissions tab, you can configure permissions such as Normal operation role, Administrator operation role, Instance-level operation role, Cluster management role, Backup and restore role, and Superuser role. For more information about role permissions, see MongoDB role permissions.

        You can also click the Other Database Permissions tab to add database names and configure role permissions for the corresponding databases.

      • If you select a database other than admin as the target database:

        On the Current Database Permissions tab, you can configure only the Normal operation role and Administrator operation role for the current database. For more information about role permissions, see MongoDB role permissions.

        The Other Database Permissions tab is not available.

  5. Click Confirm.

    Note

    Database instances in Security Collaboration mode are restricted by security rules. If the operation cannot be performed, follow the on-screen instructions or contact a DBA or DMS administrator.

Create a PostgreSQL database account

  1. Log in to DMS 5.0.

  2. Log on to the target database. For more information, see Log on to a database.

  3. Click Database instances on the left side of the homepage. In the instance list, right-click the target instance name and click Account Management.

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

  5. In the dialog box that appears, configure the following parameters.

    1. Click the Basic settings tab and configure the parameters.

      Parameter

      Description

      User name

      The username of the account.

      Password

      The logon password.

      Confirm Password

      Enter the logon password again.

      Password expiration time

      The expiration time of the password. For example, if you set the password to expire at 00:00 on December 30, 2021, the password becomes invalid at that time.

      This parameter is optional. By default, the password does not expire.

      Connection limit

      The maximum number of concurrent connections for the account. For example, if you set this parameter to 10, the account can connect to the database from up to 10 clients at the same time.

      The default value is -1, which indicates that the number of connections is not limited.

      Remarks

      The description of the account.

    2. Click the Permissions tab and select the target permissions.

      Permission type

      Description

      Allow logon

      The logon permission. The corresponding SQL commands are LOGIN and NOLOGIN. By default, logon is allowed.

      Allow user creation

      The permission to create database accounts. The corresponding SQL commands are CREATEROLE and NOCREATEROLE. By default, user creation is allowed.

      Allow database creation

      The permission to create databases. The corresponding SQL commands are CREATEDB and NOCREATEDB. By default, database creation is allowed.

  6. Click Confirm.

  7. In the Preview SQL dialog box, click Confirm.

    Note

    Database instances in Security Collaboration mode are restricted by security rules. If the operation cannot be performed, follow the on-screen instructions or contact a DBA or DMS administrator. For more information about how to adjust security rules, see Permission reference.

Edit or delete an account

You can use the account editing feature to modify the Username, Password, and MySQL Global permissions and Object permissions of an account.

  1. Log in to DMS 5.0.

  2. Click Database instances on the right side of the homepage. In the instance list, right-click the target instance name and click Account Management.

  3. Find the target account and click Edit or Delete to perform the corresponding operation.

Edit or delete an account

You can use the account editing feature to modify the Username, Password, and MySQL Global permissions and Object permissions of an account.

  1. Log in to DMS 5.0.

  2. On the home page, click the database instance in the navigation pane on the left. In the instance list, right-click the destination instance name and click Database Account Management.

  3. Find the target account and click Edit or Delete to perform the corresponding operation.

Switch the database account

  1. Log in to DMS 5.0.

  2. In the Database instances area on the left, find the target instance.

  3. Right-click the instance name and select Edit Instance.

  4. In the Basic Information section, enter the database account and password that you want to use for logon.

  5. Click Save.

Grant or revoke database account permissions

  1. Log in to DMS 5.0.

  2. On the home page, click the database instance in the navigation pane on the left. In the instance list, right-click the destination instance name and click Database Account Management.

  3. Find the target account and click Edit.

  4. In the permissions area, select the target permissions to grant them or clear the target permissions to revoke them, and then click Confirm.

Permission reference

MySQL global permissions

Permission

Object

Description

CREATE

Databases, tables, or indexes

Create a database, table, or index.

DROP

Databases, tables, or views

Delete a database, table, or view.

GRANT OPTION

Databases, tables, or stored programs

Grant or revoke permissions for other accounts.

REFERENCES

Databases, tables, or columns

Create a foreign key.

LOCK TABLES

Databases

Lock tables in a database.

EVENT

Databases

Query, create, modify, or delete MySQL events.

ALTER

Tables or views

Modify a table or view. For example, add a column or index, or modify a column.

DELETE

Tables

Delete data from a table.

INDEX

Tables

Create or delete indexes for a table.

INSERT

Tables or columns

Insert data into a table or column.

SELECT

Tables or columns

Query data in a table or column.

UPDATE

Tables or columns

Update data in a table or column.

CREATE VIEW

Views

Create a view.

SHOW VIEW

Views

View the definition of a view.

TRIGGER

Triggers

Create, delete, run, or display a trigger.

ALTER ROUTINE

Stored procedures

Modify a stored procedure.

CREATE ROUTINE

Stored procedures

Create a stored procedure.

EXECUTE

Stored procedures

Run a stored procedure.

FILE

File access on a server host

Access files on a server host.

CREATE TEMPORARY TABLES

Server administration

Create a temporary table.

CREATE USER

Server administration

Create an account.

PROCESS

Server administration

View information about running processes.

RELOAD

Server administration

Run commands such as FLUSH-HOSTS, FLUSH-LOGS, FLUSH-PRIVILEGES, FLUSH-STATUS, FLUSH-TABLES, FLUSH-THREADS, REFRESH, and RELOAD.

REPLICATION CLIENT

Server administration

Check the status of replication sources and replicas.

REPLICATION SLAVE

Server administration

Read binary logs from the replication source.

SHOW DATABASES

Server administration

View the names of all databases.

SHUTDOWN

Server administration

Shut down the server.

SUPER

Server administration

Run KILL statements to terminate threads.

Note

The SUPER permission is not supported by RDS MySQL or RDS MariaDB.

MySQL object permissions

Permission

Object

Description

CREATE

Databases, tables, or indexes

Create a database, table, or index.

DROP

Databases, tables, or views

Delete a database, table, or view.

GRANT OPTION

Databases, tables, or stored programs

Grant or revoke permissions for other accounts.

REFERENCES

Databases, tables, or columns

Create a foreign key.

LOCK TABLES

Databases

Lock tables in a database.

EVENT

Databases

Query, create, modify, or delete MySQL events.

ALTER

Tables or views

Modify a table or view. For example, add a column or index, or modify a column.

DELETE

Tables

Delete data from a table.

INDEX

Tables

Create or delete indexes for a table.

INSERT

Tables or columns

Insert data into a table or column.

SELECT

Tables or columns

Query data in a table or column.

UPDATE

Tables or columns

Update data in a table or column.

CREATE VIEW

Views

Create a view.

SHOW VIEW

Views

View the definition of a view.

TRIGGER

Triggers

Create, delete, run, or display a trigger.

MongoDB role permissions

For more information about role permissions, see the MongoDB official documentation.

Role type

Role

Description

Normal operation role

read

Query data in the current database.

readWrite

Insert, delete, modify, and query data in the current database.

Administrator operation role

dbAdmin

Perform administrative operations on database objects, without read or write permissions on the database.

userAdmin

Create users in the current database.

dbOwner

Perform all operations on the current database.

Instance-level operation role

readAnyDatabase

Query data in all databases of the instance.

readWriteAnyDatabase

Insert, delete, modify, and query data in all databases of the instance.

userAdminAnyDatabase

Create users in all databases of the instance.

dbAdminAnyDatabase

Perform dbAdmin operations on all databases of the instance.

Cluster management role

hostManager

Perform administrative operations on database objects, without read or write permissions on the database.

clusterMonitor

Query the cluster and replica set.

clusterManager

Manage and monitor the cluster and replica set.

clusterAdmin

Perform all operations on the cluster.

Backup and restore role

backup

Query data in all databases of the instance.

restore

Insert, delete, modify, and query data in all databases of the instance.

Superuser role

Root

Superuser permissions.

Related topics

Best practices for permission management in ApsaraDB RDS for PostgreSQL

FAQ

Q: When I create a permission user, an error indicates that the CREATE_USER command is blocked by security rules. How do I resolve this issue?

A: Resolve this issue by following the steps in the error dialog box. Perform the following operations:

  1. View the name of the security rule set bound to the instance.

    In the instance list on the left side of the console homepage, find the target instance, right-click it, and click View Details.

  2. Adjust the security rules.

    1. Log on to DMS.

    2. In the top menu bar, choose security and disaster recovery (DBS) > security rules.

    3. On the security rules tab, find the target rule set and click Edit in the Actions column.

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

    5. Set Checkpoint to SQL execution rules.

    6. Find the Allow all DCL statements to be executed directly in SQL Console security rule, and click Edit in the Actions column.

    7. In the Rule DSL section of the Edit Rule dialog box, change the rule to: if @fac.sql_type in ["DCL"] then @act.allow_execute_direct end.

      This syntax matches the entire DCL category and covers all DCL statements such as CREATE USER and GRANT, without listing each SQL type individually. In this rule, allow_execute_direct allows statements to be executed directly in SQL Console, whereas allow_submit only allows statements to be submitted as tickets for approval. The two options differ in how permissive they are, so choose based on your requirements.

  3. Click Submit.

  4. Check the risk identification rules. Under SQL Change > Risk Identification Rules, verify whether a rule flags DCL statements as high risk. If such a rule exists, disable or adjust it. Otherwise, it overrides the DCL rule you configured in the previous step, and the rule change does not take effect.

Note

Only instances in Security Collaboration mode support editing security rules. Instances in Flexible Management and Stable Change modes use built-in system rules that cannot be edited. If the target instance is not in Security Collaboration mode, purchase a Security Collaboration license and switch the control mode before you modify the rules as described above. Also make sure that the rule set you modified is associated with the target instance. Otherwise, the changes do not take effect.

Previous：Account permission managementNext：MongoDB database account permissions