How to create an account and grant permissions-PolarDB(PolarDB)-阿里云帮助中心

This topic describes how to manage accounts and permissions.

The PolarDB-X account and permission system is compatible with MySQL 5.7. It supports statements such as GRANT, REVOKE, SHOW GRANTS, CREATE USER, DROP USER, and SET PASSWORD. You can grant permissions at the database level and table level. Global level and column level permissions are not supported.

Create an account

Syntax

CREATE USER [IF NOT EXISTS] user IDENTIFIED BY 'password';

Parameters

The user parameter specifies an account, which is a combination of a username and a hostname. The format is 'username'@'host'.

The username must follow these rules:
- Is case-sensitive.
- Must be 4 to 20 characters long.
- Must start with a letter.
- Can contain uppercase letters, lowercase letters, and digits.
The host specifies the host from which a user can log in. An account is uniquely identified by the combination of its username and hostname. The host must follow these rules:
- The host must be an IP address. You can use the _ and % wildcards. A host value that contains a wildcard must be enclosed in single quotation marks, for example, lily@'30.9.%.%' and david@'%'.
  
  Note
  _ matches a single character, and % matches zero or more characters.
- If two user accounts match a login attempt, the system uses the account with the longest prefix match. For example, if the system has two users, david@'30.9.12_.234' and david@'30.9.1%.234', a login attempt for david from the host 30.9.127.234 uses the david@'30.9.12_.234' account.
- The host IP address can change when you use a Virtual Private Cloud (VPC). To prevent account and permission configurations from becoming invalid, set the host to '%' to match any IP address.
The password parameter must meet the following requirements:
- Must be 6 to 20 characters long.
- Can contain uppercase letters, lowercase letters, digits, and the following special characters: @#$%^&+=.

Example

CREATE USER 'user1'@'127.0.0.1' IDENTIFIED BY '123456';

CREATE USER IF NOT EXISTS 'user2'@'%' identified by '123456';

Change account password

Syntax

SET PASSWORD FOR user = PASSWORD('auth_string')

Example

SET PASSWORD FOR 'user1'@'127.0.0.1' = PASSWORD('654321');

Note

You cannot use SQL statements to change a privileged account's password.

Delete an account

Syntax

DROP USER user;

Example

DROP USER 'user2'@'%';

Note

You cannot use SQL statements to delete a privileged account.

Grant permissions to an account

Syntax

GRANT privileges ON database.table TO user;

Parameters

The privileges parameter specifies the permission types. The permission levels, from highest to lowest, are: global level (not supported), database level, table level, and column level. PolarDB-X supports eight basic table-related permissions: CREATE, DROP, ALTER, INDEX, INSERT, DELETE, UPDATE, and SELECT.

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

Example

GRANT SELECT,UPDATE ON `db1`.* TO 'user1'@'127.0.0.1';

Note

You cannot use SQL statements to grant permissions to a privileged account.

View account permissions

Syntax

SHOW GRANTS [FOR user];

Note

You can use current_user() to view the current user.

Example

SHOW GRANTS FOR 'user1'@'127.0.0.1';
+------------------------------------------------------+
| GRANTS FOR 'USER1'@'127.0.0.1'                       |
+------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user1'@'127.0.0.1'            |
| GRANT SELECT, UPDATE ON db1.* TO 'user1'@'127.0.0.1' |
+------------------------------------------------------+

SHOW GRANTS FOR current_user();
+------------------------------------------------------+
| GRANTS FOR 'USER1'@'127.0.0.1'                       |
+------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user1'@'127.0.0.1'            |
| GRANT SELECT, UPDATE ON db1.* TO 'user1'@'127.0.0.1' |
+------------------------------------------------------+

Revoke permissions from an account

Syntax

REVOKE privileges ON database.table FROM user;

Example

REVOKE UPDATE ON db1.* FROM 'user1'@'127.0.0.1';

SHOW GRANTS FOR 'user1'@'127.0.0.1';
+----------------------------------------------+
| GRANTS FOR 'USER1'@'127.0.0.1'               |
+----------------------------------------------+
| GRANT USAGE ON *.* TO 'user1'@'127.0.0.1'    |
| GRANT SELECT ON db1.* TO 'user1'@'127.0.0.1' |
+----------------------------------------------+

Note

You cannot use SQL statements to revoke permissions from a privileged account.