GRANTS using general and specific permissions
Posted by: Mauricio Cacho Gutierrez
Date: August 14, 2017 07:20AM

I came across with something in permissions and can't know if it's the expected behaviour or there's something missing:
If I give an user GRANT SELECT on wild card schema like "schema_%" and then I give him INSERT and UPDATE in specifig 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)







This is happening with MySQL Community Server 5.6.36 in Centos 7.3.1611


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.




I was going to issue this as a bug; but I don't know if I'm wrong and this is the expected behaviour; I think MySQL should do something like "merge" all the GRANTS involving, and looks like it's getting the first permission (the SELECT) required and stops looking for more (INSERT, SELECT, UPDATE later).
Hope anyone can help me.

Options: ReplyQuote


Subject
Written By
Posted
GRANTS using general and specific permissions
August 14, 2017 07:20AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.