JIYIK CN >

In this article, we will learn how to revoke MySQL privileges.

Revoking Privileges in MySQL

The actions that a MySQL account is allowed to perform depend on the privileges assigned to it. The context in which each type of MySQL privilege is used and its respective level of action varies.

The grant tables in the MySQL system database contain data about account privileges.

System administrators can use the GRANT statement to provide privileges and roles to MySQL user accounts, allowing those accounts to access assigned permissions on the database as needed.

GRANT privilege_name ON object_name TO user_name;

The privilege or access rights granted to the user account are specified by privilege_name. If you want to grant all privileges, separate multiple privileges with the comma operator.

The privilege level at which access is provided is determined by object_name. It includes granting access to a table; the name of the table should be the object.

The account name of the user to whom access will be provided is determined by user_name. A list of permission levels is provided below.

1. Global
2. Database
3. Table
4. Column
5. Stored Routine
6. Proxy

Consider the following example to help you better understand the previous idea.

> GRANT ALL ON *.* TO user_name@localhost;

Execute the above command to provide user_name@localhost access to all databases on the current server. To revoke access to user account privileges, MySQL provides REVOKEstatement.

By using the revoke statement, a system administrator can prevent a MySQL user account from using database privileges that were granted in the past.

REVOKE privilege_name ON object_name FROM user_name;

The privileges or access rights granted to the user account are specified by privilege_name. If you want to revoke them, separate multiple privileges using the comma operator.

The privilege level at which access is provided is determined by object_name. It includes granting access to a table; the name of the table should be the object.

The account name of the user whose access we want to revoke is identified by user_name. Consider the following example to help you better understand the previous idea.

> REVOKE ALL ON *.* FROM user_name@localhost;
> REVOKE ALL, GRANT OPTION FROM user_name@localhost;
> REVOKE ALL PRIVILEGES, GRANT OPTION FROM user_1 , user_2 ...

Execute the above command to revoke user_name@localhost's access rights to all databases on the current server. Execute the second command to remove all permissions granted to the user.

Use the third syntax to remove all privileges for the specified user or role. This removes all global, database, table, column, and routine privileges.