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

PolarDB-X 1.0 uses the same account and permission model as MySQL. Supported statements include GRANT, REVOKE, SHOW GRANTS, CREATE USER, DROP USER, and SET PASSWORD.

Account model

Account format

An account consists of a username and a hostname, formatted as username@'host'. Two accounts with the same username but different hostnames are distinct accounts with independent passwords and permissions. For example, lily@30.9.73.96 and lily@30.9.73.100 are separate accounts.

Built-in accounts

After you create a database in the PolarDB-X 1.0 console, the system automatically creates two built-in accounts:

Account type	Naming pattern	Example (database `easydb`)
Administrator account	Same as the database name	`easydb`
Read-only account	Database name + `_RO`	`easydb_RO`

Built-in accounts cannot be deleted, and their permissions cannot be modified.

Accounts created with CREATE USER exist only in PolarDB-X 1.0 and are not synchronized to ApsaraDB RDS.

Account permissions

Administrator accounts have full permissions on their bound database. An administrator account is bound to one database and cannot grant permissions on other databases.
Only administrator accounts can create other accounts and grant permissions.
Read-only accounts have the SELECT permission only.

Naming and password requirements

Account names:

4–20 characters, case-sensitive
Must start with a letter
Can contain letters and digits only

Passwords:

6–20 characters
Can contain letters, digits, and these special characters: @#$%^&+=

Hostname matching

Hostnames support wildcards: _ matches exactly one character, and % matches zero or more characters. Enclose hostnames that contain wildcards in single quotation marks, for example lily@'30.9.%.%' or david@'%'.

When two accounts match the same login user and host, the account whose hostname has the longer prefix before the first wildcard takes precedence. For example, if david@'30.9.12_.xxx' and david@'30.9.1%.234' both exist and david logs in from 30.9.127.xxx, the match is david@'30.9.12_.xxx'.

Important

After Virtual Private Cloud (VPC) is activated, host IP addresses change. Set the hostname to '%' to avoid invalid account configurations.

Permission model

Supported permission levels

PolarDB-X 1.0 supports database-level and table-level permissions. Global permissions, column-level permissions, and subprogram-level permissions are not supported.

The following table maps permission levels to the ON clause syntax used in GRANT statements:

Permission level	`ON` clause syntax	Example
Database-level	`db_name.*`	`ON easydb.*`
Table-level	`db_name.tbl_name` or `tbl_name`	`ON easydb.employees`

Available permissions

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

The following table maps SQL operations to the permissions they require:

SQL operation	Required permission(s)
TRUNCATE	DROP (table-level)
REPLACE	INSERT + DELETE (table-level)
CREATE INDEX, DROP INDEX	INDEX (table-level)
CREATE SEQUENCE	CREATE (database-level)
DROP SEQUENCE	DROP (database-level)
ALTER SEQUENCE	ALTER (database-level)
INSERT ON DUPLICATE UPDATE	INSERT + UPDATE (table-level)

Permission rules

Permissions are bound to the full account identifier (username@'host'), not just the username.
When you grant permissions on a table, the table must already exist. If it does not exist, an error is returned.
A higher-level permission overwrites lower-level permissions. Removing a higher-level permission also removes any lower-level permissions under it.
The USAGE permission is not supported.

Grant permissions on multiple databases

From PolarDB-X 1.0 V5.3.6, a single account can hold permissions on multiple databases.

Option 1 (recommended): Use the account management page in the PolarDB-X 1.0 console to create an account and assign permissions.

Option 2: Use SQL statements. Note these constraints:

Only administrator accounts can create users and grant permissions.
An administrator account can only grant permissions on its own bound database. To grant new_user@'%' permissions on both Database A and Database B, use the administrator account of Database A to grant Database A permissions, then use the administrator account of Database B to grant Database B permissions.

Limitations when using a multi-database account: Cross-database queries and cross-database data insertion are not supported. When logged in to Database A, use USE to switch databases before accessing Database B:

-- Query data in Database B
USE B;
SELECT * FROM table_in_B;

-- Insert data into Database B
USE B;
INSERT INTO table_in_B VALUES('value');

SQL statement reference

Create an account

Syntax:

CREATE USER user_specification [, user_specification] ...
user_specification: user [ auth_option ]
auth_option: IDENTIFIED BY 'auth_string'

Examples:

Create lily@30.9.73.96 with password 123456. This account can only connect from 30.9.73.96:

CREATE USER lily@30.9.73.96 IDENTIFIED BY '123456';

Create david@'%' with no password. This account can connect from any host:

CREATE USER david@'%';

Delete an account

Syntax:

DROP USER user [, user] ...

Example:

DROP USER lily@30.9.73.96;

Change an account password

Syntax:

SET PASSWORD FOR user = PASSWORD('auth_string')

Example:

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

Grant permissions

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 [ IDENTIFIED BY 'auth_string' ]

If the account in the GRANT statement does not exist and no IDENTIFIED BY clause is included, an error is returned. If IDENTIFIED BY is included, the account is created and permissions are granted in one statement.

Examples:

Create david@'%' and grant full permissions on easydb:

-- Two-step: create account, then grant
CREATE USER david@'%' IDENTIFIED BY 'your#password';
GRANT ALL PRIVILEGES ON easydb.* TO david@'%';

-- One-step: create account and grant together
GRANT ALL PRIVILEGES ON easydb.* TO david@'%' IDENTIFIED BY 'your#password';

Grant full permissions on the easydb.employees table to hanson@'%':

GRANT ALL PRIVILEGES ON easydb.employees TO hanson@'%'
IDENTIFIED BY 'your#password';

Grant INSERT and SELECT on easydb.emp to hanson@192.168.3.10 only:

GRANT INSERT, SELECT ON easydb.emp TO hanson@'192.168.3.10'
IDENTIFIED BY 'your#password';

Create a read-only account actro@'%' for easydb:

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

Revoke permissions

Syntax:

Revoke specific permissions at a given level:

REVOKE
    priv_type [, priv_type] ...
    ON priv_level
    FROM user [, user] ...

Revoke all database-level and table-level permissions from an account:

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

GRANT OPTION must be included in the second form for MySQL compatibility.

Examples:

Revoke CREATE, DROP, and INDEX on easydb.emp from hanson@'%':

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

Revoke all permissions from lily@30.9.73.96:

REVOKE ALL PRIVILEGES, GRANT OPTION FROM lily@30.9.73.96;

View granted permissions

Syntax:

SHOW GRANTS [FOR user@host];

Example:

SHOW GRANTS FOR actro@'%';

In PolarDB-X 1.0 V5.3.6 and later, SHOW GRANTS returns permissions for the current account only. To view permissions for all accounts, go to the account management page in the PolarDB-X 1.0 console.