Bug #55972 Privilege ordering sorts % before more specific entries like %.foo.com
Submitted: 13 Aug 2010 15:10 Modified: 10 Sep 2010 18:00
Reporter: Moritz Sch?pp Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Security: Privileges Severity:S3 (Non-critical)
Version:5.1.47, 5.0, 5.6.99 OS:Any
Assigned to: CPU Architecture:Any
Tags: host, order, sort, user, wildcard
Triage: Triaged: D2 (Serious)

[13 Aug 2010 15:10] Moritz Sch?pp
Description:
http://dev.mysql.com/doc/refman/5.1/en/connection-access.html states that "The pattern '%' means “any host” and is least specific." but if you have a user that is allowed to connect from "%" and from "%.foo.com" you will always login from "%" even if you connect from "bar.foo.com"

How to repeat:

mysql> create user test@'%';
mysql> create user text@'%.foo.com';

login from bar.foo.com as user "test":

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

Suggested fix:
Sort from most to least specific.
[13 Aug 2010 16:03] Moritz Sch?pp
i meant: "mysql> create user test@'%.foo.com';"
[13 Aug 2010 18:18] Sveta Smirnova
Thank for the report.

Please send us also connect string you use and output of SELECT USER(), CURRENT_USER();
[13 Aug 2010 18:32] Moritz Sch?pp
Hi,

thanks for your feedback.

connect-string:

bar.foo.com:~# mysql -utest -h $mysql-server

mysql> select user(),current_user();
+-------------------------------------+----------------+
| user()                              | current_user() |
+-------------------------------------+----------------+
| test@bar.foo.com                    | test@%         |
+-------------------------------------+----------------+
1 row in set (0.00 sec)
[15 Aug 2010 11:21] Sveta Smirnova
Thank you for the feedback.

I can not repeat described behavior. Have you upgraded MySQL server and did not run mysql_upgrade?
[23 Aug 2010 10:06] Moritz Sch?pp
Yes, MySQL-Server was upgraded and i ran mysql_upgrade before. If i try to run it again:

mysql-server:~# mysql_upgrade
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
This installation of MySQL is already upgraded to 5.1.47, use --force if you still need to run mysql_upgrade

MySQL-Version is:

mysql-server:~# mysql -V
mysql  Ver 14.14 Distrib 5.1.47, for debian-linux-gnu (i486) using readline 5.2

What more information do you need to reproduce?
[23 Aug 2010 17:47] Sveta Smirnova
Thank you for the feedback.

Please try to run mysql_upgrade --force and if problem still exists send us output of SELECT user, host FROM mysql.user;
[30 Aug 2010 13:30] Moritz Sch?pp
I ran mysql_upgrade --force and the problem still persists. Output for user 'test':

mysql> SELECT user, host FROM mysql.user where user='test';
+------+-------------------------+
| user | host                    |
+------+-------------------------+
| test | %                       |
| test | %.foo.com               |
+------+-------------------------+
2 rows in set (0.00 sec)

I noticed that mysql uses the first occurence of '%'. If i drop "test@%" and recreate the user and then reconnect as user test i am logged in to mysql as 'test@%' '(assumably because mysql stops searching for the correct user at the first occurence of '%' in host-field).
[10 Sep 2010 18:00] Sveta Smirnova
Thank you for the feedback.

Which user is choosen depends from order how one creates users. Bug verified.

Test case for MTR:

delete from mysql.user where user='';
flush privileges;

create user test@'%.mysql.com';
create user test@'%';

create user bug@'%';
create user bug@'%.mysql.com';

connect (addcontest, blade12.mysql.com, test,,test,$MASTER_MYPORT);
connection addcontest;
select user(), current_user();

connect (addconbug, blade12.mysql.com, bug,,test,$MASTER_MYPORT);
connection addconbug;
select user(), current_user();

Result:

delete from mysql.user where user='';
flush privileges;
create user test@'%.mysql.com';
create user test@'%';
create user bug@'%';
create user bug@'%.mysql.com';
select user(), current_user();
user()  current_user()
test@blade12.mysql.com  test@%.mysql.com
select user(), current_user();
user()  current_user()
bug@blade12.mysql.com   bug@%

Wrong behavior is either for `test` or for `bug`
[17 Dec 2010 10:57] Arnaud Gadal
Hi Sveta,Moritz,

I've just hit the same one. I've ran test cases on 5.1.51 and 5.1.54, linux debian/CentOS.

My use case :

*** SERVER :  (mysqltest)

/etc/init.d/mysql restart
mysql> select user,host,password from mysql.user;
+-------------+--------------+-------------------------------------------+
| user        | host         | password                                  |
+-------------+--------------+-------------------------------------------+
| root        | localhost    | *667F407DE7C6AD07358FA38DAED7828A72014B4  |
+-------------+--------------+-------------------------------------------+

grant all privileges on test.* to 'arnaud'@'%' identified by 'a';
grant all privileges on test.* to 'arnaud'@'localhost' identified by 'a';
grant all privileges on test.* to 'arnaud'@'%.example.com' identified by 'a';

mysql> select user,host,password from mysql.user order by user;
+-------------+--------------+-------------------------------------------+
| user        | host         | password                                  |
+-------------+--------------+-------------------------------------------+
| arnaud      | %            | *667F407DE7C6AD07358FA38DAED7828A72014B4E |
| arnaud      | localhost    | *667F407DE7C6AD07358FA38DAED7828A72014B4E |
| arnaud      | %.example.com | *667F407DE7C6AD07358FA38DAED7828A72014B4E |
| root        | localhost    | *667F407DE7C6AD07358FA38DAED7828A72014B4E  |
+-------------+--------------+-------------------------------------------+

*** CLIENT :  (mysqldev)

mysql -p -hmysqltest -uarnaud

mysql> select user(),current_user();
+----------------------------+----------------+
| user()                     | current_user() |
+----------------------------+----------------+
| arnaud@mysqldev.example.com | arnaud@%       |
+----------------------------+----------------+
1 row in set (0.00 sec)

*** SERVER :
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

*** CLIENT :
mysql> exit
Bye
[root@mysqldev ~]# mysql -p -hmysqltest -uarnaud
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.1.51-log MySQL Community Server (GPL)

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

mysql> select user(),current_user();
+----------------------------+----------------+
| user()                     | current_user() |
+----------------------------+----------------+
| arnaud@mysqldev.example.com | arnaud@%       |
+----------------------------+----------------+
1 row in set (0.00 sec)

=> flush privileges on server doesn't change anything.

*** SERVER :

mysql> exit
Bye
mysqltest:/data/tmp# /etc/init.d/mysql.server restart
Shutting down MySQL.........
Starting MySQL...

*** CLIENT :
mysql> exit
Bye
[root@mysqldev ~]# mysql -p -hmysqltest -uarnaud
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.51-log MySQL Community Server (GPL)

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

mysql> select user(),current_user();
+----------------------------+----------------+
| user()                     | current_user() |
+----------------------------+----------------+
| arnaud@mysqldev.example.com | arnaud@%       |
+----------------------------+----------------+
1 row in set (0.00 sec)

=> server's restart doesn't change anything either.

*** SERVER :
mysql> grant select on *.* to 'toto'@'%' identified by 'toto';
Query OK, 0 rows affected (0.00 sec)

*** CLIENT :
mysql> exit
Bye
[root@mysqldev ~]# mysql -p -hmysqltest -uarnaud
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.51-log MySQL Community Server (GPL)

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

mysql> select user(),current_user();
+----------------------------+---------------------+
| user()                     | current_user()      |
+----------------------------+---------------------+
| arnaud@mysqldev.example.com | arnaud@%.example.com |
+----------------------------+---------------------+
1 row in set (0.00 sec)

=> the new grant changed the way my client connects itself to the server (!)
=> Some scripts may don't run as normal if privileges differ according to hosts which can lead to break production merely by adding a new GRANT...

Regards,
Arnaud