Configure role-based permission control - PolarDB - Alibaba Cloud - PolarDB

This topic describes the syntax for managing roles and permissions and provides relevant examples.

PolarDB-X is compatible with the role-based access control of native MySQL 8.0. For more information, see the MySQL documentation on role-based access control.

Create a role

Syntax:

CREATE ROLE role [, role]...

A role, like a user, consists of a name and a host.

The name cannot be empty.
The host must comply with the following rules:
- The host can be an IP address or a hostname. The percent sign (%) and underscore (_) characters function as wildcards.
- If the host is not specified, it defaults to '%', which acts as a wildcard that matches any host.

Example:

CREATE ROLE 'role_ro'@'%', 'role_write';

Drop a role

Syntax:

DROP ROLE role [, role] ...

Example:

DROP ROLE 'role_ro'@'%';

Grant roles

Grant permissions to a role

Syntax:

GRANT priv_type [, priv_type] ... ON priv_level TO role [, role]... [WITH GRANT OPTION]

Example:

GRANT ALL PRIVILEGES ON db1.* TO 'role_write';

Grant a role to a user

Syntax:

GRANT role [, role] ...
    TO user_or_role [, user_or_role] ...
    [WITH ADMIN OPTION]

Notes:

To run this command, you must meet one of the following conditions:
- The current user has the CREATE_USER permission.
- The current user has been granted the role with the ADMIN OPTION.
If you include the WITH ADMIN OPTION clause, the grantee can then grant the role to other users.
Granting a role to a user does not automatically activate its permissions. You must use SET DEFAULT ROLE or SET ROLE to activate the role.

Example:

GRANT 'role_write' TO 'user1'@'127.0.0.1';

Set the default role

Syntax:

SET DEFAULT ROLE
    {NONE | ALL | role [, role ] ...}
    TO user [, user ] ...

To run this command, you must meet one of the following conditions:

The specified roles must have already been granted to the target user.
You are the target user, or you have the CREATE_USER permission.

Example:

SET DEFAULT ROLE 'role_write' TO 'user1'@'127.0.0.1';

Set session role

Syntax:

SET ROLE {
    DEFAULT
  | NONE
  | ALL
  | ALL EXCEPT role [, role ] ...
  | role [, role ] ...
}

Note

If you run the SET ROLE DEFAULT statement, the active roles are those specified in the SET DEFAULT ROLE statement.
Roles activated by this syntax are effective only for the current session.

Example:

SET ROLE 'role_write';

View role permissions

Syntax:

SHOW GRANTS
    [FOR user_or_role
        [USING role [, role] ...]]

Example:

SHOW GRANTS FOR 'role_write'@'%';
+---------------------------------------------------+
| GRANTS FOR 'ROLE_WRITE'@'%'                       |
+---------------------------------------------------+
| GRANT USAGE ON *.* TO 'role_write'@'%'            |
| GRANT ALL PRIVILEGES ON db1.* TO 'role_write'@'%' |
+---------------------------------------------------+

SHOW GRANTS FOR 'user1'@'127.0.0.1' USING 'role_write';
+------------------------------------------------------+
| GRANTS FOR 'USER1'@'127.0.0.1'                       |
+------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user1'@'127.0.0.1'            |
| GRANT ALL PRIVILEGES ON db1.* TO 'user1'@'127.0.0.1' |
| GRANT 'role_write'@'%' TO 'user1'@'127.0.0.1'        |
+------------------------------------------------------+

-- Run in a session as user1
SELECT CURRENT_ROLE();
+------------------+
| CURRENT_ROLE()   |
+------------------+
| 'role_write'@'%' |
+------------------+

Revoke roles

Revoke permissions from a role

Syntax:

REVOKE priv_type [, priv_type] ... ON priv_level FROM role [, role]...

Example:

REVOKE ALL PRIVILEGES ON db1.* FROM 'role_write';

SHOW GRANTS FOR 'role_write'@'%';
+----------------------------------------+
| GRANTS FOR 'ROLE_WRITE'@'%'            |
+----------------------------------------+
| GRANT USAGE ON *.* TO 'role_write'@'%' |
+----------------------------------------+

Revoke a role from a user

Syntax:

REVOKE role [, role ] ... FROM user_or_role [, user_or_role ] ...

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 ON db1.* TO 'user1'@'127.0.0.1'  |
| GRANT 'role_write'@'%' TO 'user1'@'127.0.0.1' |
+-----------------------------------------------+

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