MySQL Bugs: #33241: Wrong privileges used if there are different for same user name IP and host

Bug #33241	Wrong privileges used if there are different for same user name IP and host
Submitted:	14 Dec 2007 12:48	Modified:	10 Dec 2008 8:44
Reporter:	Sveta Smirnova	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S3 (Non-critical)
Version:	5.0.45, 5.1 BK	OS:	Any (Linux (x86_64))
Assigned to:		CPU Architecture:	Any

Description:
If there are different privileges for user with same name and different host (hostname and IP of this host) wrong privileges are used if compare to SHOW GRANTS output.

This only happens after upgrade or with old versions.

How to repeat:
With version 5.0.27:

ssmirnova@foobar ~/mysql-max-5.0.27-linux-x86_64-glibc23
$./bin/mysqld --defaults-file=support-files/my-small.cnf --basedir=. --datadir=./data --log-error --socket=/tmp/mysql_ssmirnova.sock --port=33050 &
[1] 15769

ssmirnova@foobar ~/mysql-max-5.0.27-linux-x86_64-glibc23
$./bin/resolveip foobar
IP address of foobar is 192.168.0.1

ssmirnova@foobar ~/mysql-max-5.0.27-linux-x86_64-glibc23
$ ./bin/mysql -uroot --socket=/tmp/mysql_ssmirnova.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.27-max

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

mysql> create database qa001;
Query OK, 1 row affected (0.01 sec)

mysql> use qa001
Database changed
mysql> create table ecfoo(id int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into ecfoo values(1);
Query OK, 1 row affected (0.01 sec)

mysql> \q
Bye

ssmirnova@foobar ~/mysql-max-5.0.27-linux-x86_64-glibc23
$ ./bin/mysql -uroot --socket=/tmp/mysql_ssmirnova.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.27-max

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

mysql> grant select on qa001.* to 'ec'@'foobar';
Query OK, 0 rows affected (0.03 sec)

mysql> grant insert on qa001.* to 'ec'@'192.168.0.1';
Query OK, 0 rows affected (0.00 sec)

mysql> grant select on qa001.* to 'ec'@'foobar.mysql.com';
Query OK, 0 rows affected (0.00 sec)

mysql> \q
Bye

ssmirnova@foobar ~/mysql-max-5.0.27-linux-x86_64-glibc23
$./bin/mysql -hfoobar -P33050 -uec qa001
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14 to server version: 5.0.27-max

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

mysql> select user(), current_user();
+----------------------+----------------------+
| user() | current_user() |
+----------------------+----------------------+
| ec@foobar.mysql.com | ec@foobar.mysql.com |
+----------------------+----------------------+
1 row in set (0.00 sec)

mysql> use qa001
Database changed
mysql> select * from ecfoo;
ERROR 1142 (42000): SELECT command denied to user 'ec'@'foobar.mysql.com' for table 'ecfoo'
mysql> insert into ecfoo values(2);
Query OK, 1 row affected (0.00 sec)

mysql> show grants;
+-------------------------------------------------------+
| Grants for ec@foobar.mysql.com |
+-------------------------------------------------------+
| GRANT USAGE ON *.* TO 'ec'@'foobar.mysql.com' |
| GRANT SELECT ON `qa001`.* TO 'ec'@'foobar.mysql.com' |
+-------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> \q
Bye

With version 5.0.45:

ssmirnova@foobar ~/foobar/mysql-5.0.45-linux-x86_64-glibc23
$cp -r ../../mysql-max-5.0.27-linux-x86_64-glibc23/data/ .

ssmirnova@foobar ~/foobar/mysql-5.0.45-linux-x86_64-glibc23
$./bin/mysqld --defaults-file=support-files/my-small.cnf --basedir=. --datadir=./data --log-error --socket=/tmp/mysql_ssmirnova.sock --port=33050 &
[1] 30146

ssmirnova@foobar ~/foobar/mysql-5.0.45-linux-x86_64-glibc23
$./bin/mysql_upgrade -hfoobar -P33050 -uroot
<snip>
OK

ssmirnova@foobar ~/foobar/mysql-5.0.45-linux-x86_64-glibc23
$./bin/mysql -hfoobar -P33050 -uec qa001
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.0.45 MySQL Community Server (GPL)

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

mysql> show grants;
+-------------------------------------------------------+
| Grants for ec@foobar.mysql.com                       |
+-------------------------------------------------------+
| GRANT USAGE ON *.* TO 'ec'@'foobar.mysql.com'        | 
| GRANT SELECT ON `qa001`.* TO 'ec'@'foobar.mysql.com' | 
+-------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> select * from ecfoo;
ERROR 1142 (42000): SELECT command denied to user 'ec'@'foobar.mysql.com' for table 'ecfoo'
mysql> \q
Bye

Workaround: update grant tables to only use IP

data directory

Attachment: data_bug33241.tar.gz (application/x-gzip, text), 119.79 KiB.

Sveta, you have two records with Db='qa001' and User='ec' in the mysql.db.
One with IP address that does prohibit SELECTs
and another with host name that allows.

"... The server sorts the db table based on the Host, Db, and User scope columns, and sorts the host table based on the Host and Db scope columns. As with the user table, sorting puts the most-specific values first and least-specific values last, and when the server looks for matching entries, it uses the first match that it finds."
( http://dev.mysql.com/doc/refman/5.0/en/request-access.html )

So the first one is the mysql.db record with IP that forbids such SELECTs.

In my opinion problem is what user have not clear information which rights she uses: rights of user@host_name or user@IP. So bug can be in:

1. How MySQL check grants when perform particular query.
2. Work of CURRENT_USER (if it chooses wrong user)
3. Work of SHOW GRANTS (it does not show grants in the same manner how MySQL uses it when perform query).
4. Documentation which does not clearly say "if you have both IP and host_name privileges defined behavior is undefined."
5. mysql_upgrade does not upgrade privileges tables in a way they would work smoothly

In my opinion more likely problem is 1. or 3. or 5. What to fix does not matter for me, because either would solve the problem.

Not 4, because 1) problem exists only if use data directory created by previous version of MySQL, 2) with correct privilege tables there is no such inconsistency and 3) it is dangerous to not have clear behavior which privileges to choose.

Not 2, because it is possible to have different rights for different objects based on user@host_name and user@IP identification, although current_user() should return only value.

And sorry, but not better test case, because problem is only repeatable with particular privilege tables.