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'; Copy code
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) Copy code
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@% Copy code
%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' Copy code
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) Copy code
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
}
Copy coderesource_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)
Copy codeNote 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) Copy code
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]
}
Copy codePassword 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)
Copy codeThe following are account lockout options:
lock_option: {
ACCOUNT LOCK
| ACCOUNT UNLOCK
}
Copy codeThis 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) Copy code
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) Copy code
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) Copy code
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. Copy code
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) Copy code
Finally, we will unlock dev01:
mysql> ALTER USER dev01@localhost ACCOUNT UNLOCK; Query OK, 0 rows affected (0.10 sec) Copy code
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) Copy code
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) Copy code
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; Copy code
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) Copy code
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) Copy codeDatabase permissions are stored in the mysql.db table.
-
Table permissions that apply to all columns of the specified table. Database permission usage db_name.table_name means; If DB is not specified_ Name, use the default database; 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) Copy codeTable permissions are stored in 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)
Copy codeColumn 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)
Copy codeThe 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)
Copy codeThe 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; Copy code
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)
Copy codeThe 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)
Copy codeUser 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:
- Create a role;
- Authorize permissions for roles;
- 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) Copy code
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) Copy code
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) Copy code
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) Copy code
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) Copy code
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) Copy code
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) Copy code
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) Copy code
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) Copy code
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' Copy code
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) Copy code
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)
Copy codeAfter 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) Copy code
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; Copy code
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)
Copy codeAt 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, ...; Copy code
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)