Description:
I came with this situation when using permissions:
If I give an user GRANT SELECT on wild card schema like "schema_%" and then I give him INSERT and UPDATE in specific schema like schema like "schema_user", I get some errors. More detailed situation:
When I start, the user has this GRANTS:
+----------------------------------------------------------------------------------------------------------------+
| Grants for user@% |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user'@'%' IDENTIFIED BY PASSWORD '*XXXXXXXXXXXXXXXXXXXXXX' |
| GRANT SELECT ON `schema_%`.* TO 'user'@'%'; |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `schema_user`.* TO 'user'@'%' |
+----------------------------------------------------------------------------------------------------------------+
And if I issue a UPDATE I get an error:
mysql> UPDATE schema_user.table SET field='some_text';
ERROR 1142 (42000): UPDATE command denied to user 'user'@'X.X.X.X' for table 'table'
BUT if I REVOKE and GRANT the general permissions:
mysql> REVOKE SELECT ON `schema_%`.* FROM user;
mysql> GRANT SELECT ON `schema_%`.* TO user;
And check the GRANTS again, the order changed:
+----------------------------------------------------------------------------------------------------------------+
| Grants for user@% |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user'@'%' IDENTIFIED BY PASSWORD '*XXXXXXXXXXXXXXXXXXXXXX' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `schema_user`.* TO 'user'@'%' |
| GRANT SELECT ON `schema_%`.* TO 'user'@'%'; |
+----------------------------------------------------------------------------------------------------------------+
And now the UPDATE works fine:
mysql> UPDATE schema_user.table SET field='some_text';
Query OK, 0 rows affected (0.09 sec)
How to repeat:
Here's the steps that can lead to replicate my situation:
As root:
CREATE DATABASE demo_priv;
CREATE TABLE demo_priv.tbl (id INT(5) NOT NULL AUTO_INCREMENT PRIMARY KEY, some_text VARCHAR(50));
INSERT INTO demo_priv.tbl(some_text) VALUES ('insert');
CREATE USER test IDENTIFIED BY 'Password';
GRANT SELECT ON `demo_%`.* TO test;
GRANT INSERT, SELECT, UPDATE ON demo_priv.* TO test;
FLUSH PRIVILEGES;
SHOW GRANTS FOR test;
As "test":
UPDATE demo_priv.tbl SET some_text='update';
ERROR 1142 (42000): UPDATE command denied to user 'test'@'X.X.X.X' for table 'tbl'
As root:
REVOKE SELECT ON `demo_%`.* FROM test;
GRANT SELECT ON `demo_%`.* TO test;
And then you should be able to UPDATE.
Suggested fix:
I think MySQL should "merge" all the GRANTS, looks like it's finding the first line with "relevant" permission and stops searching
Description: I came with this situation when using permissions: If I give an user GRANT SELECT on wild card schema like "schema_%" and then I give him INSERT and UPDATE in specific schema like schema like "schema_user", I get some errors. More detailed situation: When I start, the user has this GRANTS: +----------------------------------------------------------------------------------------------------------------+ | Grants for user@% | +----------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'user'@'%' IDENTIFIED BY PASSWORD '*XXXXXXXXXXXXXXXXXXXXXX' | | GRANT SELECT ON `schema_%`.* TO 'user'@'%'; | | GRANT SELECT, INSERT, UPDATE, DELETE ON `schema_user`.* TO 'user'@'%' | +----------------------------------------------------------------------------------------------------------------+ And if I issue a UPDATE I get an error: mysql> UPDATE schema_user.table SET field='some_text'; ERROR 1142 (42000): UPDATE command denied to user 'user'@'X.X.X.X' for table 'table' BUT if I REVOKE and GRANT the general permissions: mysql> REVOKE SELECT ON `schema_%`.* FROM user; mysql> GRANT SELECT ON `schema_%`.* TO user; And check the GRANTS again, the order changed: +----------------------------------------------------------------------------------------------------------------+ | Grants for user@% | +----------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'user'@'%' IDENTIFIED BY PASSWORD '*XXXXXXXXXXXXXXXXXXXXXX' | | GRANT SELECT, INSERT, UPDATE, DELETE ON `schema_user`.* TO 'user'@'%' | | GRANT SELECT ON `schema_%`.* TO 'user'@'%'; | +----------------------------------------------------------------------------------------------------------------+ And now the UPDATE works fine: mysql> UPDATE schema_user.table SET field='some_text'; Query OK, 0 rows affected (0.09 sec) How to repeat: Here's the steps that can lead to replicate my situation: As root: CREATE DATABASE demo_priv; CREATE TABLE demo_priv.tbl (id INT(5) NOT NULL AUTO_INCREMENT PRIMARY KEY, some_text VARCHAR(50)); INSERT INTO demo_priv.tbl(some_text) VALUES ('insert'); CREATE USER test IDENTIFIED BY 'Password'; GRANT SELECT ON `demo_%`.* TO test; GRANT INSERT, SELECT, UPDATE ON demo_priv.* TO test; FLUSH PRIVILEGES; SHOW GRANTS FOR test; As "test": UPDATE demo_priv.tbl SET some_text='update'; ERROR 1142 (42000): UPDATE command denied to user 'test'@'X.X.X.X' for table 'tbl' As root: REVOKE SELECT ON `demo_%`.* FROM test; GRANT SELECT ON `demo_%`.* TO test; And then you should be able to UPDATE. Suggested fix: I think MySQL should "merge" all the GRANTS, looks like it's finding the first line with "relevant" permission and stops searching