How to cancel user permissions in mysql

Mysql method to cancel user permissions: 1. Use "REVOKE ALL ON *.*" to cancel global permissions; 2. Use "REVOKE ALL ON database name.*" to cancel database-level permissions; 3. Use " REVOKE ALL ON "database name. table name" cancels table-level permissions.

The operating environment of this tutorial: windows10 system, mysql8.0.22 version, Dell G3 computer.

How to cancel user permissions in mysql

In MySQL, you can use the REVOKE statement to delete certain permissions of a user

Global level, grant and revoke global permissions

• Authorization: GRANT ALL ON *.*

• Revocation of authorization: REVOKE ALL ON *.*

Database level, grant and revoke permissions for a database

• Authorization: GRANT ALL ON database name.*

• Revoke authorization: REVOKE ALL ON database name.*

Table level, grant and revoke permissions for a table in a database

• Authorization: GRANT ALL ON database name.Table name

• Revocation of authorization: REVOKE ALL ON database name.Table name

Extended knowledge:

In MySQL, you can use the REVOKE statement to delete certain permissions of a user (this user will not be deleted), to a certain extent The security of the system can be guaranteed. For example, a database administrator can remove DELETE permissions if they feel that a user should not have them.

There are two syntax formats for using the REVOKE statement to delete permissions, as shown below:

1) The first one

Deletes some specific permissions of the user, the syntax format As follows:

REVOKE priv_type [(column_list)]...
ON database.table
FROM user [, user]...

The parameters in the REVOKE statement have the same meaning as the parameters in the GRANT statement. Among them:

priv_type parameter indicates the type of permission;

column_list parameter indicates which columns the permission applies to. Without this parameter, it applies to the entire table;

user parameter is represented by It consists of username and hostname, in the format of "username'@'hostname'".

2) The second type

deletes all permissions of a specific user. The syntax format is as follows:

REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...

You need to pay attention to the following points when deleting user permissions:

The syntax of REVOKE syntax is similar to that of the GRANT statement, but has opposite effects.

To use the REVOKE statement, you must have global CREATE USER permission or UPDATE permission on the MySQL database.

The example is as follows

Use the REVOKE statement to cancel the insert permission of user testUser. The SQL statement and execution process are as follows.

mysql> REVOKE INSERT ON *.*
    -> FROM 'testUser'@'localhost';
Query OK, 0 rows affected (0.01 sec)
 
mysql> SHOW GRANTS FOR 'testUser'@'localhost';
+-----------------------------------------------------------------+
| Grants for testUser@localhost                                   |
+-----------------------------------------------------------------+
| GRANT SELECT ON *.* TO 'testUser'@'localhost' WITH GRANT OPTION |
+-----------------------------------------------------------------+
1 row in set (0.00 sec)

The results show that the INSERT permission of the testUser user was successfully deleted.

Recommended learning: mysql video tutorial

The above is the detailed content of How to cancel user permissions in mysql. For more information, please follow other related articles on the PHP Chinese website!