Bumped by Community user
3 Corrected word "anybody" at the final statement
source | link

Can enubodyanybody 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.

Can enubody 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.

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.

2 Removing mysql-5.7 tag since there is no mention of MySQL 5.7 in the question.
| link
1
source | link

Error when using general and specific GRANT

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 enubody 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.