MacLochlainns Weblog

Somebody asked me why you must provide the IDENTIFIED BY password clause on a grant after you’ve already created the user. This is a very good question, I wondered it myself a while back. This blog provides the what, why, and how of it.

The reason you typically must provide the IDENTIFIED BY password clause is that typically you’ve enabled the NO_AUTO_CREATE_USER value in your sql_mode. That parameter is there by default from MySQL 5.0.2 forward. It’s what requires you to use the IDENTIFIED BY password clause.

The parameter enforces a match against user, host, and password columns in the user table of the mysql database. If you disable it, the match is on user and host columns in the user table. If you make a grant to a user without specifying a host and a wildcard (%) host doesn’t exist for that user, the grant will insert a duplicate user. The same user name with a wildcard host trumps the one with a localhost host value for access privileges, which means that user no longer needs a password to gain entry to the database.

The following steps will show and explain what happens in MySQL’s security model.

1. Sign on as the root user, then create a new myuser user:

mysql> CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'myuser'@'localhost';

If you attempt to grant permissions to the myuser without a password value, like this

mysql> GRANT ALL ON sampledb.* TO 'myuser'@'localhost';

You should see an outcome like the following. At least, you will when the NO_AUTO_CREATE_USER value is set in your sql_mode variable.

ERROR 1133 (42000): Can't find any matching row in the user table

If it let you set it, then the NO_AUTO_CREATE_USER value isn’t set. You can check what’s set with this query:

mysql> SELECT @@sql_mode;

You should see this:

+----------------------------------------------------------------+
| @@sql_mode                                                     |
+----------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------+

2. Reset the sql_mode to disable the NO_AUTO_CREATE_USER value, like this (presuming you’ve got the generic MySQL 5.1 settings):

mysql> SET sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';

3. After disabling the restriction, you can grant privileges on a sampledb to the myuser user without the IDENTIFIED BY password clause. You’d use the following syntax:

mysql> GRANT ALL ON sampledb.* TO 'myuser'@'localhost';

Before you sign off and sign on to test your new user, you should now query the USER table. Here’s the query to focus on the three important columns:

mysql> SELECT USER, host, password
    -> FROM USER
    -> WHERE USER = 'myuser';

If you return more than one row, you made an error and most likely left off the @'localhost' or used @'%'. If you made that mistake, you’ll find that disabling the NO_AUTO_CREATE_USER value, can yield multiple entries in the user table for the same user. Worse yet, the user no longer can sign in with their password.

+--------+-----------+-------------------------------------------+
| USER   | host      | password                                  |
+--------+-----------+-------------------------------------------+
| myuser | %         |                                           |
| myuser | localhost | *CBA73BBE5D9AF59311C3F4D7E8C20AA847F7B188 |
+--------+-----------+-------------------------------------------+

If you were to sign off and attempt to sign on with the myuser account password, you’d get the following error message:

ERROR 1045 (28000): Access denied FOR USER 'myuser'@'localhost' (USING password: YES)

You can now sign on as the myuser user only without a password. Fixing it is simple, just drop the user for any host:

mysql> DROP USER 'myuser'@'%';

While disabling the sql_mode parameters makes your job of granting privileges easier, it risks compromising your database. Therefore, avoid it or be careful. If I missed something or got it wrong, let me know.