[MDEV-14732] mysql.db privileges evaluated on order of grants rather than hierarchically Created: 2017-12-21  Updated: 2019-08-22  Resolved: 2019-06-17

Status: Closed
Project: MariaDB Server
Component/s: Authentication and Privilege System
Affects Version/s: 5.5, 10.0, 10.1, 10.2, 10.3
Fix Version/s: 10.4.6

Type: Bug Priority: Major
Reporter: Juan Assignee: Sergei Golubchik
Resolution: Fixed Votes: 1
Labels: upstream
Environment:

CentOS 6, CentOS 7, OSX 10.11

Issue Links:
Duplicate
duplicates MDEV-16466 Inconsistent privileges when changing... Closed
Problem/Incident
causes MDEV-19899 Multiple matching grants no longer st... Closed
Relates
relates to MDEV-8269 Correct fix for Bug #20181776 :- ACCE... Closed
relates to MDEV-14735 better matching order for grants Closed

 Description   

As described by customer:

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'

Interestingly, that works (order of grants) :

drop user twg@'%';
 
create user twg@'%' identified by 'test';
 
 
 
grant all privileges on `wildcard_grants`.* to twg@'%';
 
grant create, drop 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);
 
Query OK, 1 row affected (0.00 sec)

The problem here is that the server is evaluating rows in mysql.db in order of addition rather than in logical order with the more specific database name superseding the more general or vice-versa.

 Comments   
Comment by Sergei Golubchik [ 2017-12-21 ]

Grants are sorted from most specific (no wildcards) to less specific (wildcards only).

The order considers only the prefix up to the first wildcard. Note that the underscore '_' is a wildcard, so both grants have the same "specificity weight" and the order is undefined, practically it happens to be the order in which GRANT statements were issued, but it' not guaranteed.

If you remove the wildcard in the middle:

grant create, drop on `wildcard\_grants%`.* to twg@'%';
 
grant all privileges on `wildcard\_grants`.* to twg@'%';

The more specific (no wildcards) name will be always sorted first, independently from the GRANT statement order.

Comment by Sergei Golubchik [ 2017-12-21 ]

Ideally, the sorting algorithm should recognize that wildcard_grants% is less specific than wildcard_grants, despite them both having a wildcard in the middle.

Comment by Elena Stepanova [ 2018-07-02 ]

I think ideally the system should merge all applicable privileges, that's what an average user probably expects. I don't know if the standard agrees, though.

Comment by Manuel Arostegui [ 2019-06-17 ]

Can this be backported to 10.1 and 10.3 as well?

Comment by Sergei Golubchik [ 2019-06-17 ]

Technically — yes, easily, it's a very local change.

But it does change behavior is some corner cases. For example, the old code could consider hostname pattern www.% to be "more specific" than %.mariadb.org. This is clearly wrong, there are a lot more hosts that match the first pattern than the second, so the second is a lot more specific. And the new code handles it as such. Still it means that after this bugfix a user will have different privileges if these both grants were present in mysql.user table.

Same for databases.

That is why this bugfix was only pushed in 10.4, pre-GA.

Generated at Thu Feb 08 08:15:50 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.