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’@’db4.adapt.com’ for table ‘gbidsuggestion_0616’

As super, I connect and revoke a different permission for user ‘bentest@db4.adapt.com’

(root@localhost) [test]> revoke insert on test.* from bentest@’db4.adapt.com’;
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@’%.adapt.com’. That meant that if ‘bentest’ logged in from any host on the adapt.com subnet, he could select from any table in the test database. But then I granted insert to bentest@’db4.adapt.com’, and when bentest logged in from db4.adapt.com, MySQL found the most restrictive host clause, db4.adapt.com, 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@’%.adapt.com’;
2. grant select on test.* to bentest@’db4.adapt.com’;
3. grant insert on test.* to bentest@’db4.adapt.com’;

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 db4.adapt.com, even though db4.adapt.com is part of %.adapt.com.

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.