MySQL REVOKE

MySQL REVOKE

The REVOKE statement in MySQL is used to remove privileges or permissions that have been granted to users or roles. This can include revoking access to databases, tables, or specific operations like SELECT, INSERT, UPDATE, DELETE, etc. The REVOKE statement is essentially the opposite of the GRANT statement.

Syntax:

REVOKE privilege_type
ON object
FROM user;

• privilege_type: The type of privilege (e.g., SELECT, INSERT, UPDATE, DELETE, ALL PRIVILEGES, etc.)
• object: The database object (e.g., database, table, column, or wildcard * for all databases).
• user: The username from which the privilege is being revoked, in the format username@hostname.

Example:

1. Revoke All Privileges from a User:

To revoke all privileges on a specific database or table from a user:

REVOKE ALL PRIVILEGES ON database_name.* FROM 'username'@'hostname';

2. Revoke Specific Privileges (e.g., SELECT) on a Table:

To revoke only the SELECT privilege on a table for a user:

REVOKE SELECT ON database_name.table_name FROM 'username'@'hostname';

3. Revoke Privileges from a User (Wildcard Example):

To revoke INSERT privileges on all tables in a specific database:

REVOKE INSERT ON database_name.* FROM 'username'@'hostname';

4. Revoke Privileges on Multiple Databases:

To revoke privileges on all databases:

REVOKE ALL PRIVILEGES ON *.* FROM 'username'@'hostname';

Important Notes:

• REVOKE only removes privileges that were granted using the GRANT statement. It does not affect any other privileges that may have been inherited or granted by other means.
• After revoking privileges, you should use the FLUSH PRIVILEGES command to apply the changes immediately:

FLUSH PRIVILEGES;

• REVOKE cannot remove privileges granted to a user by GRANT OPTION if the user is the one who granted those privileges to others. You need to revoke privileges for those users specifically.

Checking Privileges:

To view the privileges granted to a user, use the SHOW GRANTS command:

SHOW GRANTS FOR 'username'@'hostname';

This will list all privileges granted to the user.

Conclusion:

The REVOKE statement is a key tool for managing user permissions in MySQL, allowing you to restrict access to certain resources. By understanding how to revoke privileges properly, you can ensure that only the necessary users have access to specific parts of your database.