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

How to Grant All Privileges to a MySQL Database Except for a Single Table

Question:

Users seek a solution to allow a user to have complete access to a database except for a particular table, where they should have only SELECT privileges.

Answer:

Despite attempts to grant all privileges on the database and then specifically grant only SELECT privileges on the desired table, or to revoke insert, update, and delete privileges, errors have persisted.

Solution:

Dynamic Grant Statement Generation:

To simplify the process, you can use the following SELECT CONCAT query to generate the necessary GRANT statements:

<code class="sql">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";</code>

Steps:

1. Revoke all privileges for the user on the database:

   <code class="sql">REVOKE ALL PRIVILEGES ON db.* FROM user@localhost;  </code>

2. Execute the SELECT CONCAT query to generate the GRANT statements.
3. Paste the generated statements into your MySQL client and run them.

Example:

REVOKE ALL PRIVILEGES ON my_db.* FROM my_user@localhost;  

SELECT CONCAT("GRANT UPDATE ON my_db.", table_name, " TO my_user@localhost;")
FROM information_schema.TABLES
WHERE table_schema = "my_db" AND table_name <> "special_table";

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