How to revoke user Rights by mysql

This article introduces the knowledge of "how to revoke user rights in mysql". In the operation of actual cases, many people will encounter such a dilemma. Next, let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Mysql cancels user rights: 1, cancel global permissions with "REVOKE ALL ON *. *"; 2, cancel database-level permissions with "REVOKE ALL ON database name. *"; 3, cancel table-level permissions with "REVOKE ALL ON database name. Table name".

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

How to revoke the user's right by mysql

In MySQL, you can use the REVOKE statement to remove some permissions from a user

Global level, granting and revoking global permissions

Authorization: GRANT ALL ON *. *

Revocation of authorization: REVOKE ALL ON *. *

Database level, granting and revoking permissions to a database

Authorization: GRANT ALL ON database name. *

Revoke authorization: REVOKE ALL ON database name. *

Table level, granting and revoking permissions for a table in a database

Authorization: GRANT ALL ON database name. Table name

Revoke authorization: REVOKE ALL ON database name. Table name

Expand knowledge:

In MySQL, you can use the REVOKE statement to remove some permissions of a user (this 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 you can remove the DELETE permission.

The syntax format for deleting permissions using the REVOKE statement comes in two forms, as follows:

1) the first kind

Delete some specific permissions of the user. The syntax format is as follows:

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

The parameters in the REVOKE statement have the same meaning as those in the GRANT statement. Where:

The priv_type parameter indicates the type of permission

The column_list parameter indicates which columns the permission applies to, and if there is no such parameter, it acts on the entire table

The user parameter consists of a user name and a host name in the format "username'@'hostname'".

2) the second kind

Delete all permissions for 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 to delete user permissions:

The syntax format of REVOKE syntax and GRANT statement is similar, but has the opposite effect.

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

Examples are as follows

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

Mysql > REVOKE INSERT ON *. *-> FROM 'testUser'@'localhost';Query OK, 0 rows affected (0.01sec) 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 deleted successfully.

This is the end of the content of "how to revoke user permissions in mysql". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!