Do MySQL privileges really work like this?

November 22, 2010 11:46 AM

With MySQL 5.0, how can I grant SELECT privileges on everything *except* for one column in one table?

I need to create a read-only user that can query all the tables in the database except for one particular column of one table.

Do I have to do something ridiculous like:

GRANT SELECT ON DB.TABLE1 TO user;
GRANT SELECT ON DB.TABLE2 TO user;
GRANT SELECT ON DB.TABLE3 TO user;
[...]

GRANT SELECT(COLUMN1) ON DB.TABLE0 TO user;
GRANT SELECT(COLUMN2) ON DB.TABLE0 TO user;
GRANT SELECT(COLUMN3) ON DB.TABLE0 TO user;
[...]

So that TABLE0.COLUMN0 will not be available to "user"?

posted by chengjih to Computers & Internet (13 answers total)

You could GRANT widely then REVOKE the rights you don't want them to have.

http://dev.mysql.com/doc/refman/5.1/en/revoke.html
posted by advicepig at 12:00 PM on November 22, 2010

Search on stack overflow if you don't get a satisfactory answer here.
posted by special-k at 12:01 PM on November 22, 2010

Response by poster: When I GRANT widely and then REVOKE specifically, I get something like: ERROR 1147 There is no such grant defined for user...."

Apparently, the wide GRANT is at the DB level, and the REVOKE is happening at the table/column level, so the server isn't accepting it.
posted by chengjih at 12:06 PM on November 22, 2010

Couldn't you just iterate through each table and grant just to that table and not grant to the entire database?
posted by Brian Puccio at 12:18 PM on November 22, 2010

Oh right, column level privs.

So then, what you need to know is that column level privs are stored differently than table level privs. So you should be able to

GRANT SELECT(COLUMN1, COLUMN2, COLUMN3) ON DB.TABLE0 TO user;
posted by advicepig at 12:24 PM on November 22, 2010

Response by poster: So, I'm going to have to iterate through all tables except TABLE0 and then iterate through the columns of TABLE0 except for COLUMN0? I was hoping for some magic sequence of commands that would do the wide grant and then the narrow revokes. Oh, well.
posted by chengjih at 12:31 PM on November 22, 2010

Nope,

GRANT SELECT ON *.* TO user;
REVOKE SELECT ON table0 FROM user;
GRANT SELECT(COLUMN1, COLUMN2, COLUMN3) ON DB.TABLE0 TO user;

The first one gives them select on everything. The second revokes it for the table you want to be more specific on. The third gives rights just to the columns that you list. Note that the third statement allows you to list them all separated by commas.
posted by advicepig at 1:21 PM on November 22, 2010

Response by poster: I had tried that, and got a "ERROR 141", "There is no such grant defined for user ..." on the REVOKE.

This is a MySQL 5.0 DB, if that matters.
posted by chengjih at 2:33 PM on November 22, 2010

Best answer: REVOKE only revokes a grant that exists. If it ain't in SHOW GRANTS you can't revoke it. (Put differently, MySQL only records positive grants. It's "deny if not explicitly allowed".) You have to do something ridiculous like you suggested.
posted by mendel at 4:31 PM on November 22, 2010

Best answer: If you want a magic sequence of commands, pick the scripting language of your choice and make it do the following:

1. Get a list of all tables
2. From this list, remove "special table"
3. Grant SELECT on the list
4. Get a list of all columns in "special table"
5. From this new list, remove "special column"
6. Grant SELECT on the list on "special table"

Or, if you won't have to do this more than once/don't have tons of tables/columns, just do it by hand, or use a GUI to quickly check all and then uncheck the one you don't want both times through.
posted by Brian Puccio at 5:54 PM on November 22, 2010

Response by poster: Yeah, I did it in bash, actually.

I have to bring this up with the developers. This is obviously a pain in the ass if there are schema changes.
posted by chengjih at 6:18 PM on November 22, 2010

For prod databases (or more precisely, databases with real data) you really shouldn't be doing grant alls anyway.

Whatever process adds the table (e.g., liquibase) should also issue the minimum grants possible to the table.

For read-only access to a subset of a table's columns, make a view that contains only that subset. Whatever process runs the "create or replace view" should issue a grant to your read-only special user, to that view.
posted by orthogonality at 10:32 PM on November 22, 2010

Agreed with orthogonality.
posted by Brian Puccio at 1:05 PM on November 23, 2010