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 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 2

Starting with MySQL 8.0.16, you can finally substract privileges, although for now, only from global grants (e.g. you cannot yet do it for individual columns), but it is worth noting:

As of MySQL 8.0.16, it is possible to explicitly deny a privilege granted at the global level by revoking it for particular databases, if the partial_revokes system variable is enabled:

GRANT SELECT, INSERT, UPDATE ON *.* TO u1;
REVOKE INSERT, UPDATE ON db1.* FROM u1;

The partial_revokes system variable has to be set for this:

Enabling this variable makes it possible to revoke privileges partially. Specifically, for users who have privileges at the global level, partial_revokes enables privileges for specific schemas to be revoked while leaving the privileges in place for other schemas. For example, a user who has the global UPDATE privilege can be restricted from exercising this privilege on the mysql system schema. (Or, stated another way, the user is enabled to exercise the UPDATE privilege on all schemas except the mysql schema.) In this sense, the user's global UPDATE privilege is partially revoked.