How to revoke MySQL user privileges for one table?

Question

When I have granted privileges to a user for some specific tables:

GRANT ALL PRIVILEGES ON table1.* TO 'user1'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON table2.* TO 'user1'@'localhost' IDENTIFIED BY 'password';

How do I revoke the privileges for this user, just for table1?

Answer 1

Google is your friend! http://dev.mysql.com/doc/refman/5.7/en/revoke.html

Syntax:

REVOKE ALL PRIVILEGES ON table1.* FROM 'user1'@'localhost';

---

To further explain this answer - I'll teach how to fish (rather than just give you a fish).

The MySQL documentation can look confusing at first - the "syntax" for REVOKE looks like this:

REVOKE
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    FROM user [, user] ...

REVOKE ALL PRIVILEGES, GRANT OPTION
    FROM user [, user] ...

REVOKE PROXY ON user
    FROM user [, user] ...

It means there are 3 "ways" of calling it:

1. REVOKE priv_type ...
2. REVOKE ALL PRIVILEGES, GRANT ...
3. REVOKE PROXY ON ...

These three are separated by the blank lines in the MySQL doc page.

For each of these, there are "optional" parameters/settings/values. These are denoted by the square brackets, for example:

REVOKE priv_type [(column_list)] ...

The (column_list) is optional. You can supply it, but you don't have to.

Similarly you can chain these together - they've indented the next line to indicate this (and used ... to show you can continue repeating):

priv_type [(column_list)]
  [, priv_type [(column_list)]] ...    <-- indented, and note the "..."

More complicated examples exist in the MySQL documentation - like for CREATE TABLE you have lists of optional flags:

[COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]

This {x|y|z} syntax indicates you must specify one of them (the {...} is non-optional, the [...] means everything inside is optional - so if you specify COLUMN_FORMAT, one of the three following flags is required), the pipes (|) indicate you can only specify one of the list (FIXED / DYNAMIC / DEFAULT).

---

One final thing to say - be very aware of the MySQL documentation version. It's stated in several places on the website - personally I just look at the URL:

http://dev.mysql.com/doc/refman/5.7/en/create-table.html

Note it says 5.7 in it. This means the documentation you're reading may not be applicable to any version other than MySQL 5.7. That's bitten me a lot of times ... usually when I'm under the gun trying to fix something in a panic! Always double-check it.