MySQL Bugs: #728: GRANT ON "%" IDENTIFIED BY does not enforce password checking

Bug #728	GRANT ON "%" IDENTIFIED BY does not enforce password checking
Submitted:	26 Jun 2003 1:42	Modified:	15 Oct 2003 13:11
Reporter:	Charl Gerber	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.0.13	OS:	Windows (Windows 2000 & XP)
Assigned to:		CPU Architecture:	Any

Description:
Logging in as user root (mysql command line tool), I issue the following command:

GRANT ALL ON *.* TO charl@"%" IDENTIFIED BY "gerber"
(also tried)
GRANT ALL ON *.* TO charl IDENTIFIED BY "gerber"

If I now attempt to log on as "charl" and supply password "gerber", I get access denied. However, if I supply NO password, the user is allowed access!

I tried logging in through the mysql command line program, the Windows GUI and a Tomcat server database connection pool, all with the same results. All these clients run on the same machine as the MySQL server (I assume localhost is then the host).

I would have expected that the "%" includes all hosts (also localhost) and that supplying no password from ANY host (including localhost) would fail. This allows a security breach and unauthorised access to data when no password is provided where one should have been provided.

The expected behaviour is seen when I explicitly specify the host:
GRANT AL ON *.* TO charl@"localhost" IDENTIFIED BY "gerber".
Then the password "gerber" must be supplied, otherwise I get access errors.

How to repeat:
Follow the SQL commands above. I did it repeatedly on Windows XP and Windows 2000 systems, everytime with the same result.

Suggested fix:
The "%" host should also include "localhost". If no password is specified for localhost when for "%" one is, then logging in from localhost with no password should fail.

Tried this with 4.0.13 and this is how it worked:

mysql> GRANT ALL ON *.* TO charl@"%" IDENTIFIED BY "gerber";
Query OK, 0 rows affected (0.02 sec)

mysql> quit
Bye
[/mnt/work/mysql-4.0]$ ./client/mysql -u charl -pgerber
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.0.14-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> quit
Bye

Can you try command line options, like above ???

Also, try, after connecting the command

SELECT CURRENT_USER()

to be sure you are connected as charl@%

It works from the command line now (???). I'll check again on the web server, as that was the main problem area (the connection pool could not be initialized because of security).

I'm experiencing this bug with 4.0.15a on Linux.  (Slackware 9.1).  This happens with both the slackware package and the mysql-standard binary downloaded from
mysql.com.  Here's how I reproduce it:

------------------BEGIN-------------------------

mysql> GRANT ALL ON *.* TO charl@"%" IDENTIFIED BY "gerber";
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye
moscow#./bin/mysql -u charl -pgerber
ERROR 1045: Access denied for user: 'charl@localhost' (Using password: YES)
moscow#./bin/mysql -u charl         
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 4.0.15-standard

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| @localhost     |
+----------------+
1 row in set (0.00 sec)

mysql> quit
Bye
moscow#./bin/mysql --user=charl -p   
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9 to server version: 4.0.15-standard

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| @localhost     |
+----------------+
1 row in set (0.00 sec)

------------------END-------------------------

Note that on the last command I just hit enter when promted for the password.

From the looks of things, the -u/--user flag is being ignored by the client
in unless I actually specify a password.

Password authorization does work if I use 'localhost' for the Host field, but 
as soon as I use '%' in the user.Host column this breakage occurs.

This is not a bug. You're being authenticated as the anonymous
user due to the way that the server looks through user table
records:

http://www.mysql.com/doc/en/Connection_access.html

Delete the anonymous user accounts and try again. Connect
as root, then issue these statements:

mysql> DELETE FROM mysql.user WHERE User = '';
mysql> FLUSH PRIVILEGES;

Then try connecting as charl again.

Note that if there are two records in the mysql.user table: One for '%' and one for 'localhost', this problem goes away.  I think the real bugs here are:

1) user.Host = '%' means Host is anything BUT localhost.
2) The client reverts to user '' if there is no matching user for the given 
   hostname in mysql.user.

Sorry, but there is still no bug. The behaviors you
describe are consequences of the way that the
server looks through user table records:

http://www.mysql.com/doc/en/Connection_access.html

% does match localhost. What you're seeing is that
there is another record that occurs earlier in the
sort list that also matches localhost.