I'm having troubles with GRANTs when trying to use particular permissions on specific database. There's an user that needs SELECT on all schemas starting with some text; and INSERT, SELECT, UPDATE, DELETE on one schema. The thing is, when I give him the permissions, MySQL is giving "UPDATE command denied".

After long research, trying everything that came to my mind, I found that when the order is:

+----------------------------------------------------------------------------------------------------------------+
| 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 I try (with user)

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'

I get the error... BUT if I do:

REVOKE SELECT ON `schema_%`.* FROM user;
GRANT SELECT ON `schema_%`.* TO user;

And check again the GRANTS, the order changed and now it's first the "full" GRANT:

+----------------------------------------------------------------------------------------------------------------+
| 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 I can UPDATE now without error:

mysql> UPDATE schema_user.table SET field='some_text';
Query OK, 0 rows affected (0.09 sec)

The thing now is, everytime I do FLUSH PRIVILEGES; the order is back to the first and "user" can't UPDATE.

This is happening with MySQL Community Server 5.6.36 in Centos 7.3.1611. I tried with my local machine in 5.6.34 and got the same error, but tried again in VM Centos 7.3.1611 and same MySQL version and worked fine.

Here's some script that you can use in order to "replay" my error:

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.

Can anybody help me? I don't know if it's a bug or I'm understanding wrong the privileges in MySQL. Any help would be appreciated.

  • Please run 2 queries and tell us the output : 1) SELECT VERSION();, 2) SELECT COUNT(1) mysql_user_table_columns FROM information_schema.columns WHERE table_schema='mysql' AND table_name='user';. – RolandoMySQLDBA Aug 1 '17 at 19:24
  • 1) 5.6.36-log 2) 43 – Mauricio Cacho Aug 2 '17 at 11:57

You already found the answer to this problem. It all has to do with the order the grants appear.

Let's go back to your first grants display

+----------------------------------------------------------------------------------------------------------------+
| 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'@'%'                                          |
+----------------------------------------------------------------------------------------------------------------+

Here is how the grants are stored:

  • Information from GRANT USAGE is stored in mysql.user
  • Information from the other two lines are stored in mysql.db

To see this order, please run this query:

SELECT * FROM mysql.db WHERE db='schema_user' AND user='user'\G

This will display those grants in whatever order it was entered.

When you deleted the grants and reinserted them using

REVOKE SELECT ON `schema_%`.* FROM user;
GRANT SELECT ON `schema_%`.* TO user;

This reverses the order in the mysql.db table. To prove that, just rerun

SELECT * FROM mysql.db WHERE db='schema_user' AND user='user'\G

after doing the REVOKE and GRANT.

UPDATE 2017-08-02 13:20 EDT

In your comment, you said

I didn't knew of the mysql.db existence... But yes, it looks like it's the order; I suspected that already; but is this the expected behaviour? I would expect MySQL to "merge" the permissions so I didn't have to worry about order, since the grants are made by a SP automatically in here.

The problem here is MySQL will not merge them. For the sake of continuity, you should avoid using wildcards at the database level if your want to manage the grants of a single user. In that instance, you will have to manage the grants of every single user the same way.

  • I didn't knew of the mysql.db existence... But yes, it looks like it's the order; I suspected that already; but is this the expected behaviour? I would expect MySQL to "merge" the permissions so I didn't have to worry about order, since the grants are made by a SP automatically in here. I guess I'll have to modify the SP. – Mauricio Cacho Aug 2 '17 at 12:28
  • I would not trust the "order". The privileges are stored in a MyISAM table, hence, the order can change at the whim of even unrelated actions. I suspect that you are asking for a non-implemented feature. – Rick James Aug 13 '17 at 19:15
  • @RickJames, you are right in saying not to trust the order. In this case, the order is not so much about the storage of the grant in MyISAM. The "whim" may be somewhat predictable. The mysql.db table has PRIMARY KEY (Host,Db,User) and KEY User (User) as indexes. If the display of a user's grants comes from a join between mysql.user and mysql.db, the only usable key for such a join would be the User index of mysql.db. If join rules are coded differently between mysqld releases, well, there is one such whim. Adding an ORDER BY to the grant display query would be another. – RolandoMySQLDBA Aug 13 '17 at 19:33

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.