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' |
  +----------------------------------------------+

results matching ""

    No results matching ""