The usage of the account and permission system for Distributed Relational Database Service (DRDS) is consistent with that of the account and permission system for MySQL. DRDS supports
DROP USER, and
SET PASSWORD statements. DRDS allows you to grant permissions at the database and table levels, but does not allow you to grant permissions at the global or column level.
For information about the MySQL account and permission system, see MySQL documentation.
Note: Accounts created by executing the CREATE USER statement in DRDS only exist in the DRDS instance and are not synchronized to the backend ApsaraDB RDS for MySQL instances.
An account is specified by a user name and a hostname in the
username@'host' format. Accounts with the same user name but different hostnames are different accounts. For example,
email@example.com are different accounts and their passwords and permissions may be different.
After you create a database in the DRDS console, the system automatically creates two system accounts for the database: an administrator account and a read-only account. These two accounts are built-in accounts, which cannot be deleted. The permissions of these two accounts cannot be modified either.
- The administrator account name is the same as the database name. For example, if the database name is
easydb, the administrator account name is also
- The read-only account name consists the database name and a suffix
_RO. For example, if the database name is
easydb, the read-only account name is
Assume that the
andordb databases are available. Based on the preceding rules, the
dreamdb database has the administrator account
dreamdb and the read-only account
andordb database has the administrator account
andordb and the read-only account
- An administrator account has all permissions.
- Only the administrator account can create accounts and grant permissions. Other accounts can only be created and granted permissions by the administrator account.
- The administrator account is bound to a database and does not have any permission on other databases. It can only log on to the bound database, but cannot grant any permission on other databases to another account. For example, the administrator account easydb can only log on to the easydb database and grant only the permissions for the easydb database or the permissions for the tables in the easydb database to another account.
- A read-only account has only the permission to execute SELECT statements.
- User names are case sensitive.
- A user name must be 4 to 20 characters in length.
- A user name must start with a letter.
- A user name can contain letters and digits.
- A password must be 6 to 20 characters in length.
- A password can contain letters, digits, and special characters @ # $ % ^ & + =.
- A hostname must be an IP address and can contain a wildcard
%. An underscore (
_) indicates one character and a percent sign (
%) indicates zero or more characters. A hostname with a wildcard must be quoted with single quotations marks (‘), such as lily@’30.9. %.%’ and david@’%’.
- If two accounts can be used to log on to the same system, the account with the longest IP segment, excluding wildcards, prevails. For example, if accounts
david@'30.9.1%.234'are available in the system,
david@'30.9.12_.234'is used to log on to the host
- After you enable the Virtual Private Cloud (VPC) access feature for a host, the IP address of the host changes. To avoid invalid configurations in the account and permission system, set the hostname to ‘%’ to match any IP address.
- Global permissions: Not supported
- Database-level permissions: Supported
- Table-level permissions: Supported
- Column-level permissions: Not supported
- Subprogram-level permissions: Not supported
DRDS supports the following table-level basic permission items: CREATE, DROP, ALTER, INDEX, INSERT, DELETE, UPDATE, and SELECT.
- The TRUNCATE statement requires the table-level DROP permission.
- The REPLACE statement requires the table-level INSERT and DELETE permissions.
- The CREATE INDEX and DROP INDEX statements require the table-level INDEX permission.
- The CREATE SEQUENCE statement requires the database-level CREATE TABLE permission.
- The DROP SEQUENCE statement requires the database-level DROP TABLE permission.
- The ALTER SEQUENCE statement requires the database-level ALTER TABLE permission.
- The INSERT ON DUPLICATE UPDATE statement requires the table-level INSERT and UPDATE permissions.
- Permissions are bound to an account such as username@’host’, not a user name such as username.
- The system checks whether a table exists when a permission to manage the table is granted. An error occurs if the table does not exist.
- The following database permissions are sorted in descending order of levels: global permissions (not supported), database-level permissions, table-level permissions, and column-level permissions.
- A newly granted higher-level permission overwrites a lower-level permission. If you remove the higher-level permission, the lower-level permission is also removed.
- The USAGE privilege is not supported.
CREATE USER user_specification [, user_specification] ...
user_specification: user [ auth_option ]
auth_option: IDENTIFIED BY 'auth#string'
Examples:Create an account with the user name lily, the password 123456, and the host 220.127.116.11 from which the account can log on.
CREATE USER firstname.lastname@example.org IDENTIFIED BY '123456';
Create an account with the user name david, no password, and no specified host.
CREATE USER david@'%';
DROP USER user [, user] ...
Examples:Remove the account
DROP USER email@example.com;
SET PASSWORD FOR user = password_option
Examples:Change the password of the account
firstname.lastname@example.org to 123456.
SET PASSWORD FOR email@example.com = PASSWORD('123456')
priv_type[, priv_type] ...
TO user_specification [, user_specification] ...
[WITH GRANT OPTION]
user [ auth_option ]
IDENTIFIED BY 'auth#string'
NOTE: If the account specified in the GRANT statement does not exist and no IDENTIFIED BY information is provided, an error is returned. If the IDENTIFIED BY information is provided, the account is created and granted permissions.
Examples:In the easydb database, create an account named david, which can be used to log on from any host and has all the permissions on easydb.You can execute one statement to create the account first and then execute one statement to grant permissions to the account.
CREATE USER david@'%' IDENTIFIED BY 'your#password';
GRANT ALL PRIVILEGES ON easydb.* to david@'%';
Alternatively, you can execute only one statement to create the account and grant permissions to the account.
GRANT ALL PRIVILEGES ON easydb.* to david@'%' IDENTIFIED BY 'your#password';
In the easydb database, create an account named hanson, which can be used to log on from any host and has all the permissions on the easydb.employees table.
GRANT ALL PRIVILEGES ON easydb.employees to hanson@'%'
IDENTIFIED BY 'your#password';
In the easydb database, create an account named hanson, which can be used to log on only from 192.168.3.10 and has the INSERT and SELECT permissions on the easydb.emp table.
GRANT INSERT,SELECT ON easydb.emp to hanson@'192.168.3.10'
IDENTIFIED BY 'your#password';
In the easydb database, create a read-only account named actro, which can be used to log on from any host.
GRANT SELECT ON easydb.* to actro@'%' IDENTIFIED BY 'your#password';
You can delete a permission item of an account at a specific permission level. The permission level is specified by priv_level.
[, priv_type] ...
You can delete all the permission items of an account at the database and table levels.
REVOKE ALL PRIVILEGES, GRANT OPTION
FROM user [, user] ...
Examples:Delete the CREATE, DROP, and INDEX permissions for the easydb.emp table from the hanson@’%’ account.
REVOKE CREATE,DROP,INDEX ON easydb.emp FROM hanson@'%';
Delete all the permissions of the account
REVOKE ALL PRIVILEGES,GRANT OPTION FROM firstname.lastname@example.org;
Note: GRANT OPTION must be added to the statement to ensure that the statement is compatible with MySQL.
Since version 5.3.6, DRDS allows you to grant permissions for multiple databases to a single user.
You can create an account and grant permissions to the account on the Account Management page in the Alibaba Cloud DRDS console.
You can also execute SQL CREATE USER and GRANT statements to create an account and grant permissions to the account, respectively.
We recommend that you use the Alibaba Cloud DRDS console.
If you need to execute an SQL statement to grant permissions, note the following requirements:
Only an administrator account can create users and grant permissions to them.
An administrator account can only grant users permissions for databases bound to the administrator account. If the administrator of database A creates an account named new_user@'%' and the new_user user needs permissions for databases A and B, the administrator of database A and the administrator of database B must separately grant permissions to the user.
DRDS 5.3.6 and later allow you to grant permissions for multiple databases to a user. However, the use of the permissions has the following limits:
If the account new_user@’%’ has the SELECT and INSERT permissions for databases A and B, the new_user user must comply with the following rules:
If the user has logged on to database A and needs to query database B, the user must execute the following statement:
use B; SELECT * FROM table_in_B;
Cross-database queries such as SELECT * FROM B.table_in_B; are not supported.
If the user has logged on to database A and needs to write data to database B, the user must execute the following statement:
use B; INSERT INTO table_in_B VALUES('value');
Cross-database insert operations such as INSERT INTO B.table_in_B VALUES('value'); are not supported.
The preceding rules also apply to other SQL statements.
SHOW GRANTS[ FOR user@host];
Query all permissions
Versions earlier than 5.3.6: Execute the SHOW GRANTS statement.
Version 5.3.6 and later: The result of the SHOW GRANTS statement only displays the permissions of the current user. You can view all accounts and permissions in the DRDS console.
Query the permissions of an account
SHOW GRANTS FOR user@host;