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

Operations at a glance

Operation	Statement
Create a role	`CREATE ROLE`
Delete a role	`DROP ROLE`
Grant privileges to a role	`GRANT ... ON ... TO role`
Assign a role to a user	`GRANT role TO user`
Set the default role for a user	`SET DEFAULT ROLE`
Activate a role for the current session	`SET ROLE`
View grants for a role or user	`SHOW GRANTS`
Revoke privileges from a role	`REVOKE ... ON ... FROM role`
Remove a role assignment from a user	`REVOKE role FROM user`

Create a role

CREATE ROLE role [, role]...

A role name has two parts: a Name and a Host.

Name: Required. Cannot be blank.
Host: Must be an IP address. Can contain underscores (_) and percent signs (%), but neither is treated as a wildcard character. If left blank, % is used as the host value. Query % using exact match—it is not expanded as a wildcard.

Example:

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

Delete a role

DROP ROLE role [, role] ...

Example:

mysql> DROP ROLE 'role_ro'@'%';

Grant privileges to a role

Use this syntax to assign database privileges (such as SELECT or ALL PRIVILEGES) directly to a role. This form includes an ON clause to specify the privilege level.

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

Example:

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

Assign a role to a user

Use this syntax to assign a role to a user.

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

Requirements:

The executing account must have the CREATE_USER privilege.
The executing account must have admin permission on the role being assigned.
Adding WITH ADMIN OPTION grants the recipient admin permission on the assigned role.

Example:

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

Important

Assigning a role does not automatically activate it. The user's session will not gain the role's privileges until the role is activated. Use SET DEFAULT ROLE and SET ROLE to activate it (see the sections below).

Set the default role for a user

SET DEFAULT ROLE specifies which roles are automatically active when a user connects.

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

Requirements:

The specified role must already be assigned to the user via GRANT.
The user must be assigned the role, or the user must have the CREATE_USER privilege.

Example:

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

Activate a role for the current session

SET ROLE changes which roles are active in the current connection. Changes do not persist after the session ends.

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

SET ROLE DEFAULT activates the roles configured by SET DEFAULT ROLE. The activated roles apply only to the current connection.

Example:

mysql> SET ROLE 'role_write';

To verify which roles are currently active, run SELECT CURRENT_ROLE():

mysql> SELECT CURRENT_ROLE();
+------------------+
| CURRENT_ROLE()   |
+------------------+
| 'role_write'@'%' |
+------------------+

View grants

SHOW GRANTS displays privileges and role assignments for a user or role. Add the USING clause to expand a role's privileges inline in the output.

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

Example — view grants for a role:

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

Example — view grants for a user, with role privileges expanded:

mysql> 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 as user1
mysql> SELECT CURRENT_ROLE();
+------------------+
| CURRENT_ROLE()   |
+------------------+
| 'role_write'@'%' |
+------------------+

Revoke privileges from a role

Use this syntax to remove specific privileges from a role.

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

Example:

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

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

Remove a role assignment from a user

Use this syntax to unassign a role from a user.

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

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

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