The Great Joys of MySQL permissioning
(I was going to talk about God's sick sense of humor, but that's a Random Rant for another day.)
I just had some great...fun...with MySQL permissions and I thought I'd record it here for posterity, because sure as shootin' I'm going to be bitten by this problem again.
I have a small script that gets called by a web page that tries to create new databases and assign permissions to yet other users from the original site...that is to say:
The light went on when I logged in interactively from the web server and saw what I thought "remote root" could do:
When the light went on, it nearly blinded me.
Notice that in the second line, the "remote root"'s privileges on the mysql database (where all of the user privileges are kept) has no 'IDENTIFIED BY' (meaning, a password is required, and has a cryptographic has value equivalent to what I've deleted).
In fact, you won't ever see the 'IDENTIFIED BY' in the second line, but what tickled me about this is that there's no indication that the "remote root" user wouldn't be able to grant permissions.
There's a paradox there. In order to change privileges on the database server (to allow 'user1' to log in, for example), you need to provide no password (that is, you must not provide a password), but in order to log in, you must provide a password! (It is only because I've seen this behavior before that I recognized this; there is otherwise little other indication about it.)
MySQL, to their credit, does document most of this, in their typical fashion, but without any mention of what workarounds might be necessary, or that the regular 'GRANT' facility might not work the way you think, or where the command will succeed--it will do what you tell it to--but not what you want it to... Once I rectified this by granting privileges on the mysql table to the "remote root" user, all the problems went away:
and then...
Once again: MySQL acts like you tell it to, but not how you might want it to, and there's no indication of how you might shoot yourself in the foot here; it simply silently sets things up in an impossible fashion.
+-------------+ +---------------+ | web server | | database srvr | | user 'user1'| ----> | | +-------------+ +---------------+Now when the web page runs, it calls a script that connects as user 'root' from the webserver host.
+-------------+ +---------------+Presumably, if user 'root' can log in, it can create and grant privileges? Ah, not so! It turns out, you can, but if you're not careful when you first set up the permissions for root@'webserver', you end up with some permissions to do things and some NOT.
| web server | | database srvr |
| user 'root' | ----> | |
+-------------+ +---------------+
The light went on when I logged in interactively from the web server and saw what I thought "remote root" could do:
mysql> show grants; +--------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Grants for root@webserverhost | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'webserver' IDENTIFIED BY PASSWORD '*you think i will put this here??!!' WITH GRANT OPTION | | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES ON `mysql`.* TO 'root'@'webserverhost' | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
When the light went on, it nearly blinded me.
Notice that in the second line, the "remote root"'s privileges on the mysql database (where all of the user privileges are kept) has no 'IDENTIFIED BY' (meaning, a password is required, and has a cryptographic has value equivalent to what I've deleted).
In fact, you won't ever see the 'IDENTIFIED BY' in the second line, but what tickled me about this is that there's no indication that the "remote root" user wouldn't be able to grant permissions.
There's a paradox there. In order to change privileges on the database server (to allow 'user1' to log in, for example), you need to provide no password (that is, you must not provide a password), but in order to log in, you must provide a password! (It is only because I've seen this behavior before that I recognized this; there is otherwise little other indication about it.)
MySQL, to their credit, does document most of this, in their typical fashion, but without any mention of what workarounds might be necessary, or that the regular 'GRANT' facility might not work the way you think, or where the command will succeed--it will do what you tell it to--but not what you want it to... Once I rectified this by granting privileges on the mysql table to the "remote root" user, all the problems went away:
mysql> grant all on *.* to root@'webserver' identified by 'xxxxxyyyy' with grant option
-> ;
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
and then...
[jbaltz@webhost] >mysql -u clover -pxxxxxyyyy -e 'show tables from newDataBase' -hDatabaseServer +-----------------------+ | Tables_in_newDataBase | +-----------------------+ | User | +-----------------------+...which gives me what I need.
Once again: MySQL acts like you tell it to, but not how you might want it to, and there's no indication of how you might shoot yourself in the foot here; it simply silently sets things up in an impossible fashion.