Playing with Permissions

Quick quiz: when can this happen? When can revoking a privilege from a user “grant” them another privilege?

I try to select from a table, and am denied.

(bentest@db3) [test]> select * from gbidsuggestion_0616 limit 1;
ERROR 1142 (42000): SELECT command denied to user ‘bentest’@’’ for table ‘gbidsuggestion_0616’

As super, I connect and revoke a different permission for user ‘’

(root@localhost) [test]> revoke insert on test.* from bentest@’’;
Query OK, 0 rows affected (0.00 sec)

Now I log back in as the original user, and I can do the select.

(bentest@db3) [test]> select * from gbidsuggestion_0616 limit 1;
| gbidsuggestionid |… | active |
| 1 |… | 0 |
1 row in set (0.00 sec)

How can this happen?

It has to do with how MySQL checks permissions.

In MySQL, you grant permissions to a user, or to a user logging in from a particular host or network. (This might mean you want to script your grants – you can end up doing a lot of typing, user-by-user, host-by-host.) You can do some pretty nice things with this. You can have a user’s permissions depend on where they’re connecting from, for example. (You can even have different passwords depending on where you connect from. I tried it just out of curiosity.)

I got a bit tripped up, though, at one point. I granted select access to a user from anywhere on our internal network. Then I wanted to allow them to also do inserts, but only from one particular host on the network. (Only from the proper host. Eg, to make sure someone didn’t accidentally update production from QA.) I simply granted insert to that user from that host. I figured, then they can select from anywhere on the subnet, and insert if they’re on the right host.

But it doesn’t work that way. When a user logs in, MySQL looks for the most restrictive host clause that applies to that user, and uses the grants from that. Since I didn’t explicitly grant select from that one host, MySQL didn’t find a select grant and select was denied. If I revoked the grants for that one host, MySQL then finds the grants from the next level up (for the subnet) and found the select grant.

Eg, in my example above, select on test.* was granted to bentest@’’. That meant that if ‘bentest’ logged in from any host on the subnet, he could select from any table in the test database. But then I granted insert to bentest@’’, and when bentest logged in from, MySQL found the most restrictive host clause,, and used permissions for bentest at that host.

The moral is, if you want to grant extra privileges for connecting from a particular host, grant all the applicable privileges for that user and host, not just the additional ones you wanted to grant. So I would have to use the following grants:
1. grant select on test.* to bentest@’’;
2. grant select on test.* to bentest@’’;
3. grant insert on test.* to bentest@’’;

Ie, (as we saw) if you only had the 1st and 3rd grant there, the 1st grant would not allow bentest to select from test.* if he connected from, even though is part of

I think it would be nicer if it worked the way I thought it did, but oh well… It would also be nice to be able to use roles, and to be able to use kerberos-based authentication, but you can’t do those either.


3 Responses to “Playing with Permissions”

  1. Log Buffer #60: a Carnival of the Vanities for DBAs · Steve Karam · The Oracle Alchemist Says:

    […] does revoking a permission grant a permission? When it’s MySQL! This post, also by the MySQL DBA that was once an Oracle DBA, talks about how permissions work in MySQL, and […]

  2. sandrar Says:

    Hi! I was surfing and found your blog post… nice! I love your blog. 🙂 Cheers! Sandra. R.

  3. DBAWork Says:

    Because of host level permission, mysql becomes very interesting to work with. You database level,object level as well host level permissions, you can ask user to work from perticular computer, if they want to work on this database.

    Interesting blog, thank you for sharing.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: