Manage accounts and permissions
This topic describes how to manage accounts and permissions.
Accounts and permissions in PolarDB-X are managed in the same manner as in MySQL 5.7. PolarDB-X supports statements such as GRANT, REVOKE, SHOW GRANTS, CREATE USER, DROP USER, and SET PASSWORD. PolarDB-X allows you to grant permissions on databases and tables. PolarDB-X does not support permissions at the global or column level.
Create an account
Syntax:
CREATE USER [IF NOT EXISTS] user IDENTIFIED BY 'password';
An account name consists of a username and a hostname. The format is 'username'@'host'.
username specifies the username that you create. A username must meet the following requirements:
The username must be case-sensitive.
The username must be 4 to 20 characters in length.
The username must start with a letter.
The username can contain letters and digits.
host specifies the host on which the account can log. If the usernames of the accounts are the same and the hostnames are different, the accounts are different. A hostname must meet the following requirements:
The hostname must be a value that represents one or more IP addresses. The value 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 PolarDB-X match the logon user on a host, the account whose hostname contains the longer prefix is the logon account. The prefix of a hostname is the CIDR block that precedes the wildcards in the IP address of the host. For example, the david@'30.9.12.234' and david@'30.9.1%.234' accounts are available in the system. If you use the david username to log on to a database from the 30.9.127.234 host, the david@'30.9.12.234' account is used.
After Virtual Private Cloud (VPC) is activated, the IP addresses of hosts change. To prevent invalid account and permission configurations, we recommend that you set the hostname to '%' to match all IP addresses.
password specifies the password of an account. A password must meet the following requirements:
The password must be 6 to 20 characters in length.
The password can contain letters, digits, and the following special characters: @#$%^&+=
Example:
mysql> CREATE USER 'user1'@'127.0.0.1' IDENTIFIED BY '123456';
mysql> CREATE USER IF NOT EXISTS 'user2'@'%' identified by '123456';
Change the password of an account
Syntax:
SET PASSWORD FOR user = PASSWORD('auth_string')
Example:
mysql> SET PASSWORD FOR 'user1'@'127.0.0.1' = PASSWORD('654321');
Delete an account
Syntax:
DROP USER user;
Example:
mysql> DROP USER 'user2'@'%';
Grant permissions to an account
Syntax:
GRANT privileges ON database.table TO user;
In the preceding statement, privileges indicates the permission type. 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. PolarDB-X supports eight basic table permissions: 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.
Example:
mysql> GRANT SELECT,UPDATE ON `db1`.* TO 'user1'@'127.0.0.1';
View the permissions of an account
Syntax:
SHOW GRANTS [FOR user];
You can specify the current_user() function to view the username of the current user.
Example:
mysql> 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' |
+------------------------------------------------------+
mysql> 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 the permissions of an account
Syntax:
REVOKE privileges ON database.table TO user;
Example:
mysql> REVOKE UPDATE ON db1.* FROM 'user1'@'127.0.0.1';
mysql> 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' |
+----------------------------------------------+