How to Grant All Privileges in a MySQL Database Except for a Specific Table?

Granting Privileges to a Database Except for a Specific Table in MySQL

Many databases often require granting different levels of privileges to various users. Some users may need complete control over a database, while others may only require limited permissions to specific tables. In MySQL, assigning comprehensive privileges to a user can be straightforward; however, allotting all permissions except for a particular table can be challenging.

Initial Unsuccessful Attempts

As mentioned in the user's inquiry, attempting to grant all privileges using "db_name.*" and then selectively granting SELECT privileges on the desired table doesn't override the "all" privileges. Additionally, revoking insert, update, and delete privileges after granting all privileges results in an error due to the absence of a grant rule for the specific table.

Individual Privilege Granting

While individually granting all privileges to each table in the database except the read-only table may seem tedious, it's currently the most direct and reliable method. This approach ensures that the user has no update rights for the specific table.

Easier Way?

The user expresses hope for a simpler solution, and there is indeed a technique that combines the convenience of granting all privileges with the exclusion of a specific table:

1. Revoke All Privileges:

   REVOKE ALL PRIVILEGES ON db.* FROM user@localhost;

2. Use Information_schema to Create GRANT Statements:

   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";

3. Copy and Execute Results
   Copy the results of the query and execute them in the MySQL client to grant update privileges to all tables except the specified one.

This approach provides a quicker and more dynamic alternative to manually granting privileges to each table individually.

The above is the detailed content of How to Grant All Privileges in a MySQL Database Except for a Specific Table?. For more information, please follow other related articles on the PHP Chinese website!