Grant, Revoke, Grant, Revoke: Help my db

January 24, 2007 10:31 AM

Help me restrict MySQL privileges for a particular table without screwing up access to the rest of the database.

User 'soandso' was set up initially with the command on the lines of GRANT ALL PRIVILGES ON db.* TO soandso@suchandsuch IDENTIFIED BY whatever. Now I want to revoke UPDATE and INSERT privileges for this person on one table, without affecting his rights to UPDATE and INSERT on the other tables in the database.

How do I do this? I tried doing a REVOKE INSERT, UPDATE ON db.tablename FROM soandso. I did FLUSH PRIVILEGES. Then I tested this by connecting as user 'soandso' and trying to insert a row -- and voila, it inserts.

What am I missing? Do the privileges for the db override the privileges for the table? In other words, do I need to revoke all, then grant the privileges separately for each and every table? [that would be a pain]. What next?

posted by Robert Angelo to Technology (2 answers total)

Yep, global privileges override database-level privileges, which override table-level privileges, which in turn override column-level privileges. Permissions are ORed together.
posted by Khalad at 10:47 AM on January 24, 2007

Thanks, Khalad. That's exactly what I was suspecting, though I couldn't find it in the MySQL docs. Maybe I wasn't looking in the right place... I'll find another way to work through this for my user.
posted by Robert Angelo at 11:09 AM on January 24, 2007