Description:
And old privilege for a user will change when DBA add new privileges for other user
How to repeat:
First install a new database
Shell> scripts/mysql_install_db --basedir=./ --datadir=./data
Then execute the following SQL:
1. check the db is new with just two records
mysql> select host,db, user from db;
+------+---------+------+
| host | db | user |
+------+---------+------+
| % | test | |
| % | test\_% | |
+------+---------+------+
2 rows in set (0.00 sec)
mysql> GRANT USAGE ON *.* TO 'usera'@'192.168.49.212' IDENTIFIED BY '111111';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON `db_%`.* TO 'usera'@'192.168.49.212';
Query OK, 0 rows affected (46.20 sec)
mysql> GRANT CREATE ON `db_a`.* TO 'usera'@'192.168.49.212';
Query OK, 0 rows affected (23 min 33.57 sec)
mysql> show grants for 'usera'@'192.168.49.212';
+----------------------------------------------------------------------------------------------------------------------+
| Grants for usera@192.168.49.212 |
+----------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'usera'@'192.168.49.212' IDENTIFIED BY PASSWORD '*xxxxxxxxxxxxx' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `db_%`.* TO 'usera'@'192.168.49.212' |
| GRANT CREATE ON `db_a`.* TO 'usera'@'192.168.49.212' |
+----------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
mysql> GRANT CREATE ON `db_a`.* TO 'usera'@'192.168.49.213';
Query OK, 0 rows affected (23 min 33.57 sec)
mysql> GRANT CREATE ON `db_a`.* TO 'usera'@'192.168.49.214';
Query OK, 0 rows affected (23 min 33.57 sec)
mysql> GRANT CREATE ON `db_a`.* TO 'usera'@'192.168.49.215';
Query OK, 0 rows affected (23 min 33.57 sec)
mysql> GRANT CREATE ON `db_a`.* TO 'usera'@'192.168.49.216';
Query OK, 0 rows affected (23 min 33.57 sec)
mysql> GRANT CREATE ON `db_a`.* TO 'usera'@'192.168.49.217';
Query OK, 0 rows affected (23 min 33.57 sec)
mysql> GRANT CREATE ON `db_a`.* TO 'usera'@'192.168.49.218';
Query OK, 0 rows affected (23 min 33.57 sec)
mysql> show grants for 'usera'@'192.168.49.212';
+----------------------------------------------------------------------------------------------------------------------+
| Grants for usera@192.168.49.212 |
+----------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'usera'@'192.168.49.212' IDENTIFIED BY PASSWORD '*xxxxxxxxxxxxx' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `db_%`.* TO 'usera'@'192.168.49.212' |
| GRANT CREATE ON `db_a`.* TO 'usera'@'192.168.49.212' |
+----------------------------------------------------------------------------------------------------------------------+
Here the privileges is not changes, BUT
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'usera'@'192.168.49.212';
+----------------------------------------------------------------------------------------------------------------------+
| Grants for usera@192.168.49.212 |
+----------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'usera'@'192.168.49.212' IDENTIFIED BY PASSWORD '*xxxxxxx' |
| GRANT CREATE ON `db_a`.* TO 'usera'@'192.168.49.212' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `db_%`.* TO 'usera'@'192.168.49.212' |
+----------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
As you see, the CREATE privileges is above the SIUD privielges。
Suggested fix:
I check the code and find the my_qsort will not behaviour the same for different algorithm: insert sort and quick sort。Insert sort will not change the order of the grants if two record have the same sort priority,
but quick sort may change the order, also the "flush privilges" which load the privileges again from the mysql.db
