PolarDB:Manage accounts and permissions

Create an account

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

The user parameter uses the format 'username'@'host'. Two accounts are different if they share the same username but have different hostnames.

Username requirements:

• 4–20 characters, starting with a letter

• Case-sensitive; can contain letters and digits

Hostname requirements:

• Represents one or more IP addresses

• Supports _ (matches one character) and % (matches zero or more characters)

• Hostnames with wildcards must be enclosed in single quotation marks, for example, lily@'30.9.%.%' and david@'%'

• When two accounts match the same login user, the account with the longer prefix (the more specific CIDR block before the wildcard) takes precedence. For example, if both david@'30.9.12_.234' and david@'30.9.1%.234' exist, logging in from 30.9.127.234 uses david@'30.9.12_.234'

• After Virtual Private Cloud (VPC) is activated, host IP addresses change. Set the hostname to % to match all IP addresses and avoid stale permission configurations

Password requirements:

• 6–20 characters

• Can contain letters, digits, and the following special characters: @#$%^&+=

Examples:

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

SET PASSWORD FOR user = PASSWORD('auth_string');

Example:

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

Passwords of privileged accounts cannot be changed using SQL statements.

Delete an account

DROP USER user;

Example:

DROP USER 'user2'@'%';

Privileged accounts cannot be deleted using SQL statements.

Grant permissions to an account

GRANT privileges ON database.table TO user;

PolarDB-X supports eight table-level permissions: CREATE, DROP, ALTER, INDEX, INSERT, DELETE, UPDATE, and SELECT. All grants operate at the database level or table level. Global-level and column-level grants are not supported.

For the permissions required for specific SQL operations, see Required permissions for SQL operations.

Example:

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

Permissions cannot be granted to privileged accounts using SQL statements.

View the permissions of an account

SHOW GRANTS [FOR user];

Examples:

SHOW GRANTS FOR 'user1'@'127.0.0.1';  -- show grants for a specific 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' |
+------------------------------------------------------+

SHOW GRANTS FOR current_user();  -- show grants for the currently logged-in 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

REVOKE privileges ON database.table FROM user;

Example:

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

After revoking UPDATE, the remaining grants for the account are:

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

Permissions cannot be revoked from privileged accounts using SQL statements.

Required permissions for SQL operations

The following table lists the permissions required to run specific SQL statements.