MySQL Bugs: #89007: Issue with grants and wildcards

Bug #89007	Issue with grants and wildcards
Submitted:	21 Dec 2017 10:57	Modified:	23 Jun 2020 13:53
Reporter:	Arnaud Adant	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.7.18	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
As super user :

drop user twg@'%';

create user twg@'%' identified by 'test';

 

grant create, drop on `wildcard_grants%`.* to twg@'%';

grant all privileges on `wildcard_grants`.* to twg@'%';

 

drop database if exists wildcard_grants;

create database wildcard_grants;

use wildcard_grants;

create table t(id int);

mysql -utwg -ptest -hlocalhost wildcard_grants;

 
MariaDB [wildcard_grants]> insert into t values(1);

ERROR 1142 (42000): INSERT command denied to user 'twg'@'localhost' for table 't'

Workaround : escape _ using \_ in the on clause

How to repeat:
As super user :

drop user twg@'%';

create user twg@'%' identified by 'test';

 

grant create, drop on `wildcard_grants%`.* to twg@'%';

grant all privileges on `wildcard_grants`.* to twg@'%';

 

drop database if exists wildcard_grants;

create database wildcard_grants;

use wildcard_grants;

create table t(id int);

mysql -utwg -ptest -hlocalhost wildcard_grants;

 

MariaDB [wildcard_grants]> insert into t values(1);

ERROR 1142 (42000): INSERT command denied to user 'twg'@'localhost' for table 't'

Suggested fix:
Fix the way grants are sorted.

Isn't it explained in the manual, https://dev.mysql.com/doc/refman/5.7/en/grant.html:

"The _ and % wildcards are permitted when specifying database names in GRANT statements that grant privileges at the database level. This means, for example, that if you want to use a _ character as part of a database name, you should specify it as \_ in the GRANT statement, to prevent the user from being able to access additional databases matching the wildcard pattern; for example, GRANT ... ON `foo\_bar`.* TO .... "

Good catch Valeriy. I still think this is a bug because here the end result is less privileges ... which is quite surprising.

Changing the order of the grants is also a workaround.

Is it described that the order of the grants matters ?

Hi Arnaud,

Not sure I follow the "change order" thing? 
Of course if you first give all then it's not really important if you give or not drop/create afterwards since they are included in "all"?

all best
Bogdan

(sorry for unearthing this ticket years after)

I think there is at least something that is not clearly explained in the documentation about how permissions are processed.

If you have grants like that :

GRANT SELECT ON `db\_%`.* TO <user>;
GRANT SELECT, INSERT, UPDATE, DELETE ON `db_foo`.* TO <user>;

The created user will be able to do SELECTs on tables from databases like db_foo or db_bar, which is expected.

However the user won't be able to INSERT/UPDATE/DELETE on tables from db_foo as you would have thought, because it seems that the second GRANT is ignored as first rule matches which is a bit counter-intuitive.

If you reverse the order of the grants, i.e. :

GRANT SELECT, INSERT, UPDATE, DELETE ON `db_foo`.* TO <user>;
GRANT SELECT ON `db\_%`.* TO <user>;

It would work as expected, but it is not very usable this way because you'd need to revoke some permissions to re-add them after which is not a friendly way of handling that.

Hi,

Yes, I agree this could be better documented.
I'll switch this to a documentation bug.

Thanks
Bogdan

After further tests it may only happen in certain cases (maybe related to views), I'll test some more and I will add some comments.

In case of simple tables it seems to work as expected unlike what I said :

MySQL [test_michel]> show grants;
+---------------------------------------------------------------------------+
| Grants for michel@%                                                       |
+---------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'michel'@'%'                                        |
| GRANT SELECT ON `test\_%`.* TO 'michel'@'%'                               |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `test_michel`.`t` TO 'michel'@'%' |
+---------------------------------------------------------------------------+
3 rows in set (0.00 sec)

MySQL [test_michel]> delete from t where id=1;
Query OK, 1 row affected (0.00 sec)

Found it :

MySQL [test_michel]> show grants;
+-------------------------------------------------------------------------+
| Grants for michel@%                                                     |
+-------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'michel'@'%'                                      |
| GRANT SELECT ON `test\_%`.* TO 'michel'@'%'                             |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `test_michel`.* TO 'michel'@'%' |
+-------------------------------------------------------------------------+
3 rows in set (0.00 sec)

MySQL [test_michel]> insert into t set id=5;

The difference with the test in the previous comment is that the second GRANT also uses a * wildcard for the table.

The behaviour is very different in this case and is definitely not what is expected from the user

Sorry forgot the last line :

ERROR 1142 (42000): INSERT command denied to user 'michel'@'10.32.50.52' for table 't'
MySQL [test_michel]> show grants;
+-------------------------------------------------------------------------+
| Grants for michel@%                                                     |
+-------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'michel'@'%'                                      |
| GRANT SELECT ON `test\_%`.* TO 'michel'@'%'                             |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `test_michel`.* TO 'michel'@'%' |
+-------------------------------------------------------------------------+
3 rows in set (0.00 sec)

MySQL [test_michel]> insert into t set id=5;
ERROR 1142 (42000): INSERT command denied to user 'michel'@'xx.xx.xx.xx' for table 't'

Posted by developer:
 
The order in which the server applies granted privileges is described here:

https://dev.mysql.com/doc/refman/8.0/en/request-access.html