MySQL Bugs: #55972: Privilege ordering sorts % before more specific entries like %.foo.com

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

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.

i meant: "mysql> create user test@'%.foo.com';"

Thank for the report.

Please send us also connect string you use and output of SELECT USER(), CURRENT_USER();

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)

Thank you for the feedback.

I can not repeat described behavior. Have you upgraded MySQL server and did not run mysql_upgrade?

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?

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;

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).

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`

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