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 to databases and tables. However, it does not offer global or column-level permission settings.
Create an account
Syntax
CREATE USER [IF NOT EXISTS] user IDENTIFIED BY 'password';Parameters
user is the account that consists of the username and hostname in the format of '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 from which the account can log on to a database. 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@'%'.
NoteThe "_" represents a single character, while the "%" represents 0 or more characters.
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
CREATE USER 'user1'@'127.0.0.1' IDENTIFIED BY '123456';
CREATE USER IF NOT EXISTS 'user2'@'%' identified by '123456';Change the password of an account
Syntax
SET PASSWORD FOR user = PASSWORD('auth_string')Example
SET PASSWORD FOR 'user1'@'127.0.0.1' = PASSWORD('654321');You cannot use SQL statements to change the password of a privileged account.
Delete an account
Syntax
DROP USER user;Example
DROP USER 'user2'@'%';You cannot use SQL statements to delete privileged accounts.
Grant permissions to an account
Syntax
GRANT privileges ON database.table TO user;Parameters
In the preceding statement, privileges indicates a specific 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. 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
GRANT SELECT,UPDATE ON `db1`.* TO 'user1'@'127.0.0.1';You cannot use SQL statements to authorize privileged accounts.
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
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 the permissions of 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' |
+----------------------------------------------+You cannot use SQL statements to reclaim privileged accounts.