Description:
Given a user that has some table-level privileges, replacing those with equivalent schema-level privileges causes access denied errors until the user reconnects. During this time, the output of "SHOW GRANTS" is contradicted by actual behavior. Executing FLUSH PRIVILEGES does not alleviate the problem.
Step by step description of problem:
* grant only table-level privs to a user
* that user connects
* grant the same privs at the database-level to that user
* delete entries from mysql.tables_priv & flush privileges;
* the user is now denied those privileges, even though executing "SHOW GRANTS" within that user's connection claims they should have it.
* the user re-connects, and the privileges are working
Step by step description of alternate order of operations which does not result in the unexpected behavior:
* grant table-level privs
* also grant db-level privs
* user connects
* delete entries from mysql.tables_priv & flush privileges;
* user encounters no errors
How to repeat:
Here are sample commands copied from two mysql sessions, one as user "root", the other as user "foo", demonstrating the problem and resulting error message.
### create table and user, grant insert on table level
master [localhost] {root} (mysql) > create table test.t (a int not null primary key) engine=innodb;
Query OK, 0 rows affected (0.24 sec)
master [localhost] {root} (mysql) > grant select on test.* to 'foo'@'localhost';
Query OK, 0 rows affected (0.00 sec)
master [localhost] {root} (mysql) > grant insert on test.t to 'foo'@'localhost';
Query OK, 0 rows affected (0.00 sec)
### confirm access, insert a row as the "foo" user
master [localhost] {foo} (test) > show grants;
+-------------------------------------------------+
| Grants for foo@localhost |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO 'foo'@'localhost' |
| GRANT SELECT ON `test`.* TO 'foo'@'localhost' |
| GRANT INSERT ON `test`.`t` TO 'foo'@'localhost' |
+-------------------------------------------------+
3 rows in set (0.00 sec)
master [localhost] {foo} (test) > select * from t;
Empty set (0.00 sec)
master [localhost] {foo} (test) > insert into t values (1);
Query OK, 1 row affected (0.26 sec)
### grant insert on schema level, and remove insert on table level
master [localhost] {root} (mysql) > grant insert on test.* to 'foo'@'localhost';
Query OK, 0 rows affected (0.00 sec)
master [localhost] {root} (mysql) > delete from mysql.tables_priv where user='foo';
Query OK, 1 row affected (0.00 sec)
master [localhost] {root} (mysql) > flush privileges;
Query OK, 0 rows affected (0.00 sec)
### observe changed privileges as "foo" user
### but "foo" is unable to exercise those privileges!
master [localhost] {foo} (test) > show grants;
+-------------------------------------------------------+
| Grants for foo@localhost |
+-------------------------------------------------------+
| GRANT USAGE ON *.* TO 'foo'@'localhost' |
| GRANT SELECT, INSERT ON `test`.* TO 'foo'@'localhost' |
+-------------------------------------------------------+
2 rows in set (0.00 sec)
master [localhost] {foo} (test) > insert into t values (2);
ERROR 1142 (42000): INSERT command denied to user 'foo'@'localhost' for table 't'
### reconnect, and now it works...
master [localhost] {foo} (test) > insert into t values (2);
Query OK, 1 row affected (0.41 sec)
Description: Given a user that has some table-level privileges, replacing those with equivalent schema-level privileges causes access denied errors until the user reconnects. During this time, the output of "SHOW GRANTS" is contradicted by actual behavior. Executing FLUSH PRIVILEGES does not alleviate the problem. Step by step description of problem: * grant only table-level privs to a user * that user connects * grant the same privs at the database-level to that user * delete entries from mysql.tables_priv & flush privileges; * the user is now denied those privileges, even though executing "SHOW GRANTS" within that user's connection claims they should have it. * the user re-connects, and the privileges are working Step by step description of alternate order of operations which does not result in the unexpected behavior: * grant table-level privs * also grant db-level privs * user connects * delete entries from mysql.tables_priv & flush privileges; * user encounters no errors How to repeat: Here are sample commands copied from two mysql sessions, one as user "root", the other as user "foo", demonstrating the problem and resulting error message. ### create table and user, grant insert on table level master [localhost] {root} (mysql) > create table test.t (a int not null primary key) engine=innodb; Query OK, 0 rows affected (0.24 sec) master [localhost] {root} (mysql) > grant select on test.* to 'foo'@'localhost'; Query OK, 0 rows affected (0.00 sec) master [localhost] {root} (mysql) > grant insert on test.t to 'foo'@'localhost'; Query OK, 0 rows affected (0.00 sec) ### confirm access, insert a row as the "foo" user master [localhost] {foo} (test) > show grants; +-------------------------------------------------+ | Grants for foo@localhost | +-------------------------------------------------+ | GRANT USAGE ON *.* TO 'foo'@'localhost' | | GRANT SELECT ON `test`.* TO 'foo'@'localhost' | | GRANT INSERT ON `test`.`t` TO 'foo'@'localhost' | +-------------------------------------------------+ 3 rows in set (0.00 sec) master [localhost] {foo} (test) > select * from t; Empty set (0.00 sec) master [localhost] {foo} (test) > insert into t values (1); Query OK, 1 row affected (0.26 sec) ### grant insert on schema level, and remove insert on table level master [localhost] {root} (mysql) > grant insert on test.* to 'foo'@'localhost'; Query OK, 0 rows affected (0.00 sec) master [localhost] {root} (mysql) > delete from mysql.tables_priv where user='foo'; Query OK, 1 row affected (0.00 sec) master [localhost] {root} (mysql) > flush privileges; Query OK, 0 rows affected (0.00 sec) ### observe changed privileges as "foo" user ### but "foo" is unable to exercise those privileges! master [localhost] {foo} (test) > show grants; +-------------------------------------------------------+ | Grants for foo@localhost | +-------------------------------------------------------+ | GRANT USAGE ON *.* TO 'foo'@'localhost' | | GRANT SELECT, INSERT ON `test`.* TO 'foo'@'localhost' | +-------------------------------------------------------+ 2 rows in set (0.00 sec) master [localhost] {foo} (test) > insert into t values (2); ERROR 1142 (42000): INSERT command denied to user 'foo'@'localhost' for table 't' ### reconnect, and now it works... master [localhost] {foo} (test) > insert into t values (2); Query OK, 1 row affected (0.41 sec)