Bug #24988 FLUSH PRIVILEGES causes brief unavailability
Submitted: 12 Dec 2006 1:02 Modified: 25 Jun 2007 16:21
Reporter: Kolbe Kegel Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Security: Privileges Severity:S2 (Serious)
Version:5.0.30 OS:Any
Assigned to: Kristofer Pettersson CPU Architecture:Any
Tags: bfsm_2006_12_21, flush privileges

[12 Dec 2006 1:02] Kolbe Kegel
Description:
Repeatedly running FLUSH PRIVILEGES in one thread and a simple SELECT statement in another will cause the SELECT to fail intermittently with Access denied errors.

Access denied errors occurring unnecessarily could cause Denial of Service, application failure, or significant administrative headaches.

How to repeat:
create database if not exists db1;
create table db1.t1 (id int);
GRANT SELECT ON db1.t1 TO 'bugtest'@'localhost';

shella$ while true; do mysql -uroot -e "FLUSH PRIVILEGES;"; done

shellb$ while true; do echo 'select count(*) from t1;' ; done | mysql -f -u bugtest -h 127.0.0.1 db1 >/dev/null

ERROR 1044 (42000) at line 449: Access denied for user 'bugtest'@'localhost' to database 'db1'
ERROR 1044 (42000) at line 2611: Access denied for user 'bugtest'@'localhost' to database 'db1'
ERROR 1044 (42000) at line 2766: Access denied for user 'bugtest'@'localhost' to database 'db1'
ERROR 1044 (42000) at line 3861: Access denied for user 'bugtest'@'localhost' to database 'db1'
ERROR 1044 (42000) at line 4101: Access denied for user 'bugtest'@'localhost' to database 'db1'
ERROR 1044 (42000) at line 4267: Access denied for user 'bugtest'@'localhost' to database 'db1'
ERROR 1044 (42000) at line 4305: Access denied for user 'bugtest'@'localhost' to database 'db1'

Suggested fix:
It should not be possible for the MySQL server to generate an "Access denied" or any other privilege-related error if the privilege tables are not fully loaded or in an otherwise inconsistent state.
[27 Feb 2007 18:41] Kolbe Kegel
Still repeatable in 5.0-bk just built...

kolbe@beluga:~/MySQL/inst/5.0-bk> cat | ./bin/mysql
create database if not exists db1;
create table db1.t1 (id int);
GRANT SELECT ON db1.t1 TO 'bugtest'@'localhost';

kolbe@beluga:~/MySQL/inst/5.0-bk> while true; do mysql -uroot -e "FLUSH PRIVILEGES;"; done &
[2] 4148

kolbe@beluga:~/MySQL/inst/5.0-bk> while true; do echo 'select count(*) from t1;' ; done | mysql -f -u bugtest -h 127.0.0.1 db1 >/dev/null
ERROR 1044 (42000) at line 425: Access denied for user 'bugtest'@'localhost' to database 'db1'
ERROR 1044 (42000) at line 588: Access denied for user 'bugtest'@'localhost' to database 'db1'
ERROR 1044 (42000) at line 678: Access denied for user 'bugtest'@'localhost' to database 'db1'
ERROR 1044 (42000) at line 1097: Access denied for user 'bugtest'@'localhost' to database 'db1'
ERROR 1044 (42000) at line 1351: Access denied for user 'bugtest'@'localhost' to database 'db1'
ERROR 1044 (42000) at line 1428: Access denied for user 'bugtest'@'localhost' to database 'db1'
ERROR 1044 (42000) at line 1532: Access denied for user 'bugtest'@'localhost' to database 'db1'
ERROR 1044 (42000) at line 1635: Access denied for user 'bugtest'@'localhost' to database 'db1'
ERROR 1044 (42000) at line 1688: Access denied for user 'bugtest'@'localhost' to database 'db1'
ERROR 1044 (42000) at line 2508: Access denied for user 'bugtest'@'localhost' to database 'db1'
ERROR 1044 (42000) at line 3434: Access denied for user 'bugtest'@'localhost' to database 'db1'
ERROR 1044 (42000) at line 5147: Access denied for user 'bugtest'@'localhost' to database 'db1'
ERROR 1044 (42000) at line 5275: Access denied for user 'bugtest'@'localhost' to database 'db1'
ERROR 1044 (42000) at line 5528: Access denied for user 'bugtest'@'localhost' to database 'db1'
ERROR 1044 (42000) at line 6534: Access denied for user 'bugtest'@'localhost' to database 'db1'
ERROR 1044 (42000) at line 6573: Access denied for user 'bugtest'@'localhost' to database 'db1'
ERROR 1044 (42000) at line 7383: Access denied for user 'bugtest'@'localhost' to database 'db1'
ERROR 1044 (42000) at line 8780: Access denied for user 'bugtest'@'localhost' to database 'db1'
ERROR 1044 (42000) at line 8920: Access denied for user 'bugtest'@'localhost' to database 'db1'
ERROR 1044 (42000) at line 9046: Access denied for user 'bugtest'@'localhost' to database 'db1'
ERROR 1044 (42000) at line 9100: Access denied for user 'bugtest'@'localhost' to database 'db1'
ERROR 1044 (42000) at line 9283: Access denied for user 'bugtest'@'localhost' to database 'db1'
ERROR 1044 (42000) at line 9601: Access denied for user 'bugtest'@'localhost' to database 'db1'
[28 May 2007 11:50] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/27455

ChangeSet@1.2489, 2007-05-28 13:50:33+02:00, thek@adventure.(none) +2 -0
  Bug#24988 FLUSH PRIVILEGES causes brief unavailability
  - A race condition caused brief unavailablility when trying to acccess
    a table.
  - The unprotected variable 'grant_option' wasn't intended to alternate
    during normal execution. Variable initialization moved to grant_init
    a lines responsible for the alternation are removed.
[28 May 2007 11:51] Konstantin Osipov
Approved the original patch in 5.1, as it's a minimal change.
Requested to remove grant_option altogether in 5.1, as de-facto it has been doing nothing in the past 3 years.
[28 May 2007 12:08] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/27459

ChangeSet@1.2539, 2007-05-28 14:08:04+02:00, thek@adventure.(none) +8 -0
  Bug#24988 FLUSH PRIVILEGES causes brief unavailability
  - A race condition caused brief unavailablility when trying to acccess
    a table. 
  - The variable 'grant_option' was removed to resolve the race condition and
    to simplify the design pattern. This flag was originally intended to optimize
    grant checks.
[1 Jun 2007 19:21] Bugs System
Pushed into 5.0.44
[1 Jun 2007 19:23] Bugs System
Pushed into 5.1.20-beta
[18 Jun 2007 16:06] Paul Dubois
Noted in 5.0.44, 5.1.20 changelogs.

Due to a race condition, executing FLUSH PRIVILEGES in one thread
could cause brief table unavailability in other threads.
[25 Jun 2007 13:55] Michael Widenius
The patch causes a big slowdown compared to 4.1 when there is no table grants.

The original code (or at least logic) has to be added back and patch reworked to fix this issue.

In other words, the logic should be that when we don't have a single table level grant, there should be a minimum impact on the query execution.
[25 Jun 2007 16:21] Konstantin Osipov
Putting back to 'Closed' after discussion with Monty.
A new bug report was reported for the slowdown, which indeed takes place:
Bug#29341 "Performance regression if there are any column-level grants."
[9 May 2008 0:27] Mark Callaghan
This code in mysql_change_db is one of the places where toggling the value of grant_option from TRUE to FALSE during grant_load will produce transient authentication errors.

    if (!(db_access & DB_ACLS) && (!grant_option ||
                                   check_grant_db(thd,db_name)))
    {
      my_error(ER_DBACCESS_DENIED_ERROR, MYF(0),
               sctx->priv_user,
               sctx->priv_host,
               db_name);
      mysql_log.write(thd, COM_INIT_DB, ER(ER_DBACCESS_DENIED_ERROR),
                      sctx->priv_user, sctx->priv_host, db_name);
      my_free(db_name, MYF(0));
      DBUG_RETURN(1);
    }
[9 May 2008 0:44] Mark Callaghan
And this block in check_access is the other place where things go wrong.

  /* grant_option is set if there exists a single table or column grant */
  if (db_access == want_access ||
      (grant_option && !dont_check_global_grants &&
       !(want_access & ~(db_access | TABLE_ACLS | PROC_ACLS))))
    DBUG_RETURN(FALSE);                         /* Ok */
[9 May 2008 6:15] Konstantin Osipov
Marc, do you have a test case? Please do not comment on closed bug reports, developers don't see them.
[9 May 2008 13:46] Mark Callaghan
I reproduced this on a 4 core server with the test case at the start of this bug. I also reproduce this in production, but I can't give you a test case for that. Support found this bug when I described my production problems. The fix for this bug is good enough for me because we always run with grants.