MySQL tutorial Chapter 05 accounts and permissions

Keywords: Database MySQL SQL

When the client connects to the MySQL server, it must provide valid identity authentication, such as user name and password. When the user performs any database operation, the server will verify whether the user has corresponding permissions. For example, SELECT permission is required for query tables and DROP permission is required for deleting objects.

In order to facilitate the management of user permissions, MySQL 8.0 provides the function of Role. A Role is a collection of permissions.

In this article, we discuss the management of accounts and permissions in MySQL.

5.1 managing users

5.1.1 create user

MySQL uses the CREATE USER statement to create a user. The basic syntax is as follows:

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

Where, account_name is the account name; The account name is divided into two parts: user_name and host_name. Use% connection. IDENTIFIED BY specifies the password of the user. IF NOT EXISTS is used to avoid error messages when creating duplicate accounts.

The following statement creates a new user dev01, which can log in locally (localhost):

mysql> CREATE USER dev01@localhost IDENTIFIED BY 'Dev01@mysql';
Query OK, 0 rows affected (0.21 sec)

The account in MySQL is determined by the user name and host name. Dev01 on the host office.example.com and dev01 on the host home.example.com are two accounts. If you do not specify a host name, users can log in from any host:

user_name
user_name@%

%Is a wildcard that represents any string; In addition_ Represents any single character.

If the user name or host name contains special characters, such as spaces or -, you need to use quotation marks to refer to these two parts respectively:

'user-name'@'host-name'

In addition to single quotation marks, you can also use back quotation marks (`) or double quotation marks (").

The account information in MySQL is stored in the user table of the system database mysql:

mysql> select host, user from mysql.user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| localhost | dev01            |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
5 rows in set (0.00 sec)

Except dev01@localhost In addition, the other four users are system users created by initialization.

In addition to the basic syntax, you can specify more options when creating users:

resource_option: {
    MAX_QUERIES_PER_HOUR count
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count
}

resource_option is used to limit the user's use of system resources:

  • MAX_QUERIES_PER_HOUR, the number of queries allowed to be executed per hour. The default is 0, which means there is no limit;
  • MAX_UPDATES_PER_HOUR, the number of updates allowed per hour. The default is 0, which means there is no limit;
  • MAX_CONNECTIONS_PER_HOUR, the number of connections allowed per hour. The default is 0, which means there is no limit;
  • max_user_connections, the number of concurrent connections of the user. The default is 0, which means there is no limit. At this time, the number of concurrent connections of the user is determined by the system variable max_user_connections.

The following statement creates a new account dev02, which allows login from any host. At the same time, the user is limited to 1000 queries and 100 updates per hour:

mysql> CREATE USER 'dev02'@'%'
    -> WITH MAX_QUERIES_PER_HOUR 1000 MAX_UPDATES_PER_HOUR 100;
Query OK, 0 rows affected (0.01 sec)

Note that - > in the second line is the prompt of the client, not the input. Query the system user table to display the above settings:

mysql> select host, user, max_questions, max_updates from mysql.user;
+-----------+------------------+---------------+-------------+
| host      | user             | max_questions | max_updates |
+-----------+------------------+---------------+-------------+
| %         | dev02            |          1000 |         100 |
| localhost | dev01            |             0 |           0 |
| localhost | mysql.infoschema |             0 |           0 |
| localhost | mysql.session    |             0 |           0 |
| localhost | mysql.sys        |             0 |           0 |
| localhost | root             |             0 |           0 |
+-----------+------------------+---------------+-------------+
6 rows in set (0.00 sec)

The following are password management options:

password_option: {
    PASSWORD EXPIRE [DEFAULT | NEVER | INTERVAL N DAY]
  | PASSWORD HISTORY {DEFAULT | N}
  | PASSWORD REUSE INTERVAL {DEFAULT | N DAY}
  | PASSWORD REQUIRE CURRENT [DEFAULT | OPTIONAL]
}

Password management options can be used to set password expiration policy, reuse policy and verification when modifying password:

  • Password exit: set the password to expire immediately; password exit default: use the global password expiration policy, which is determined by the system variable default_password_lifetime; password exit never: the password will never expire; password exit international N day: the password will expire every N days;
  • PASSWORD HISTORY DEFAULT, which uses the global password reuse policy and is determined by the system variable password_history; PASSWORD HISTORY N, the new password and the last N passwords cannot be repeated;
  • PASSWORD REUSE INTERVAL DEFAULT, which uses the global password reuse policy (specified according to the time interval) and is determined by the system variable password_reuse_interval; PASSWORD REUSE INTERVAL N DAY, the new password and the password in the last N days cannot be repeated;
  • PASSWORD REQUIRE CURRENT, the user needs to enter the current password when modifying the password; PASSWORD REQUIRE CURRENT OPTIONAL, the user does not need to enter the current password when modifying the password; PASSWORD REQUIRE CURRENT DEFAULT, the global policy is used, which is determined by the system variable password_require_current.

The password options of the account can also be viewed through the mysql.user table:

mysql> select host,user,
    -> password_expired, password_last_changed,
    -> password_lifetime, password_reuse_history,
    -> password_reuse_time, password_require_current
    -> from mysql.user;
+-----------+------------------+------------------+-----------------------+-------------------+------------------------+---------------------+--------------------------+
| host      | user             | password_expired | password_last_changed | password_lifetime | password_reuse_history | password_reuse_time | password_require_current |
+-----------+------------------+------------------+-----------------------+-------------------+------------------------+---------------------+--------------------------+
| %         | dev02            | N                | 2019-09-23 15:02:47   |              NULL |                   NULL |                NULL | NULL                     |
| localhost | dev01            | N                | 2019-09-23 14:23:39   |              NULL |                   NULL |                NULL | NULL                     |
| localhost | mysql.infoschema | N                | 2019-08-28 10:07:39   |              NULL |                   NULL |                NULL | NULL                     |
| localhost | mysql.session    | N                | 2019-08-28 10:07:39   |              NULL |                   NULL |                NULL | NULL                     |
| localhost | mysql.sys        | N                | 2019-08-28 10:07:39   |              NULL |                   NULL |                NULL | NULL                     |
| localhost | root             | N                | 2019-08-28 10:07:44   |              NULL |                   NULL |                NULL | NULL                     |
+-----------+------------------+------------------+-----------------------+-------------------+------------------------+---------------------+--------------------------+
6 rows in set (0.00 sec)

The following are account lockout options:

lock_option: {
    ACCOUNT LOCK
  | ACCOUNT UNLOCK
}

This option is used to specify whether to lock the account. The locked account cannot be used. The default is ACCOUNT UNLOCK, which does not lock the account.

5.1.2 modify user

The ALTER USER statement can modify the user's attributes. The options for modifying a user are the same as those for creating a user.

First, modify the user's password. The following statement is used to modify the password of user dev01:

mysql> ALTER USER dev01@localhost IDENTIFIED BY 'Dev01@2019';
Query OK, 0 rows affected (0.25 sec)

MySQL provides the RENAME USER statement to modify the user name:

mysql> RENAME USER dev02 TO dev03;
Query OK, 0 rows affected (0.26 sec)

User dev02 is renamed dev03.

The RENAME USER statement automatically grants the permissions of the old user to the new user, but does not automatically resolve object dependencies on the old user. For example, when a stored procedure is defined by the old user name and runs with the definer permissions, an error will be generated.

Another common user modification operation is to lock and unlock the account:

mysql> ALTER USER dev01@localhost ACCOUNT LOCK;
Query OK, 0 rows affected (0.13 sec)

User dev01 is locked and cannot be used to connect at this time:

"C:\Program Files\MySQL\MySQL Server 8.0\bin\mysql.exe" -u dev01 -p
Enter password: **********
ERROR 3118 (HY000): Access denied for user 'dev01'@'localhost'. Account is locked.

The system variable locked_connections is used to record the number of login attempts to lock the account:

mysql> SHOW GLOBAL STATUS LIKE 'Locked_connects';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| Locked_connects | 1     |
+-----------------+-------+
1 row in set (0.00 sec)

Finally, we will unlock dev01:

mysql> ALTER USER dev01@localhost ACCOUNT UNLOCK;
Query OK, 0 rows affected (0.10 sec)

5.1.3 delete user

The DROP USER statement is used to delete a user. The following statement will delete dev03:

mysql> DROP USER dev03;
Query OK, 0 rows affected (0.14 sec)

If the deleted user has connected to the MySQL server, the user can continue to perform the operation; however, a new connection cannot be established.

5.2 management authority

The newly created user has only use permission by default and can only connect to the database without any operation permission. Use the SHOW GRANTS command to view the user's permissions:

mysql> SHOW GRANTS FOR dev01@localhost;
+-------------------------------------------+
| Grants for dev01@localhost                |
+-------------------------------------------+
| GRANT USAGE ON *.* TO `dev01`@`localhost` |
+-------------------------------------------+
1 row in set (0.00 sec)

GRANT statements can be used to GRANT permissions to users.

5.2.1 authorization

The basic syntax of GRANT statement is as follows:

GRANT privilege, ... 
ON privilege_level 
TO account_name;

GRANT statement supports granting multiple permissions at a time, separated by commas.

privilege_level specifies the role level of permissions, including:

  • The global permission applies to all databases in the MySQL server. The global permission is represented by *. *, for example, the following statement grants dev01@localhost User permission to query all tables in all databases:

    mysql> GRANT SELECT
       -> ON *.*
       -> TO dev01@localhost;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> SHOW GRANTS FOR dev01@localhost;
    +--------------------------------------------+
    | Grants for dev01@localhost                 |
    +--------------------------------------------+
    | GRANT SELECT ON *.* TO `dev01`@`localhost` |
    +--------------------------------------------+
    1 row in set (0.00 sec)

    The global permissions are stored in the mysql.user table.

  • Database permissions, which apply to all objects in the specified database. Database permissions are represented by db_name. *, for example, granted by the following statement dev01@localhost User's permission to query all tables in the database world:

    mysql> GRANT ALL
        -> ON world.*
        -> TO dev01@localhost;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> SHOW GRANTS FOR dev01@localhost;
    +----------------------------------------------------------+
    | Grants for dev01@localhost                               |
    +----------------------------------------------------------+
    | GRANT SELECT ON *.* TO `dev01`@`localhost`               |
    | GRANT ALL PRIVILEGES ON `world`.* TO `dev01`@`localhost` |
    +----------------------------------------------------------+
    2 rows in set (0.00 sec)

    Database permissions are stored in the mysql.db table.

  • Table permission, which applies to all columns of the specified table. Database permission is represented by db_name.table_name; if db_name is not specified, the default database is used; if there is no default database, an error will be returned. For example, the following statement grants dev01@localhost Permission to add, delete, modify and query the country table in the user database world:

    mysql> GRANT SELECT, INSERT, UPDATE, DELETE
        -> ON world.country
        -> TO dev01@localhost;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> SHOW GRANTS FOR dev01@localhost;
    +----------------------------------------------------------------------------------+
    | Grants for dev01@localhost                                                       |
    +----------------------------------------------------------------------------------+
    | GRANT SELECT ON *.* TO `dev01`@`localhost`                                       |
    | GRANT ALL PRIVILEGES ON `world`.* TO `dev01`@`localhost`                         |
    | GRANT SELECT, INSERT, UPDATE, DELETE ON `world`.`country` TO `dev01`@`localhost` |
    +----------------------------------------------------------------------------------+
    3 rows in set (0.00 sec)

    Table permissions are stored in the mysql.tables_priv table.

    • Column permissions that apply to the specified columns of the specified table. Each column permission needs to specify a specific column name. For example, the following statement grants dev01@localhost The user's query permission for the code and name fields in the world.country table and the modification permission for the population field:
    mysql> GRANT SELECT(code, name), UPDATE(population)
        -> ON world.country
        -> TO dev01@localhost;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> SHOW GRANTS FOR dev01@localhost;
    +----------------------------------------------------------------------------------------------------------------------------------+
    | Grants for dev01@localhost                                                                                                       |
    +----------------------------------------------------------------------------------------------------------------------------------+
    | GRANT SELECT ON *.* TO `dev01`@`localhost`                                                                                       |
    | GRANT ALL PRIVILEGES ON `world`.* TO `dev01`@`localhost`                                                                         |
    | GRANT SELECT, SELECT (`code`, `name`), INSERT, UPDATE, UPDATE (`population`), DELETE ON `world`.`country` TO `dev01`@`localhost` |
    +----------------------------------------------------------------------------------------------------------------------------------+
    3 rows in set (0.00 sec)

    Column permissions are stored in mysql.columns_priv table.

    • Storage routine permissions, which act on storage routines (functions and procedures). Storage routine permissions can be specified based on global, database, or individual routines. The following statement grants dev01@localhost User's permission to create storage routines in the database world.country:
    mysql> GRANT CREATE ROUTINE
        -> ON world.*
        -> TO dev01@localhost;
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> SHOW GRANTS FOR dev01@localhost;
    +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Grants for dev01@localhost                                                                                                                                                                                             |
    +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | GRANT SELECT ON *.* TO `dev01`@`localhost`                                                                                                                                                                             |
    | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, ALTER ROUTINE, EVENT, TRIGGER ON `world`.* TO `dev01`@`localhost` |
    | GRANT SELECT, SELECT (`code`, `name`), INSERT, UPDATE, UPDATE (`population`), DELETE ON `world`.`country` TO `dev01`@`localhost`                                                                                       |
    +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    3 rows in set (0.00 sec)

    The stored routine permissions are stored in mysql.procs_priv table.

    • Delegate user permissions that allow users to act as proxies for other users. The proxy user has all the privileges of the proxy user. The following statement will dev01@localhost Agent for setting the user as root:
    mysql> GRANT PROXY
        -> ON root
        -> TO dev01@localhost;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> SHOW GRANTS FOR dev01@localhost;
    +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Grants for dev01@localhost                                                                                                                                                                                             |
    +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | GRANT SELECT ON *.* TO `dev01`@`localhost`                                                                                                                                                                             |
    | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, ALTER ROUTINE, EVENT, TRIGGER ON `world`.* TO `dev01`@`localhost` |
    | GRANT SELECT, SELECT (`code`, `name`), INSERT, UPDATE, UPDATE (`population`), DELETE ON `world`.`country` TO `dev01`@`localhost`                                                                                       |
    | GRANT PROXY ON 'root'@'%' TO 'dev01'@'localhost'                                                                                                                                                                       |
    +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    4 rows in set (0.00 sec)

    The proxy user permissions are stored in mysql.proxies_priv table.

5.2.2 revocation of authority

The REVOKE statement performs the opposite operation to the GRANT statement to REVOKE the permission granted to the user.

REVOKE privilegee, ..
ON privilege_level
FROM account_name;

The parameters for revoking permission are similar to those for granting permission. The following statement revokes the user dev01@localhost All permissions:

mysql> REVOKE ALL, GRANT OPTION
    -> FROM dev01@localhost;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW GRANTS FOR dev01@localhost;
+--------------------------------------------------+
| Grants for dev01@localhost                       |
+--------------------------------------------------+
| GRANT USAGE ON *.* TO `dev01`@`localhost`        |
| GRANT PROXY ON 'root'@'%' TO 'dev01'@'localhost' |
+--------------------------------------------------+
2 rows in set (0.00 sec)

The proxy user permission needs to be revoked separately:

mysql> REVOKE PROXY
    -> ON root
    -> FROM dev01@localhost;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW GRANTS FOR dev01@localhost;
+-------------------------------------------+
| Grants for dev01@localhost                |
+-------------------------------------------+
| GRANT USAGE ON *.* TO `dev01`@`localhost` |
+-------------------------------------------+
1 row in set (0.00 sec)

User dev01@localhost The original permissions are restored again.

For global level permissions, the effect of REVOKE takes effect when the user logs in next time; For database level permissions, the effect of REVOKE takes effect after executing the USE command; For table level or field level permissions, the effect of REVOKE and subsequent queries take effect immediately.

5.3 management role

When there are more and more users, the management of permissions becomes more and more complex; In fact, many users need the same or similar permissions. To this end, MySQL 8.0 introduces a new feature: Role. A Role is a collection of permissions.

Similar to accounts, roles can also be granted permissions; However, the role cannot be used to log in to the database. The steps of authorizing users through roles are as follows:

  1. Create a role;
  2. Authorize permissions for roles;
  3. Assign roles to users.

5.3.1 creating roles

Suppose our application needs to use the world database. Developers need full access to the database, testers need read-write permission to the table, and business analysts need permission to query data.

First, use the CREATE ROLE statement to create three roles:

mysql> CREATE ROLE devp_role, read_role, write_role;
Query OK, 0 rows affected (0.02 sec)

The role name is similar to the account name and can also contain a role_name and host_name two parts, using @ connection.

If you want to query the user table:

mysql> SELECT host,user,authentication_string FROM mysql.user;
+-----------+------------------+------------------------------------------------------------------------+
| host      | user             | authentication_string                                                  |
+-----------+------------------+------------------------------------------------------------------------+
| %         | devp_role        |                                                                        |
| %         | read_role        |                                                                        |
| %         | write_role       |                                                                        |
| localhost | dev01            | $A$005$lw58QcU;QI|L`ktULChFhIVFxy5dsYrYmEhJkJqko4mezqefUFyT0zgyE2 |
| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | root             | $A$005$kDqbW(q*0Uev;TyKgUe56D9KXiFzPtrSGVxKjvM23CYN5pgE9dLrO0eT8 |
+-----------+------------------+------------------------------------------------------------------------+
8 rows in set (0.00 sec)

It can be seen that the role is actually a user, but there is no password.

5.3.2 authorizing roles

Authorizing a role is similar to authorizing a user by using GRANT statements. We assign permissions to the above three roles:

mysql> GRANT ALL ON world.* TO devp_role;
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT SELECT ON world.* TO read_role;
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT INSERT, UPDATE, DELETE ON world.* TO write_role;
Query OK, 0 rows affected (0.01 sec)

The permissions for viewing roles are similar to those for querying users:

mysql> SHOW GRANTS FOR devp_role;
+------------------------------------------------------+
| Grants for devp_role@%                               |
+------------------------------------------------------+
| GRANT USAGE ON *.* TO `devp_role`@`%`                |
| GRANT ALL PRIVILEGES ON `world`.* TO `devp_role`@`%` |
+------------------------------------------------------+
2 rows in set (0.00 sec)

5.3.2 assigning roles to users

Next, we create several users and assign roles to them.

mysql> CREATE USER devp1 IDENTIFIED BY 'Devp1@2019';
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE USER read1 IDENTIFIED BY 'Read1@2019';
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE USER test1 IDENTIFIED BY 'Test1@2019';
Query OK, 0 rows affected (0.04 sec)

Assigning roles to users is similar to granting permissions. GRANT statements are also used:

mysql> GRANT devp_role TO devp1;
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT read_role TO read1;
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT read_role, write_role TO test1;
Query OK, 0 rows affected (0.01 sec)

Query the user's permission again:

mysql> SHOW GRANTS FOR devp1;
+--------------------------------------+
| Grants for devp1@%                   |
+--------------------------------------+
| GRANT USAGE ON *.* TO `devp1`@`%`    |
| GRANT `devp_role`@`%` TO `devp1`@`%` |
+--------------------------------------+
2 rows in set (0.00 sec)

If you want to know the specific permissions obtained by users through roles, you can use the USING option:

mysql> SHOW GRANTS FOR devp1 USING devp_role;
+--------------------------------------------------+
| Grants for devp1@%                               |
+--------------------------------------------------+
| GRANT USAGE ON *.* TO `devp1`@`%`                |
| GRANT ALL PRIVILEGES ON `world`.* TO `devp1`@`%` |
| GRANT `devp_role`@`%` TO `devp1`@`%`             |
+--------------------------------------------------+
3 rows in set (0.00 sec)

In addition, you can also copy permissions by granting one user to another:

mysql> GRANT read1 TO test1;
Query OK, 0 rows affected (0.09 sec)

A user is a role with login permission, and a role is a user who cannot log in.

5.3.4 setting default roles

Connect to the database using devp1:

"C:\Program Files\MySQL\MySQL Server 8.0\bin\mysql.exe" -u devp1 -p
Enter password: **********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 8.0.17 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use world;
ERROR 1044 (42000): Access denied for user 'devp1'@'%' to database 'world'

We have granted devp to user devp1_ Role, which has all permissions on the database world; The reason for the error is that the role is not automatically activated. Use CURRENT_ROLE() function to view the currently started role:

mysql> SELECT current_role();
+----------------+
| current_role() |
+----------------+
| NONE           |
+----------------+
1 row in set (0.00 sec)

The results show that there are no roles. The SET DEFAULT ROLE command can set the user's default active role:

mysql> SET DEFAULT ROLE ALL
    -> TO devp1;
Query OK, 0 rows affected (0.01 sec)

After using devp1 to connect to the database again, all roles of the user will be activated:

"C:\Program Files\MySQL\MySQL Server 8.0\bin\mysql.exe" -u devp1 -p
Enter password: **********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 8.0.17 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT current_role();
+-----------------+
| current_role()  |
+-----------------+
| `devp_role`@`%` |
+-----------------+
1 row in set (0.00 sec)

mysql> use world;
Database changed
mysql> select * from city limit 1;
+----+-------+-------------+----------+------------+
| ID | Name  | CountryCode | District | Population |
+----+-------+-------------+----------+------------+
|  1 | Kabul | AFG         | Kabol    |    1780000 |
+----+-------+-------------+----------+------------+
1 row in set (0.00 sec)

Another way is to use the SET ROLE command to set the active role of the current session:

SET ROLE NONE;
SET ROLE ALL;
SET ROLE DEFAULT;

The above statements respectively mean that no role is set, all roles are set and default roles are set.

5.3.5 revoke role permissions

Revoking the permission of a role is similar to revoking the permission of a user. Revoking the permission of a role will also affect the users with the role.

The following statement undoes the role write_ DELETE permission of role:

mysql> REVOKE DELETE
    -> ON world.*
    -> FROM write_role;
Query OK, 0 rows affected (0.14 sec)

At this time, the corresponding permissions on user test1 are also revoked.

5.3.6 delete role

The DROP ROLE statement can delete roles:

DROP ROLE role_name, ...;

Deleting a role will revoke the role assigned to the user. The following statement will delete the role read_role and write_role:

mysql> DROP ROLE read_role, write_role;
Query OK, 0 rows affected (0.10 sec)

Official MySQL 8.0 documentation: Access control and account management.

Posted by dreamer on Wed, 10 Nov 2021 12:52:00 -0800