Setting up a new database, or adding a new user privilege to one, and even creating a new table in one is something that even database administrators do relatively rarely. After all, if the number of databases or tables is changing faster than you’re querying the data it contains, then you’re probably doing something wrong. It is only natural, therefore, that the commands for these “one off” actions are more easily remembered, through practice, than the SELECT, INSERT, UPDATE, DELETE commands that make up the bulk of a database’s activity. Even if one can remember the precise syntax for the commands, or one uses a graphical interface where syntax errors are impossible, there are still corner cases where the semantics of the database server can be counter-intuitive. Recently I bumped into two such cases.
GRANT USAGE
If you wanted to create a new user to access a certain table of your database, you might run the following:
CREATE USER foo IDENTIFIED BY ‘bar’;
which would work perfectly. If you later wanted to check the situation for this user, however, you might run:
SHOW GRANTS FOR foo;
and the result would probably contain something like:
GRANT USAGE ON *.* TO ‘foo’@’%’ IDENTIFIED BY PASSWORD ‘*123456SOMEHASH7890ABCDEF’;
That’s all fine, but if you’re like me, you might wonder why the GRANT has automatically applied to the most permissive database / table value possible “.”. You may also wonder if there was a way to change this, to grant usage on a specific database or specific table. I couldn’t easily find a site that clarified this point, so I went to a MySQL IRC channel and asked there. Someone almost instantly answered that “You can grant specific to database, or to database and table.” but when I reiterated the fact I was asking specifically about GRANT, they admitted “Looks like you can issue a more specific grant, but it takes . - not that it matters, since USAGE doesn’t give you any privilieges anyway.” So the way I now look at it is that USAGE is a server level privilege, which just lets the user log in but does not automatically give them rights to run any queries. As I said in the channel “well, i’m not going to demand a rewrite of MySQL just so that that is more clear, i guess”.
SHOW GRANTS with wildcard
Looking back to the GRANT USAGE statement which MySQL returned in the above example, you will see another interesting piece of syntax, the 'foo'@'%' wildcard match for a user at any host. Based on this, if you wanted to find out all the GRANTs for a given user, regardless of host, you would be forgiven for thinking that you should run:
SHOW GRANTS FOR ‘foo’@’%’;
but unfortunately this only shows GRANT lines that literally apply to any host, it does not show GRANT lines regardless of host. To make matters worse, if you don’t specify a host part at all, and just do:
SHOW GRANTS FOR ‘foo’;
it parses that to the same query anyway.
To avoid all these complications, there is a more direct method. MySQL eats its own dogfood in the sense that all permissions for a database are stored in another database on the same server, which is created automatically. It is thus possible to query this database and find out the information you are looking for, although the results will not be in explicit GRANT form. As an example, assuming you’ve told MySQL to use the mysql database, you can run:
SELECT Select_priv, Host FROM user WHERE User=‘foo’;
and get back a Y or N in the first column depending on whether the privilege is granted, and a string representing the host on which it is granted in the second column.
Conclusion
So, these aren’t necessarily bugs in MySQL, or its command line client, but perhaps they count as “gotchas”. I don’t mind something so powerful having these little issues and they’ve never held me up for more than a few minutes, so perhaps that’s why I don’t hate it. However, it is funnier to present things this way:
- Why don’t I hate MySQL?
- Because I’ve tried using PostgreSQL.
Good bye, or should that be \q?
