Create and manage user accounts and permissions - PolarDB - Alibaba Cloud - PolarDB

The PolarDB-X account and permission system is compatible with MySQL and supports statements such as GRANT, REVOKE, SHOW GRANTS, CREATE USER, DROP USER, and SET PASSWORD.

Accounts

Account Permissions

An account is identified by a combination of a username and a hostname in the format username@'host'. Accounts with the same username but different hostnames are distinct. For example, lily@30.9.73.96 and lily@30.9.73.100 are two separate accounts and may have different passwords and permissions.

After you create a database in the PolarDB-X console, the system automatically creates two system accounts for that database: an administrator account and a read-only account. You cannot delete these built-in accounts or modify their permissions.

The administrator account name is the same as the database name. For example, if the database is named easydb, the administrator account is also named easydb.
The read-only account name is the database name suffixed with _RO. For example, if the database is named easydb, the read-only account is named easydb_RO.

For example, if you have two databases, 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 using the CREATE USER statement in PolarDB-X exist only within PolarDB-X. They are not related to ApsaraDB RDS and are not synchronized to backend ApsaraDB RDS instances.

Account rules

An administrator account has all permissions.
Only an administrator account can create other accounts and grant permissions.
An administrator account is bound to a specific database and has no permissions for other databases. When connecting, the account can only access its associated database. It cannot grant permissions for other databases. For example, the easydb administrator account can only connect to the easydb database and can only grant permissions on the easydb database or its tables to an account.
A read-only account has only the SELECT permission.

Naming conventions

Usernames are case-sensitive.
Contain 4 to 20 characters.
Must start with a letter.
Can contain uppercase letters, lowercase letters, and digits.

Password rules

Contain 6 to 20 characters.
Can contain uppercase letters, lowercase letters, digits, and the following special characters: @#$%^&+=.

Host matching rules

The host must be an IP address. You can use the _ and % wildcards. The _ wildcard matches a single character, and the % wildcard matches zero or more characters. A host that contains a wildcard must be enclosed in single quotation marks ('), for example, lily@'30.9.%.%' or david@'%'.
If a login attempt matches two accounts, the account with the longest matching IP address prefix takes precedence. The prefix is the part of the IP address before any wildcards. For example, if the system has two accounts, david@'30.9.12_.xxx' and david@'30.9.1%.234', a login attempt by the user david from host 30.9.127.xxx uses the david@'30.9.12_.xxx' account.
When VPC is enabled, the IP address of the host changes.
Important To prevent account and permission configurations from becoming invalid, set the HOST to '%' to match any IP address.

Permissions

Permission levels

database-level (Supported)
table-level (Supported)
global-level (Not yet supported)
column-level (Not yet supported)
subprogram-level (Not yet supported)

Supported permissions

PolarDB-X supports eight basic table-level permissions: CREATE, DROP, ALTER, INDEX, INSERT, DELETE, UPDATE, and SELECT.

The TRUNCATE statement requires the DROP permission on the table.
The REPLACE statement requires the INSERT and DELETE permissions on the table.
The CREATE INDEX and DROP INDEX statements require the INDEX permission on the table.
The CREATE SEQUENCE statement requires the CREATE permission at the database-level.
The DROP SEQUENCE statement requires the DROP permission at the database-level.
The ALTER SEQUENCE statement requires the ALTER permission at the database-level.
The INSERT ON DUPLICATE UPDATE statement requires both INSERT and UPDATE permissions on the table.

Permission rules

Permissions are bound to an account (username@'host'), not just a username.
When you grant a permission, the system verifies that the specified table exists. If it does not exist, an error is returned.
Permission levels are hierarchical, from highest to lowest: global-level (not yet supported), database-level, table-level, and column-level.
Granting a higher-level permission overrides any lower-level permissions. Revoking a higher-level permission also revokes its associated lower-level permissions.
The USAGE permission is not supported.

Granting multi-database permissions

In PolarDB-X 5.3.6 and later, you can grant permissions on multiple databases to a single account:

On the Account Management page in the Alibaba Cloud PolarDB-X console, create an account and grant permissions. This is the recommended method.
Alternatively, use the CREATE USER and GRANT SQL statements.
Note If you use SQL statements, note the following:
1. Only an administrator account can create users and grant permissions.
2. An administrator can only grant permissions on the database that they manage. For example, if the administrator of database A creates an account new_user@'%', and you want this account to access both database A and B, the administrator of A must grant permissions for A, and the administrator of B must grant permissions for B.

Using a multi-database account

In PolarDB-X 5.3.6 and later, you can grant an account permissions on multiple databases. Assume that an account such as new_user@'%' has SELECT and INSERT permissions on both database A and database B. The following limitations apply:

To query a table in database B while connected to database A, you must first switch databases by running USE B; SELECT * FROM table_in_B;. Cross-database queries such as SELECT * FROM B.table_in_B; are not supported.
To insert data into a table in database B while connected to database A, you must run USE B; INSERT INTO table_in_B VALUES('value');. Cross-database insertions such as INSERT INTO B.table_in_B VALUES('value'); are not supported.
The same principle applies to other SQL statements.

Statements

Create an account (CREATE USER)

Syntax

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

Examples
- Create an account named lily with the password 123456. This account can only connect from host 30.9.73.96.
```
CREATE USER lily@30.9.73.96 IDENTIFIED BY '123456';        
```
- Create an account named david that can connect from any host. This account has no password.
```
CREATE USER david@'%'; 
```

Drop an account (DROP USER)

Syntax
```
DROP USER user [, user] ...    
```
Example
Remove the account lily@30.9.73.96:
```
DROP USER lily@30.9.73.96;       
```

Change an account password (SET PASSWORD)

Syntax

SET PASSWORD FOR user = password_option

password_option: {
    PASSWORD('auth_string')
}

Example

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

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

Grant permissions to an account (GRANT)

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 in a GRANT statement does not exist, the statement fails unless you include an IDENTIFIED BY clause. If an IDENTIFIED BY clause is provided, the system creates the account and grants the specified permissions.

Examples

For the easydb database, create an account named david that can connect from any host and has all permissions on the database.

# Method 1: Create the account, then grant permissions.
CREATE USER david@'%' IDENTIFIED BY 'your#password';
GRANT ALL PRIVILEGES ON easydb.* to david@'%';

# Method 2: Create the account and grant permissions in a single statement.
GRANT ALL PRIVILEGES ON easydb.* to david@'%' IDENTIFIED BY 'your#password';

For the easydb database, create an account named hanson that can connect from any host and has all permissions on the easydb.employees table.
```
GRANT ALL PRIVILEGES ON easydb.employees to hanson@'%' 
IDENTIFIED BY 'your#password';    
```
For the easydb database, create an account named hanson that can only connect from host 192.168.3.10 and has 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';
```
For the easydb database, create a read-only account named actro that can connect from any host.
```
GRANT SELECT ON easydb.* to actro@'%' IDENTIFIED BY 'your#password';          
```

Revoke permissions (REVOKE)

Syntax
- Revokes specific permissions from an account at a permission level specified by priv_level.
```
REVOKE
priv_type
[, priv_type] ...
ON priv_level         
```
- Revokes all database-level and table-level permissions from a user.
```
REVOKE ALL PRIVILEGES, GRANT OPTION
FROM user [, user] ...           
```
Examples
- Revoke the CREATE, DROP, and INDEX permissions on the easydb.emp table from the account hanson@'%'.
```
REVOKE CREATE,DROP,INDEX ON easydb.emp FROM hanson@'%';      
```
- Revoke all permissions from the account lily@30.9.73.96.
```
REVOKE ALL PRIVILEGES,GRANT OPTION FROM lily@30.9.73.96;       
```
  Note To ensure compatibility with MySQL, you must include the GRANT OPTION clause.

Show grants (SHOW GRANTS)

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

Note In PolarDB-X 5.3.6 and later, the SHOW GRANTS statement only displays the permissions for the current user. To view information about all accounts and permissions, use the Alibaba Cloud PolarDB-X console.