All Products
Search
Document Center

Account and permission system

Last Updated: Aug 17, 2020

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 GRANT, REVOKE, SHOW GRANTS, CREATE USER, 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.

Accounts

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, lily@30.9.73.96 and lily@30.9.73.100 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 easydb.
  • 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 easydb_RO.

Assume that the dreamdb and andordb databases are available. Based on the preceding rules, the dreamdb database has the administrator account dreamdb and the read-only account dreamdb_RO. The andordb database has the administrator account andordb and the read-only account andordb_RO.

Account permission rules

  • 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 name rules

  • 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.

Password rules

  • A password must be 6 to 20 characters in length.
  • A password can contain letters, digits, and special characters @ # $ % ^ & + =.

Hostname matching rules

  • A hostname must be an IP address and can contain a wildcard _ or %. 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.12_.234' and david@'30.9.1%.234' are available in the system, david@'30.9.12_.234' is used to log on to the host 30.9.127.234 as david.
  • 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.

Permissions

Support for granting permissions at different levels

  • Global permissions: Not supported
  • Database-level permissions: Supported
  • Table-level permissions: Supported
  • Column-level permissions: Not supported
  • Subprogram-level permissions: Not supported

Permission items

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.

Permission rules

  • 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.

Operation commands

CREATE USER statement

Syntax:

  1. CREATE USER user_specification [, user_specification] ...
  2. user_specification: user [ auth_option ]
  3. auth_option: IDENTIFIED BY 'auth#string'

Examples:Create an account with the user name lily, the password 123456, and the host 30.9.73.96 from which the account can log on.

  1. CREATE USER lily@30.9.73.96 IDENTIFIED BY '123456';

Create an account with the user name david, no password, and no specified host.

  1. CREATE USER david@'%';

DROP USER statement

Syntax:

  1. DROP USER user [, user] ...

Examples:Remove the account lily@30.9.73.96.

  1. DROP USER lily@30.9.73.96;

SET PASSWORD statement

Syntax:

  1. SET PASSWORD FOR user = password_option
  2. password_option: {
  3. PASSWORD('auth_string')
  4. }

Examples:Change the password of the account lily@30.9.73.96 to 123456.

  1. SET PASSWORD FOR lily@30.9.73.96 = PASSWORD('123456')

GRANT statement

Syntax:

  1. GRANT
  2. priv_type[, priv_type] ...
  3. ON priv_level
  4. TO user_specification [, user_specification] ...
  5. [WITH GRANT OPTION]
  6. priv_level: {
  7. | db_name.*
  8. | db_name.tbl_name
  9. | tbl_name
  10. }
  11. user_specification:
  12. user [ auth_option ]
  13. auth_option: {
  14. IDENTIFIED BY 'auth#string'
  15. }

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.

  1. CREATE USER david@'%' IDENTIFIED BY 'your#password';
  2. GRANT ALL PRIVILEGES ON easydb.* to david@'%';

Alternatively, you can execute only one statement to create the account and grant permissions to the account.

  1. 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.

  1. GRANT ALL PRIVILEGES ON easydb.employees to hanson@'%'
  2. 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.

  1. GRANT INSERT,SELECT ON easydb.emp to hanson@'192.168.3.10'
  2. 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.

  1. GRANT SELECT ON easydb.* to actro@'%' IDENTIFIED BY 'your#password';

REVOKE statement

Syntax:

Delete a permission of an account

You can delete a permission item of an account at a specific permission level. The permission level is specified by priv_level.

  1. REVOKE
  2. priv_type
  3. [, priv_type] ...
  4. ON priv_level
Delete all permissions of an account

You can delete all the permission items of an account at the database and table levels.

  1. REVOKE ALL PRIVILEGES, GRANT OPTION
  2. FROM user [, user] ...

Examples:Delete the CREATE, DROP, and INDEX permissions for the easydb.emp table from the hanson@’%’ account.

  1. REVOKE CREATE,DROP,INDEX ON easydb.emp FROM hanson@'%';

Delete all the permissions of the account lily@30.9.73.96.

  1. REVOKE ALL PRIVILEGES,GRANT OPTION FROM lily@30.9.73.96;

Note: GRANT OPTION must be added to the statement to ensure that the statement is compatible with MySQL.

Grant permissions for multiple databases to a user

Since version 5.3.6, DRDS allows you to grant permissions for multiple databases to a single user.

Methods:

  1. You can create an account and grant permissions to the account on the Account Management page in the Alibaba Cloud DRDS console.
  2. You can also execute SQL CREATE USER and GRANT statements to create an account and grant permissions to the account, respectively.
  3. 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:

  1. Only an administrator account can create users and grant permissions to them.
  2. 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.

Limits for users with permissions for multiple database

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:

  1. If the user has logged on to database A and needs to query database B, the user must execute the following statement:
  2. use B; SELECT * FROM table_in_B;
  3. Cross-database queries such as SELECT * FROM B.table_in_B; are not supported.
  1. If the user has logged on to database A and needs to write data to database B, the user must execute the following statement:
  2. use B; INSERT INTO table_in_B VALUES('value');
  3. Cross-database insert operations such as INSERT INTO B.table_in_B VALUES('value'); are not supported.
  1. The preceding rules also apply to other SQL statements.

SHOW GRANTS statement

Syntax:

  1. SHOW GRANTS[ FOR user@host];

Query all permissions

  1. Versions earlier than 5.3.6: Execute the SHOW GRANTS statement.
  2. 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

  1. SHOW GRANTS FOR user@host;