Trapped by History


SSL-users in MySQL seem to cause quite a lot of problems for people. Quite a lot af bug reports have been posted to bugs.mysql.com over time that have most or all been classifed as ‘not a bug’. Numerous discussions exists on the Internet as well and people find weird workarounds like directly manipulating the mysql.user table.  I write this Blog because yesterday I found a discussion in a Forum where a dozen of self-appointed ‘experts’ tried to help a newbie with this causing only more confusion.

First problem: Let us try

DROP USER /*!70101  IF EXISTS */ ssss@localhost — just a ‘sidekick’, but not the point here 🙂
CREATE USER ssss@localhost;
GRANT UPDATE ON ttest.* TO ssss@localhost REQUIRE SSL;
GRANT SELECT ON ttest.* TO ssss@localhost;

Some people expect that a client would connect without encryption and would be able to SELECT from table (but not UPDATE) in the `ttest` database. But connection without encryption is not possible at all. SSL is required for authentication. Let us SHOW GRANTS:

SHOW GRANTS FOR ssss@localhost;
/* returns

Grants for ssss@localhost
——————————————————-
GRANT USAGE ON *.* TO ‘ssss’@’localhost’ REQUIRE SSL
GRANT SELECT, UPDATE ON `ttest`.* TO ‘ssss’@’localhost’
*/

Second problem:

DROP USER /*!70101 IF EXISTS */ ssss@localhost
CREATE USER ssss@localhost;
GRANT UPDATE ON ttest.* TO ssss@localhost REQUIRE SSL;
GRANT UPDATE ON ttest.* TO ssss@localhost;

Some people expect that this will “REVOKE”/REMOVE ‘REQUIRE SSL’ from the user as it is not specified in the last statement. But it does not:

SHOW GRANTS FOR ssss@localhost;
/* returns

Grants for ssss@localhost
——————————————————-
GRANT USAGE ON *.* TO ‘ssss’@’localhost’ REQUIRE SSL
GRANT UPDATE ON `ttest`.* TO ‘ssss’@’localhost’
*/

From the returns from SHOW GRANTS it is clear that SSL settings are global for user (used for authentication) and in GRANT syntax linked to the “USAGE” privilege (or rather “no-privilege acronym”). This is expected, it is documented but still confuses a lot of people.

This will do:

GRANT USAGE ON ttest.* TO ssss@localhost REQUIRE NONE;
/* returns

Grants for ssss@localhost
———————————————–
GRANT USAGE ON *.* TO ‘ssss’@’localhost’
GRANT UPDATE ON `ttest`.* TO ‘ssss’@’localhost’
*/

(and “GRANT UPDATE ON ttest.* TO ssss@localhost REQUIRE NONE;” would do excatly the same BTW in this case)

The point:

IMHO it is an illogical mess-up that SSL settings for a user are specified along with specific privileges (in a GRANT statement). A more logical way to specify SSL settings for a user would be to have the REQUIRE clause with CREATE USER – ie.

CREATE USER ssss@localhost REQUIRE SSL;
GRANT UPDATE ON ttest.* TO ssss@localhost;

(and then an ALTER USER statement would also be nice)

However historically “GRANT” is a much older statement in MySQL than “CREATE USER” (“CREATE USER” was introduced in MySQL 5.0 and “GRANT” very much earlier). So that is where we are now – trapped by history here we cannot make things logically right unless breaking compability!

I also recognize that many people/clients will never issue a CREATE USER statement as GRANT will create the use implicitly (if not exists) provided that the ‘no_auto_create_user’ SQL-mode  is not SET. But then the option to specify REQUIRE could be in both places.

Similar considerations actually also applies to

Third problem: How to change a password for a user? It may be done like this (not manipulating the mysql.user table directly):

GRANT USAGE ON *.* TO ‘ssss’@’localhost’ identified by ‘newpw’;

Again I think an ALTER USER statement would be the logically correct way:

ALTER USER ‘ssss’@’localhost’ identified by ‘newpw’;

.. as also password is not linked to specific privileges, but to user and authentication as such.

4 Comments

Add yours

+ Leave a Comment