Manage accounts and permissions - PolarDB - Alibaba Cloud Documentation Center

The method to manage accounts and permissions in Distributed Relational Database Service (PolarDB-X 1.0) is the same as that in MySQL. DRDS supports statements such as GRANT, REVOKE, SHOW GRANTS, CREATE USER, DROP USER, and SET PASSWORD.

Accounts

Account description

An account name consists of a username and a hostname. The format is username@'host'. If two accounts have the same username but different hostnames, the accounts are each considered to be different accounts. For example, lily@30.9.73.96 and lily@30.9.73.100 are two different accounts. The passwords and permissions of the two accounts may be different.

After a database is created in the PolarDB-X 1.0 console, the system automatically creates two system accounts for the database: an administrator account and a read-only account. These accounts are built-in accounts. You cannot delete them or modify their permissions.

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 is the database name suffixed with _RO. For example, if the database name is easydb, the read-only account name is easydb_RO.

For example, two databases are created: dreamdb and andordb. The dreamdb database has an administrator account named dreamdb and a read-only account named dreamdb_RO. The andordb database has an administrator account named andordb and a read-only account named andordb_RO.

Note

Accounts created by executing CREATE USER statements in PolarDB-X 1.0 exist only in PolarDB-X 1.0. These accounts cannot be used in ApsaraDB RDS, so they are not synchronized to ApsaraDB RDS.

Account permissions

An administrator account has full permissions.
Only administrator accounts can be used to create other accounts and grant permissions to the created accounts.
An administrator account is bound to a database and does not have permissions on other databases. The administrator account can only be used to access the database that is bound to the account. You cannot use the administrator account to grant permissions on other databases to other accounts. For example, the easydb administrator account can be used to connect only to the easydb database and can grant permissions only on the easydb database or tables in the easydb database to other accounts.
A read-only account has only the SELECT permission.

Naming conventions

An account name is case-sensitive.
An account name must be 4 to 20 characters in length.
An account name must start with a letter.
An account name can contain letters and digits.

Password complexity requirements

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

Hostname matching rules

A hostname must be a value that represents one or more IP addresses. It can contain underscores (_) and wildcards (%). An underscore (_) represents a character and a wildcard (%) represents zero or more characters. Hostnames that contain wildcards must be enclosed in single quotation marks ('),such as lily@'30.9.%.%' and david@'%'.
If two accounts in DRDS match the logon user in a host, the account whose hostname contains the longer prefix is the logon account. The name prefix of a host is the IP segment that precedes the wildcards in the IP address of the host. For example, if the david@'30.9.12_.xxx' account and the david@'30.9.1%.234' account exist in DRDS and the david username is used to log on to the 30.9.127.xxx host, the logon account is david@'30.9.12_.xxx'.
After Virtual Private Cloud (VPC) is activated, the IP addresses of hosts change.
Important
To prevent invalid account and permission configurations, we recommend that you set the hostname to '%' to match all IP addresses.

Permissions

Support for permissions of different levels

Database-level permissions are supported.
Table-level permissions are supported.
Global permissions are not supported.
Column-level permissions are not supported.
Subprogram-level permissions are not supported.

Permission description

Eight basic table permissions are supported: CREATE, DROP, ALTER, INDEX, INSERT, DELETE, UPDATE, and SELECT.

To execute TRUNCATE statements on a table, you must have the DROP permission on the table.
To execute REPLACE statements on a table, you must have the INSERT and DELETE permissions on the table.
To execute CREATE INDEX and DROP INDEX statements, you must have the INDEX permission on the table.
To execute CREATE SEQUENCE statements, you must have the database-level CREATE permission.
To execute DROP SEQUENCE statements, you must have the database-level DROP permission.
To execute ALTER SEQUENCE statements, you must have the database-level ALTER permission.
To execute INSERT ON DUPLICATE UPDATE statements on a table, you must have the INSERT and UPDATE permissions on the table.

Permission rules

Permissions are bound to an account (username@'host') instead of a username (username).
When you grant permissions on a table to an account, the system checks whether the table exists. If the table does not exist, an error is reported.
The following database account permissions are listed by level in descending order: global permissions, database-level permissions, table-level permissions, and column-level permissions. Global permissions are not supported.
A granted higher-level permission overwrites lower-level permissions. If you remove the higher-level permission, the lower-level permissions are also removed.
The USAGE permission is not supported.

Grant permissions on multiple databases to an account

For PolarDB-X 1.0 V5.3.6 or later, the following methods can be used to grant a single account permissions on multiple databases:

In the Alibaba Cloud PolarDB-X 1.0 console, go to the account management page, create an account, and then grant the required permissions to the account. We recommend that you use this method.
Execute the CREATE USER statement to create an account, and then execute the GRANT statement to grant the required permissions to the account.
Note
If you want to execute SQL statements, pay attention to the following limits:
1. Only administrator accounts can be used to create users and grant them permissions.
2. An administrator account can only grant permissions on its bound database to other accounts. For example, you create an account named new_user@'%' by using the administrator account of Database A and want to grant the permissions on Database A and Database B to new_user. To meet this demand, you must use the administrator account of Database A to grant the permissions on Database A to new_user and use the administrator account of Database B to grant the permissions on Database B to new_user.

Use an account granted permissions on multiple databases

DRDS V5.3.6 or later allows you to grant a single account the permissions on multiple databases. For example, if an account named new_user@’%’ has the SELECT and INSERT permissions on Database A and Database B, pay attention to the following limits when you use this account:

If you log on to Database A by using the account and you want to query data in Database B, execute the use B; SELECT * FROM table_in_B; statement instead of the SELECT * FROM B.table_in_B; statement. This is because cross-database queries are not supported.
If you log on to Database A by using the account and you want to write data to Database B, execute the use B; INSERT INTO table_in_B VALUES('value'); statement, instead of the INSERT INTO B.table_in_B VALUES('value'); statement. This is because cross-database data insertion is not supported.
The same limits also apply to other SQL statements.

Related statements

CREATE USER used to create an account

Syntax

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

Examples
- Create an account named lily@30.9.73.96. The password of the account is 123456. lily is the username. The account can be used to log on to your database only from the host whose IP address is 30.9.73.96.
```
CREATE USER lily@30.9.73.96 IDENTIFIED BY '123456';        
```
- Create an account named david@'%'. This account has no password. david is the username. The account can be used to log on to your database from all hosts.
```
CREATE USER david@'%'; 
```

DROP USER used to delete an account

Syntax
```
DROP USER user [, user] ...    
```
Examples
Delete the lily@30.9.73.96 account.
```
DROP USER lily@30.9.73.96;       
```

SET PASSWORD used to change the password of an account

Syntax

SET PASSWORD FOR user = password_option

password_option: {
    PASSWORD('auth_string')
}

Examples

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

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

GRANT used to grant permissions to an account

Syntax
```
GRANT
    priv_type[, priv_type] ...
    ON priv_level
    TO user_specification [, user_specification] ...
    [WITH GRANT OPTION]
priv_level: {
  | db_name.*
  | db_name.tbl_name
  | tbl_name
}
user_specification:
    user [ auth_option ]
auth_option: {
    IDENTIFIED BY 'auth#string'
}
            
```
Note
If the account specified in the GRANT statement does not exist and no IDENTIFIED BY clause is used, an error message is returned. The error message indicates that the account does not exist. If the account specified in the GRANT statement does not exist but the IDENTIFIED BY clause is used, the specified account is created, and the permissions are granted.
Examples
- Create an account named david@'%' for the easydb database. david is the username. The account can be used to log on to the easydb database from all hosts and has full permissions on the easydb database.
```
#Method 1: Execute a statement to create an account, and then execute another statement to grant permissions to the account.
CREATE USER david@'%' IDENTIFIED BY 'your#password';
GRANT ALL PRIVILEGES ON easydb.* to david@'%';

#Method 2: Execute only one statement to create an account and grant permissions to the account.
GRANT ALL PRIVILEGES ON easydb.* to david@'%' IDENTIFIED BY 'your#password';
```
- Create an account named hanson@'%' for the easydb database. hanson is the username. The account can be used to log on to the easydb database from all hosts and has full permissions on the easydb.employees table.
```
GRANT ALL PRIVILEGES ON easydb.employees to hanson@'%' 
IDENTIFIED BY 'your#password';    
```
- Create an account named hanson@192.168.3.10 for the easydb database. hanson is the username. The account can be used to log on to the easydb database from only 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';
```
- Create a read-only account named actro@'%' for the easydb database. actro is the username. The account can be used to log on to the easydb database from all hosts.
```
GRANT SELECT ON easydb.* to actro@'%' IDENTIFIED BY 'your#password';          
```

REVOKE used to revoke permissions

Syntax
- Delete the permissions at a specific level from an account. The permission level is specified by priv_level.
```
REVOKE
priv_type
[, priv_type] ...
ON priv_level         
```
- Delete the permissions at the database level and the table level from an account.
```
REVOKE ALL PRIVILEGES, GRANT OPTION
FROM user [, user] ...           
```
Examples
- Delete the CREATE, DROP, and INDEX permissions on the easydb.emp table from the hanson@'%' account.
```
REVOKE CREATE,DROP,INDEX ON easydb.emp FROM hanson@'%';      
```
- Delete all permissions from the lily@30.9.73.96 account.
```
REVOKE ALL PRIVILEGES,GRANT OPTION FROM lily@30.9.73.96;       
```
  Note
  GRANT OPTION must be added to the preceding statement to ensure the compatibility with MySQL.

SHOW GRANTS used to query granted permissions

Syntax
```
SHOW GRANTS[ FOR user@host];           
```
Examples
```
SHOW GRANTS FOR user1@host;       
```

Note

In DRDS V5.3.6 and later, the SHOW GRANTS statement can be executed to query the permissions of only the current account. You can log on to the PolarDB-X 1.0 console to view the information about all accounts and permissions.