MySQL Bugs: #73622: Existing privileges lost due to the unstable quick sort

Bug #73622	Existing privileges lost due to the unstable quick sort
Submitted:	18 Aug 2014 9:55	Modified:	18 Aug 2014 15:24
Reporter:	zhang yingqiang (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S3 (Non-critical)
Version:	5.5 5.6	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	privileges quicksort

Description:
Mysqld only get the first item matched in acl_dbs from acl_get. Two grants when same user and hostname, but different reg string which can match the same dbname can cover each other. For example "GRANT SELECT on `tt%`.* to 'tt'@'%’;" and "GRANT CREATE, DROP on `tt_`.* to 'tt'@'%’;”. In normal times, The one which has bigger acl_db.sort, take effect. But if the acl_db.sort is equal, the order of the two grants will change due to the unstable quick sort algorithm. And what’s worse, when you revoke some privileges, the order will not change until you restart the server or flush privileges next time.  

How to repeat:
create database ttt;                                                                                                                                                                            [21/32897]
create table ttt.t1(id int);

GRANT SELECT on `aa%`.* to 'tt'@'%';
GRANT SELECT on `dd%`.* to 'tt'@'%';
GRANT CREATE on `aaa`.* to 'tt'@'%';
GRANT SELECT on `bb%`.* to 'tt'@'%';
GRANT SELECT on `cc%`.* to 'tt'@'%';
GRANT SELECT on `tt%`.* to 'tt'@'%';
GRANT CREATE, DROP on `tt_`.* to 'tt'@'%';
show grants for 'tt'@'%';

connect(ua,localhost,tt);
connection ua;
#here we can select data from ttt.t1
select * from ttt.t1;
-- ERROR ER_TABLEACCESS_DENIED_ERROR
create table ttt.t2(id int);

#
# case 1
#
connection default;
#here we add privileges to another db
GRANT SELECT on `ee%`.* to 'tt'@'%';
show grants for 'tt'@'%';

connection ua;
#here we can not select data from ttt.t1
-- ERROR ER_TABLEACCESS_DENIED_ERROR
select * from ttt.t1;
create table ttt.t2(id int);
drop table ttt.t2;

#
# case 2
#
connection default;
#here we remove privileges to another db
REVOKE SELECT on `ee%`.* from 'tt'@'%';
show grants for 'tt'@'%';

connection ua;
#here we still can not select data from ttt.t1
-- ERROR ER_TABLEACCESS_DENIED_ERROR
select * from ttt.t1;
create table ttt.t2(id int);
drop table ttt.t2;

#
# case 3
#
connection default;
# here we reload privileges by flush or restart server
flush privileges;
show grants for 'tt'@'%';
show global variables like 'log_error';

connection ua;
#here we can select data from ttt.t1 again
select * from ttt.t1;
-- ERROR ER_TABLEACCESS_DENIED_ERROR
create table ttt.t2(id int);

connection default;
drop database ttt;
drop user 'tt'@'%';

Suggested fix:

1、using a stable sort algorithm in privileges sorting. 
2、using a union set in acl_get from acl_dbs

create database ttt; 
create table ttt.t1(id int);

GRANT SELECT on `aa%`.* to 'tt'@'%';
GRANT SELECT on `dd%`.* to 'tt'@'%';
GRANT CREATE on `aaa`.* to 'tt'@'%';
GRANT SELECT on `bb%`.* to 'tt'@'%';
GRANT SELECT on `cc%`.* to 'tt'@'%';
GRANT SELECT on `tt%`.* to 'tt'@'%';
GRANT CREATE, DROP on `tt_`.* to 'tt'@'%';
show grants for 'tt'@'%';

connect(ua,localhost,tt);
connection ua;
#here we can select data from ttt.t1
select * from ttt.t1;
-- ERROR ER_TABLEACCESS_DENIED_ERROR
create table ttt.t2(id int);

#
# case 1
#
connection default;
#here we add privileges to another db
GRANT SELECT on `ee%`.* to 'tt'@'%';
show grants for 'tt'@'%';

connection ua;
#here we can not select data from ttt.t1
-- ERROR ER_TABLEACCESS_DENIED_ERROR
select * from ttt.t1;
create table ttt.t2(id int);
drop table ttt.t2;

#
# case 2
#
connection default;
#here we remove privileges to another db
REVOKE SELECT on `ee%`.* from 'tt'@'%';
show grants for 'tt'@'%';

connection ua;
#here we still can not select data from ttt.t1
-- ERROR ER_TABLEACCESS_DENIED_ERROR
select * from ttt.t1;
create table ttt.t2(id int);
drop table ttt.t2;

#
# case 3
#
connection default;
# here we reload privileges by flush or restart server
flush privileges;
show grants for 'tt'@'%';
show global variables like 'log_error';

connection ua;
#here we can select data from ttt.t1 again
select * from ttt.t1;
-- ERROR ER_TABLEACCESS_DENIED_ERROR
create table ttt.t2(id int);

connection default;
drop database ttt;
drop user 'tt'@'%';

Hello Zhang,

Thank you for the report and test case.
Verified as described.

Thanks,
Umesh

Imho, this seems to be the classical case of “_” and “%” wildcards used when specifying database names in GRANT statements that grant privileges at the global or database levels. 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 ... see http://dev.mysql.com/doc/refman/5.6/en/grant.html

Agree