How to Revoke Table Privileges from a Database-Wide Administrator in MySQL?

Revoking Specific Table Privileges from a Database-Wide Administrator

As a MySQL administrator, you may occasionally need to grant users full permissions over a database while restricting access to a particular table. This seemingly simple task can be surprisingly challenging to achieve through conventional methods.

Granting All Privileges Except Select

One common approach involves granting all privileges on the database (e.g., db_name.*) followed by a specific grant for select privileges on the excluded table. However, this method is ineffective, as the subsequent grant does not override the initial unrestricted access.

Selective Privileges Approach

The preferred solution involves granting permissions on a table-by-table basis, excluding the table that you wish to restrict. While this approach may be tedious for databases with numerous tables, it ensures precision and prevents unintentional access to the protected table.

Using Information Schema

For convenience, you can leverage the information_schema database to generate the necessary grant statements. The following query will return a list of grant commands for all tables in a specific database, excluding the table you wish to protect:

SELECT CONCAT("GRANT UPDATE ON db.", table_name, " TO user@localhost;")
FROM information_schema.TABLES
WHERE table_schema = "YourDB" AND table_name  "table_to_skip";

Conclusion

Granting all privileges on a database except for a specific table can be achieved by manually granting permissions on a table-by-table basis or by using the information_schema database to generate grant statements for all tables, excluding the one to be protected. The latter approach provides an efficient and reliable solution to this common MySQL administration task.

The above is the detailed content of How to Revoke Table Privileges from a Database-Wide Administrator in MySQL?. For more information, please follow other related articles on the PHP Chinese website!