MySQL Revoke Privilege

The MySQL REVOKE statement is used to revoke specific privileges that were previously granted to a user or role. It allows you to remove the permission to perform certain actions on a database object. The REVOKE statement is typically used by database administrators to restrict or modify the access privileges of users or roles.

The basic syntax of the REVOKE statement is as follows:

REVOKE privileges
ON database.object
FROM 'user'@'host';

Here’s a breakdown of each part of the statement:

• privileges: Specifies the specific privileges you want to revoke. These can include privileges like SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, and many others. You can also use ALL PRIVILEGES to revoke all available privileges.
• database.object: Identifies the specific database or database object from which you want to revoke privileges. This could be a specific database, table, or a wildcard character like *.* to represent all databases and objects.
• user@host: Specifies the user or role from which you want to revoke privileges and the host from which they are connecting. The user and host are typically specified as ‘username’@’hostname’, where the hostname can include wildcard characters for more flexibility.

Here are some examples of how to use the REVOKE statement in MySQL:

1. Revoke the SELECT privilege on the employees table in the hr database from a user named ‘john’ connecting from the localhost:

   REVOKE SELECT ON hr.employees FROM 'john'@'localhost';

2. Revoke all privileges on all tables in the sales database from a user named ‘manager’ connecting from any host:

   REVOKE ALL PRIVILEGES ON sales.* FROM 'manager'@'%';

3. Revoke the ability to create databases and tables from a user named ‘developer’ connecting from the localhost:

   REVOKE CREATE, CREATE TABLE ON *.* FROM 'developer'@'localhost';

After executing the REVOKE statement, the specified user or role will no longer have the revoked privileges for the specified database object. The changes will take effect immediately, and the user or role will no longer be able to perform the revoked actions on the designated database or table.

As with the GRANT statement, the REVOKE statement should be executed by a user with the necessary administrative privileges, such as GRANT OPTION, to be able to revoke privileges from others. Additionally, you may need to execute a FLUSH PRIVILEGES statement for the changes to take effect immediately.