MySQL Bugs: #12008: Escaped wildcard in DB name prevents user from granting database permissions

Bug #12008	Escaped wildcard in DB name prevents user from granting database permissions
Submitted:	18 Jul 2005 14:31	Modified:	22 Aug 2005 21:15
Reporter:	Jonathan Wheeler	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.12	OS:	Linux (Redhat Linux)
Assigned to:	Bugs System	CPU Architecture:	Any

Description:
This is not Bug #7583 and affects versions after 4.1.10a

Database owner with full permissions on database (including grant_priv) is prevented from granting SELECT, INSERT, etc to another user when an escaped wildcard is used in the DB column of the mysql.db table.

Database owner still has all other permissions as far as I can see.

This did not happen in version 4.1.10a and may be related to fix for Grant privilege escalation (CAN-2004-0957).

How to repeat:
Example:  User Alice wants to give Bob read only access on a new database.

Name db in mysql.db to foo\_% to allow user Alice to access and create databases beginning with the foo_ prefix and grant all privs.

insert into mysql.db ( host, db, user, select_priv, insert_priv, update_priv, delete_priv, create_priv, drop_priv, grant_priv, references_priv, Index_priv, alter_priv ) values (         '%', 'foo\_%', 'alice',         'Y','Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y' )

Create new db as Alice named foo_test

Issue the following

Grant select on foo_test.* to bob

Result:

Access Denied

Suggested fix:
1. Revert to previous version 4.1.10a
2. Don't use underscores in database names (it's more trouble than it's worth)

Hello Jonathan,

Thank you for the report.

Can you show us the output of SHOW GRANTS for the user 'Alice'?

Here are the error codes after sql grant:

grant select on foo_test.test to bob

Error Code : 1142
SELECT,GRANT command denied to user 'alice'@'%' for table 'test'
(0 ms taken)

grant select on foo_test.* to bob

Error Code : 1044
Access denied for user 'alice'@'%' to database 'foo_test'
(0 ms taken)

Jonathan, please, show us the output of the following command:

SHOW GRANTS FOR 'alice'@'%'

Sorry, here is the result.

GRANT USAGE ON *.* TO 'alice'@'%' IDENTIFIED BY PASSWORD 'hidden'
GRANT ALL PRIVILEGES ON `foo\_%`.* TO 'alice'@'%' WITH GRANT OPTION

Thanks for your bug report.  I was able to reproduce this under FC4 using 4.1.13 from bk:

root-/usr/local# mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.13

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

mysql> GRANT ALL PRIVILEGES ON `foo\_%`.* TO 'alice'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.02 sec)

mysql> GRANT ALL PRIVILEGES ON `foo\_%`.* TO 'alice'@'localhost' WITH GRANT OPTION; 
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
root-/usr/local# mysql -u alice
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.13

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

mysql> create database foo_test;
Query OK, 1 row affected (0.00 sec)

mysql> use foo_test;
Database changed
mysql> Grant select on foo_test.* to bob
    -> ;
ERROR 1044 (42000): Access denied for user 'alice'@'localhost' to database 'foo_test'
mysql> SHOW GRANTS FOR 'alice';
+---------------------------------------------------------------------+
| Grants for alice@%                                                  |
+---------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'alice'@'%'                                   |
| GRANT ALL PRIVILEGES ON `foo\_%`.* TO 'alice'@'%' WITH GRANT OPTION |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

I'm looking suspiciously at "wild_compare". It seems to only deal with wildcards such as '*' and '?'

char wild_many='*';
char wild_one='?';
char wild_prefix=0; /* QQ this can potentially cause a SIGSEGV */

int wild_compare(register const char *str, register const char *wildstr,
                 pbool str_is_pattern)
{
  char cmp;

Why not '%' and '_' ? It seems it's comparing as if we are dealing with filenames and not SQL.

The check:

 if (str_is_pattern && *str++ != wild_prefix)
          DBUG_RETURN(1);

when running in the debugger plus an added DBUG_PRINT line (gcc optimisations wouldn't show me what was in str properly!!!) showed me that this particular line is performing a comparison that will always pass, because str never contains a '\'. str is the name of the table for which we are trying to give a particular user a grant to.

mysql> grant select on foo\_test.* to bob;
ERROR: 
Unknown command '\_'.
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: *** NONE ***

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\_test.* to bob' at line 1
mysql> grant select on 'foo\_test'.* to bob;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''foo\_test'.* to bob' at line 1
mysql> 

and the fix:

mysql> grant select on foo_test.* to bob;
Query OK, 0 rows affected (13.41 sec)

now running 'make test'

Looking into this further, this is correct behaviour. What you need to do is this:

Grant select on `foo\_test`.* to bob

Doing it the other way, what you are trying to do is grant foo<any character>test.* to bob, and the grant is for 'foo_(underscore)<anycharacter>'