MySQL Bugs: #31194: Privilege ordering does not order properly for wildcard values

Bug #31194	Privilege ordering does not order properly for wildcard values
Submitted:	25 Sep 2007 22:34	Modified:	17 Mar 2008 20:38
Reporter:	Todd Farmer (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S2 (Serious)
Version:	4.1, 5.0.46	OS:	Any
Assigned to:	Gleb Shchepa	CPU Architecture:	Any

Description:
The documentation on the privilege system states that rows are considered from most accurate/restrictive matching to least, but wildcard values are apparently not considered when ordering.  Instead, for accounts that match multiple rows in the privilege tables, the order in which the rows were inserted (and retrieved) appears to define the order, when all other elements are equal.

The documentation states:

"The server reads the db and host tables into memory and sorts them at the same time that it reads the user table. 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

How to repeat:
mysql -uroot -P3307
CREATE DATABASE IF NOT EXISTS privs;
CREATE DATABASE IF NOT EXISTS privs_1;
CREATE TABLE IF NOT EXISTS privs.testing (a INT);
CREATE TABLE IF NOT EXISTS privs_1.testing (a INT);
DROP USER 'privtest'@'%';
CREATE USER 'privtest'@'%';
GRANT USAGE ON *.* TO 'privtest'@'%';
GRANT SELECT ON `privs\_1`.* TO 'privtest'@'%';
GRANT SELECT ON `privs_1`.* TO 'privtest'@'%';
UPDATE mysql.db SET select_priv = 'N' WHERE db = 'privs_1' AND user = 'privtest';

FLUSH PRIVILEGES;

SHOW GRANTS FOR 'privtest'@'%';

SELECT * FROM mysql.db\G

EXIT

mysql -uprivtest -P3307

SELECT * FROM privs_1.testing;
SHOW GRANTS;

EXIT

mysql -uroot -P3307
DELETE FROM mysql.db WHERE user = 'privtest';
FLUSH PRIVILEGES;

# reverse order in which rows are added to table
GRANT SELECT ON `privs_1`.* TO 'privtest'@'%';
GRANT SELECT ON `privs\_1`.* TO 'privtest'@'%';
UPDATE mysql.db SET select_priv = 'N' WHERE db = 'privs_1' AND user = 'privtest';

FLUSH PRIVILEGES;

SHOW GRANTS FOR 'privtest'@'%';

SELECT * FROM mysql.db\G

EXIT

mysql -uprivtest -P3307

SELECT * FROM privs_1.testing;
SHOW GRANTS;

EXIT

Suggested fix:
Consider wildcard values when ordering rows from the privilege tables.

C:\Documents and Settings\Todd>mysql -uroot -P3307
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 76
Server version: 5.0.46-enterprise-gpl-nt-log MySQL Enterprise Server (GPL)

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

mysql> CREATE DATABASE IF NOT EXISTS privs;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE DATABASE IF NOT EXISTS privs_1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS privs.testing (a INT);
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS privs_1.testing (a INT);
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DROP USER 'privtest'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER 'privtest'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT USAGE ON *.* TO 'privtest'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT ON `privs\_1`.* TO 'privtest'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT ON `privs_1`.* TO 'privtest'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE mysql.db SET select_priv = 'N' WHERE db = 'privs_1' AND user = 'privtest';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> SHOW GRANTS FOR 'privtest'@'%';
+------------------------------------------------+
| Grants for privtest@%                          |
+------------------------------------------------+
| GRANT USAGE ON *.* TO 'privtest'@'%'           |
| GRANT SELECT ON `privs\_1`.* TO 'privtest'@'%' |
+------------------------------------------------+
2 rows in set (0.00 sec)

mysql>
mysql> SELECT * FROM mysql.db\G
*************************** 1. row ***************************
                 Host: %
                   Db: privs_1
                 User: privtest
          Select_priv: N
          Insert_priv: N
          Update_priv: N
          Delete_priv: N
          Create_priv: N
            Drop_priv: N
           Grant_priv: N
      References_priv: N
           Index_priv: N
           Alter_priv: N
Create_tmp_table_priv: N
     Lock_tables_priv: N
     Create_view_priv: N
       Show_view_priv: N
  Create_routine_priv: N
   Alter_routine_priv: N
         Execute_priv: N
*************************** 2. row ***************************
                 Host: %
                   Db: privs\_1
                 User: privtest
          Select_priv: Y
          Insert_priv: N
          Update_priv: N
          Delete_priv: N
          Create_priv: N
            Drop_priv: N
           Grant_priv: N
      References_priv: N
           Index_priv: N
           Alter_priv: N
Create_tmp_table_priv: N
     Lock_tables_priv: N
     Create_view_priv: N
       Show_view_priv: N
  Create_routine_priv: N
   Alter_routine_priv: N
         Execute_priv: N
2 rows in set (0.00 sec)

mysql>
mysql> EXIT
Bye

C:\Documents and Settings\Todd>
C:\Documents and Settings\Todd>mysql -uprivtest -P3307
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 77
Server version: 5.0.46-enterprise-gpl-nt-log MySQL Enterprise Server (GPL)

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

mysql>
mysql> SELECT * FROM privs_1.testing;
ERROR 1142 (42000): SELECT command denied to user 'privtest'@'localhost' for table 'testing'
mysql> SHOW GRANTS;
+------------------------------------------------+
| Grants for privtest@%                          |
+------------------------------------------------+
| GRANT USAGE ON *.* TO 'privtest'@'%'           |
| GRANT SELECT ON `privs\_1`.* TO 'privtest'@'%' |
+------------------------------------------------+
2 rows in set (0.00 sec)

mysql>
mysql> EXIT
Bye

C:\Documents and Settings\Todd>
C:\Documents and Settings\Todd>mysql -uroot -P3307
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 78
Server version: 5.0.46-enterprise-gpl-nt-log MySQL Enterprise Server (GPL)

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

mysql> DELETE FROM mysql.db WHERE user = 'privtest';
Query OK, 2 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> # reverse order in which rows are added to table
mysql> GRANT SELECT ON `privs_1`.* TO 'privtest'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT ON `privs\_1`.* TO 'privtest'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE mysql.db SET select_priv = 'N' WHERE db = 'privs_1' AND user = 'privtest';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> SHOW GRANTS FOR 'privtest'@'%';
+------------------------------------------------+
| Grants for privtest@%                          |
+------------------------------------------------+
| GRANT USAGE ON *.* TO 'privtest'@'%'           |
| GRANT SELECT ON `privs\_1`.* TO 'privtest'@'%' |
+------------------------------------------------+
2 rows in set (0.00 sec)

mysql>
mysql> SELECT * FROM mysql.db\G
*************************** 1. row ***************************
                 Host: %
                   Db: privs\_1
                 User: privtest
          Select_priv: Y
          Insert_priv: N
          Update_priv: N
          Delete_priv: N
          Create_priv: N
            Drop_priv: N
           Grant_priv: N
      References_priv: N
           Index_priv: N
           Alter_priv: N
Create_tmp_table_priv: N
     Lock_tables_priv: N
     Create_view_priv: N
       Show_view_priv: N
  Create_routine_priv: N
   Alter_routine_priv: N
         Execute_priv: N
*************************** 2. row ***************************
                 Host: %
                   Db: privs_1
                 User: privtest
          Select_priv: N
          Insert_priv: N
          Update_priv: N
          Delete_priv: N
          Create_priv: N
            Drop_priv: N
           Grant_priv: N
      References_priv: N
           Index_priv: N
           Alter_priv: N
Create_tmp_table_priv: N
     Lock_tables_priv: N
     Create_view_priv: N
       Show_view_priv: N
  Create_routine_priv: N
   Alter_routine_priv: N
         Execute_priv: N
2 rows in set (0.00 sec)

mysql>
mysql> EXIT
Bye

C:\Documents and Settings\Todd>
C:\Documents and Settings\Todd>mysql -uprivtest -P3307
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 79
Server version: 5.0.46-enterprise-gpl-nt-log MySQL Enterprise Server (GPL)

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

mysql>
mysql> SELECT * FROM privs_1.testing;
Empty set (0.00 sec)

mysql> SHOW GRANTS;
+------------------------------------------------+
| Grants for privtest@%                          |
+------------------------------------------------+
| GRANT USAGE ON *.* TO 'privtest'@'%'           |
| GRANT SELECT ON `privs\_1`.* TO 'privtest'@'%' |
+------------------------------------------------+
2 rows in set (0.00 sec)

mysql>
mysql> EXIT
Bye

I'm sorry, I don't understand this report.
1) Could you please abstain from direct updates of the privilege table in your test case? This is not supported.
2) What is the user visible effect that this bug causes?

Hi Kosta!

>  1) Could you please abstain from direct updates of the privilege table in your test case?
This is not supported.

Please direct me to the manual reference that says that such direct updates are unsupported.  There are certain use cases that specifically *require* direct modification of the authorization tables, in fact.  I can understand stating that such actions are not recommended, or not preferred for most tasks, but I don't think it is correct to say that they are unsupported.

Regardless, this issue can be recreated without the direct updates:

mysql -uroot -P3307
CREATE DATABASE IF NOT EXISTS privs;
CREATE DATABASE IF NOT EXISTS privs_1;
CREATE TABLE IF NOT EXISTS privs.testing (a INT);
CREATE TABLE IF NOT EXISTS privs_1.testing (a INT);
DROP USER 'privtest'@'%';
CREATE USER 'privtest'@'%';
GRANT USAGE ON *.* TO 'privtest'@'%';
GRANT SELECT, UPDATE ON `privs_1`.* TO 'privtest'@'%';
REVOKE SELECT ON `privs_1`.* FROM 'privtest'@'%';
GRANT SELECT, UPDATE ON `privs\_1`.* TO 'privtest'@'%';  -- doesn't contain wildcard, is more exact match than previous entries, should match first.

Here's the results of running the above:

C:\Documents and Settings\Todd>mysql -uroot -P3307
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 5.0.46-enterprise-gpl-nt MySQL Enterprise Server (GPL)

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

mysql> CREATE DATABASE IF NOT EXISTS privs;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE DATABASE IF NOT EXISTS privs_1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS privs.testing (a INT);
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS privs_1.testing (a INT);
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DROP USER 'privtest'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER 'privtest'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT USAGE ON *.* TO 'privtest'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT, UPDATE ON `privs_1`.* TO 'privtest'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> REVOKE SELECT ON `privs_1`.* FROM 'privtest'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT, UPDATE ON `privs\_1`.* TO 'privtest'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

C:\Documents and Settings\Todd>mysql -P3307 -uprivtest
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 5.0.46-enterprise-gpl-nt MySQL Enterprise Server (GPL)

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

mysql> select * from privs_1.testing;
ERROR 1142 (42000): SELECT command denied to user 'privtest'@'localhost' for table 'testing'
mysql> show grants;
+--------------------------------------------------------+
| Grants for privtest@%                                  |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO 'privtest'@'%'                   |
| GRANT UPDATE ON `privs_1`.* TO 'privtest'@'%'          |
| GRANT SELECT, UPDATE ON `privs\_1`.* TO 'privtest'@'%' |
+--------------------------------------------------------+
3 rows in set (0.00 sec)

The above behavior is incorrect; it should allow SELECT on privs_1.testing, as there is a row that matches the database name exactly (without wildcards) in mysql.db granting this privilege.

Furthermore, this behavior is entirely dependent on the order in which the privileges were created/revoked:

C:\Documents and Settings\Todd>mysql -uroot -P3307
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 5.0.46-enterprise-gpl-nt MySQL Enterprise Server (GPL)

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

mysql> DROP USER 'privtest'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER 'privtest'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT USAGE ON *.* TO 'privtest'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT, UPDATE ON `privs\_1`.* TO 'privtest'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT, UPDATE ON `privs_1`.* TO 'privtest'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> REVOKE SELECT ON `privs_1`.* FROM 'privtest'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

C:\Documents and Settings\Todd>mysql -P3307 -uprivtest
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 21
Server version: 5.0.46-enterprise-gpl-nt MySQL Enterprise Server (GPL)

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

mysql> select * from privs_1.testing;
Empty set (0.00 sec)

mysql> show grants;
+--------------------------------------------------------+
| Grants for privtest@%                                  |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO 'privtest'@'%'                   |
| GRANT SELECT, UPDATE ON `privs\_1`.* TO 'privtest'@'%' |
| GRANT UPDATE ON `privs_1`.* TO 'privtest'@'%'          |
+--------------------------------------------------------+
3 rows in set (0.00 sec)

The end result is that the privilege system behavior is dependent on the order in which the statements were executed rather than the consistent "most exact match trumps less exact match" algorithm defined in the manual.

The end user can be affected by this as perfectly valid GRANT commands fail to provide the access expected.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/42194

ChangeSet@1.2602, 2008-02-13 15:01:21+04:00, gshchepa@host.loc +3 -0
  Fixed bug#31194: Privilege ordering does not order properly
                   for wildcard values.
  The server ignored escape character before wildcards during
  the calculation of priority values for sorting of a privilege
  list. (Actually the server counted an escape character as an
  ordinary wildcard like % or _). I.e. the table name template
  with a wildcard character like 'tbl_1' had higher priority in
  a privilege list than concrete table name without wildcards
  like 'tbl\_1', and some privileges of 'tbl\_1' was hidden
  by privileges for 'tbl_1'.
  
  The get_sort function has been modified to ignore escaped
  wildcards as usual.

Pushed into 6.0.5-alpha

Pushed into 5.1.24-rc

Pushed into 5.0.60

Noted in 5.0.60, 5.1.24, 6.0.5 changelogs.

When sorting privilege table rows, the server treated escaped
wildcard characters (\% and \_) the same as unescaped wildcard
characters (% and _), resulting in incorrect row ordering.

Also documented for 5.1.23-ndb-6.3.11.