How Do I Grant Privileges in MySQL Except for a Specific Table?

Granting Privileges Except for a Specific Table

In MySQL, you can encounter situations where you need to grant a user extensive privileges on a database, but with the exception of a specific table. This can be achieved, but requires a more granular approach compared to granting "ALL" privileges.

To resolve this issue, you can individually grant various privileges on each table within the database, excluding the desired read-only table. For instance, you could grant the user privileges such as "INSERT," "UPDATE," "DELETE," and "SELECT," but exclude the "UPDATE" privilege from the table that should be read-only.

Here's how you can achieve this:

1. Revoke all existing privileges from the user on the target database. This ensures a clean slate for resetting privileges.
2. Use the GRANT command to grant individual privileges on each table. For example:

<code class="mysql">GRANT INSERT, DELETE, SELECT ON db_name.table1 TO user@localhost;</code>

1. Create a separate GRANT statement for each table in the database, excluding the one that should remain read-only.
2. Execute each GRANT statement in sequence.
3. Check the user's privileges using the SHOW GRANTS command to verify the changes.

You can also use tools like MySQL Workbench or phpMyAdmin to manage user privileges and grant specific permissions on tables and databases. These tools provide a more convenient interface for setting up permissions and allow you to visually view the privileges granted to each user.

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