MySQL Database User Management
Prerequisites
• The user has logged in to DMS.
• The target instance has been entered in DMS with the database type of MySQL.
• The database user logged in to the instance has permission to manage the database user password.
Procedure
1. On the left menu bar, click Data Source Management > Instance Metadata to go to the Instance List page.
2. Select the target instance and click the Login button in the Operate menu bar.
3. Enter the user name and password for the database to finish logging in to the instance.
4. After logging in to the instance, click the More > Database User Management button.
Function Introduction
Database user management allows you to view all database accounts of the current instance, and create, delete, and edit the account.
Account List
This list displays all account information under the instance, including account name, host information, permissions, and more. A fuzzy search by account name is supported.
• The user logged in to the database must have permission to manage the database account. Otherwise, the account list cannot be viewed.
Creating an Account
1. Log in to DMS.
2. Click Data Source Management > Instance Metadata to go to the Instance List page.
3. On the Instance List page, click More > Database User Management to go to the Management page.
4. Click the Create Account button and enter the mandatory information such as account name and password.
5. Click the OK button to generate the DDL statement for creating the account.
6. In the DDL preview pop-up window, click the Execute button to finish creating the account.
7. You can set global and object permissions for the account, as well as options such as host information, maximum queries per hour, maximum updates per hour, maximum number of connections per hour, and maximum user connections. When you create an account, password strength can be automatically detected to improve security.
Editing an Account
1. Log in to DMS.
2. Click Data Source Management > Instance Metadata to go to the Instance List page.
3. On the Instance List page, click More > Database User Management to go to the Management page.
4. Go to the Operate menu of the list and click the Edit button to enter the Edit page.
5. Modify the property content as required.
6. Click the OK button to generate the DDL statement for modifying the account.
7. In the DDL preview pop-up window, click the Execute button to finish modifying the account.
8. You can edit the account and password, global permission, and object permissions for the account, as well as options such as host information, maximum queries per hour, maximum updates per hour, maximum number of connections per hour, and maximum user connections.
² Note
Changing the user name is not supported.
Deleting an Account
1. Log in to DMS.
2. Click Data Source Management > Instance Metadata to go to the Instance List page.
3. On the Instance List page, click More > Database User Management to go to the Management page.
4. Go to the Operate menu of the list and click the Delete button. After re-confirmation, click the OK button to finish deleting the specified account.
MySQL Global Permission
Permission
Object
Description
SELECT
Table/Column
Query permission.
INSERT
Table/Column
Insertion permission.
UPDATE
Table/Column
Update permission.
DELETE
Table
Deletion permission.
FILE
File
File access permission.
CREATE
Databases, tables, and indexes
Creation permission.
ALTER
Tables and views
Update permission.
INDEX
Table
Index permission.
DROP
Databases, tables, and views
Deletion permission.
EXECUTE
Stored Procedure
Permission to execute stored procedures.
SHOW VIEW
View
Permission to query views.
CREATE VIEW
View
Permission to create views
CREATE ROUTINE
Stored Procedure
Permission to create stored procedures.
ALTER ROUTINE
Stored Procedure
Permission to change stored procedures.
EVENT
Database
Permission to query, create, modify, and delete events.
TRIGGER
Trigger
Permission to create, delete, execute, and display triggers.
CREATE TEMPORARY TABLES
Temporary table
Permission to create temporary tables.
GRANT OPTION
Databases and tables
Grant the permission options.
SUPER
Server
Permission to kill threads
PROCESS
Server
Permission to view processes
RELOAD
Server
Permission to execute the FLUSH-HOSTS, FLUSH-LOGS, FLUSH-PRIVILEGES, FLUSH-STATUS, FLUSH-TABLES, FLUSH-THREADS, REFRESH, and RELOAD commands.
SHUTDOWN
Server
Permission to shut down the server.
SHOW DATABASE
Server
Permission to view databases.
LOCK TABLES
Database
Permission to lock tables.
REFERENCES
Database, tables, and columns
Foreign key permission.
REPLICATION CLIENT
Server
Replication permission.
REPLICATION SLAVE
Server
Replication permission.
CREATE USER
Server
Permission to create accounts.
CREATE TABLESPACE
Server
Permission to create tablespace.
MySQL Object Permissions
Permission
Object
Description
CREATE
Database, table, or index
Permission to create databases, tables, or indexes.
DROP
Database, table or view
Permission to delete databases, tables, or views.
GRANT OPTION
Database, table, or stored procedure
Grant the permission options.
REFERENCES
Database, table or column
Foreign key permission.
LOCK TABLES
Database
Permission to lock tables.
EVENT
Database
Permission to query, create, modify, and delete events.
EXECUTE
Stored Procedure
Permission to execute stored procedures.
ALTER ROUTINE
Stored Procedure
Permission to change stored procedures.
CREATE ROUTINE
Stored Procedure
Permission to create stored procedures.
CREATE TEMPORARY TABLES
Temporary table
Permission to create temporary tables.
ALTER
Tables and views
Permission to change tables or views.
DELETE
Table
Permission to delete data.
INDEX
Table
Index permission.
INSERT
Table/Column
Insertion permission.
SELECT
Table/Column
Query permission.
UPDATE
Table/Column
Update permission.
CREATE VIEW
View
Permission to create views.
TRIGGER
Trigger
Permission to create, delete, execute, and display triggers.
SHOW VIEW
View
Permission to check views.
PostgreSQL Database User Management
Prerequisites
• You have logged in to DMS.
• The target instance has been entered in DMS with the database type of PostgreSQL.
• The database user logged in to the instance has permission to manage the database user password.
Procedure
1. On the left menu bar, click Data Source Management > Instance Metadata to go to the Instance List page.
2. Select the target instance and click the Login button in the Operate menu bar.
3. Enter the user name and password for the database to finish logging in to the instance.
4. After logging in to the instance, click the More > Database User Management button.
Function Introduction
Database user management allows you to view all database accounts of the current instance, and create, delete, and edit the account.
Account List
This list displays all account information under the instance, including account name, permissions, password expiry time, comments, and more. A fuzzy search by account name is supported.
Creating an Account
1. Log in to DMS.
2. Click Data Source Management > Instance Metadata to go to the Instance List page.
3. On the Instance List page, click More > Database User Management to go to the Management page.
4. Click the Create Account button and enter the mandatory information such as account name and password.
5. Click the OK button to generate the DDL statement for creating the account.
6. In the DDL preview pop-up window, click the Execute button to finish creating the account.
7. You can set account options such as global permission, object permission, password expiration time, connection limit, comments, and more. The number of connections defaults to -1, indicating no limit.
Editing an Account
1. Log in to DMS.
2. Click Data Source Management > Instance Metadata to go to the Instance List page.
3. On the Instance List page, click More > Database User Management to go to the Management page.
4. Go to the Operate menu of the list and click the Edit button to enter the Edit page.
5. Modify the property content as required.
6. Click the OK button to generate the DDL statement for modifying the account.
7. In the DDL preview pop-up window, click the Execute button to finish modifying the account.
8. You can edit and modify account options such as account and password, global permission, object permission, password expiration time, connection limit, comments, and more.
² Note
Changing the user name is not supported.
Deleting an Account
1. Log in to DMS.
2. Click Data Source Management > Instance Metadata to go to the Instance List page.
3. On the Instance List page, click More > Database User Management to go to the Management page.
4. Go to the Operate menu of the list and click the Delete button. After re-confirmation, click the OK button to finish deleting the specified account.
PostgreSQL Global Permission
Permission Type
Description
Allowing Login
Login permissions, corresponding to the SQL commands LOGIN and NOLOGIN. It defaults to Allow Login.
Allowing Creating User
Permission to create database accounts, corresponding to the SQL commands CREATEROLE and NOCREATEROLE. It defaults to Allow User Creation.
Allowing Creating DB
Permission to create databases, corresponding to the SQL commands CREATEDB and NOCREATEDB. It defaults to allow database creation.