MySQL REVOKE to delete user permissions

In MySQL, you can use the REVOKE statement to remove certain permissions of a user (the user will not be deleted), which can ensure the security of the system to a certain extent. For example, if the database administrator feels that a user should not have DELETE permission, then the DELETE permission can be removed.

There are two forms of syntax for removing permissions using the REVOKE statement, as follows:

1) The first type

Delete certain specific permissions of the user. The syntax format is 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. in:

• The priv_type parameter indicates the type of permission;
• The column_list parameter indicates which columns the permission applies to. If this parameter is not present, the permission applies to the entire table.
• The user parameter consists of the username and host name in the format of "username'@'hostname'".

2) The second type

Delete all permissions for a specific user. The syntax is as follows:

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

When deleting user permissions, please note the following points:

• The REVOKE syntax is similar to the GRANT statement syntax, but has the opposite effect.
• To use the REVOKE statement, you must have the global CREATE USER privilege or the UPDATE privilege for the MySQL database.

Example 1

Use the REVOKE statement to revoke 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 result shows that the INSERT permission of the testUser user is deleted successfully.

This is the end of this article about MySQL REVOKE to delete user permissions. For more information about MySQL REVOKE to delete user permissions, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!