Manage role permissions

This topic describes the syntax for role permission management and provides examples on how to use the syntax.

For more information about role-based permission control for PolarDB-X that is compatible with native MySQL 8.0, see Role-based permission control.

Create a role

Syntax:

CREATE ROLE role [, role]...

The components of a role name are the same as the components of a username. Each role name consists of the values of the Name and Host parameters.

• The Name parameter cannot be left empty.

• The value of the Host parameter must meet the following rules:

  • The value of the Host parameter must be an IP address and can contain underscores (_) and percent signs (%). Underscores (_) and percent signs (%) are not used as wildcard characters.

  • If the Host parameter is left empty, a percent sign (%) is used to specify the host. Percent signs (%) can be queried by using the exact match method. Percent signs (%) are not used as wildcard characters.

Example:

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

Delete a role

Syntax:

DROP ROLE role [, role] ...

Example:

DROP ROLE 'role_ro'@'%';

Grant permissions to a role

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]

Additional information:

• Before the preceding statement is executed, the following requirements must be met:

  • The user has the CREATE_USER permission.

  • The user has the admin permission on the role.

• If the statement includes the WITH ADMIN OPTION option, the user to which the role is granted has the admin permission on the role.

• After the role is assigned to the user, the role must be activated. This way, the user can have the required permissions of the role. To specify the role that you want to activate, you must execute SET DEFAULT ROLE and SET ROLE statements.

Example:

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

Configure the default role

Syntax:

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

Before the preceding statement is executed, the following requirements must be met:

• The role in the statement is assigned to the specified user by executing the GRANT statement.

• The user is assigned the role or the user is granted the CREATE_USER permission.

Example:

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

Configure the role for the connection

Syntax:

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

Note

• If you execute the SET ROLE DEFAULT statement, the activated role is the role specified in the SET DEFAULT ROLE statement.

• The role activated by this syntax takes effect only for users who use the current connection.

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

-- Use the user1 account to perform the session.
SELECT CURRENT_ROLE();
+------------------+
| CURRENT_ROLE()   |
+------------------+
| 'role_write'@'%' |
+------------------+

Revoke a role

Revoke the permissions of 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 the permissions of 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' |
+----------------------------------------------+