How to “subtract” privileges in MySQL

Question

I want to revoke update privielges from 2 columns of table "transact". I want the user to have all access to all other tables and data.

mysql> REVOKE UPDATE (system, consumer) ON ledger.transact FROM 'foo'@'localhost';
ERROR 1147 (42000): There is no such grant defined for user 'foo' on host 'localhost' on table 'transaction'

The above does not seem to work.

Answer 1

I guess that 'ledger.transact' is your table? It should work like this than:

REVOKE UPDATE ON ledger.transact FROM 'foo'@'localhost';

look also here for the revoke Syntax used in mysql.

Answer 2

I agree with Thilo - you would only be able to revoke those column privileges if you had granted them before. You cannot grant on a higher level (e.g. table) and then revoke on a more detailed level. I think this is described in the mysql reference manual MySql 5.1 chapter 12.7.1.3:

"The privileges for a database, table, column, or routine are formed additively as the logical OR of the privileges at each of the privilege levels. For example, if a user has a global SELECT privilege, the privilege cannot be denied by an absence of the privilege at the database, table, or column level."

To get the selective privileges is described by Devart already.

Answer 3

Firstly - remove all privileges (on database, table, column levels).

1. Grant UPDATE (...and other) privileges to EACH table, except 'transact'.
2. Grant UPDATE privilege to specified fields in table 'transact'.