How to Revoke Privileges in MySQL

In today’s post, we’ll learn how to revoke MySQL privileges.

Revoke Privileges in MySQL

The operations that a MySQL account is permitted to carry out depend on the privileges assigned to it. The contexts in which each type of MySQL privilege is used and their respective levels of operation vary.

The grant tables in the MySQL system database include data regarding account privileges.

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

GRANT privilege_name ON object_name TO user_name;

The grant privilege or access privileges for user accounts are specified by privilege_name. Use the comma operator to separate several privileges if you want to grant them all.

The permission level on which the access rights are being provided is determined by object_name. It includes giving the table access; the table’s name should be the object.

The account name of the user to whom access permissions would be provided is determined by user_name. The list of privilege 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 prior 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 a user account’s privileges, MySQL offers REVOKE statements.

By using the revoke statement, system administrators can prevent MySQL user accounts from using the issued permissions on the database in the past.

REVOKE privilege_name ON object_name FROM user_name;

The grant privilege or access privileges for user accounts are specified by privilege_name. Use the comma operator to separate several privileges if you want to revoke them.

The permission level on which the access rights are being provided is determined by object_name. It includes giving the table access; the table’s name should be the object.

The account name of the user whose access rights we want to revoke is identified by user_name. Consider the following example to help you better understand the prior 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 access to all databases on the current server. Execute the second command to remove all rights granted to the user.

Use the third syntax to remove all privileges for the named users or roles. This removes all global, database, table, column, and routine privileges.