Mysql – Error when using general and specific GRANT

MySQLmysql-5.6permissions

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.

Best Answer

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.

Related Solutions

Mysql – “Set Password” with a limited user

The problem I see with what you propose is that a user who has the ability to grant privileges to other users... is, almost by definition, not a "limited" user.

You can't grant privileges at all without the GRANT privilege, and even with that, you can't grant a specific privilege that you don't possess ... unless you have permission to manipulate the grant tables directly, in which case, you're not exactly a limited user either.

But, aha, here's your workaround.

Stored procedures run with the credentials of the user who defined them or as the explicitly-specified definer. (You have to have SUPER to specify somebody else as DEFINER.) Any user with the EXECUTE privilege on a stored procedure can execute the procedure, and the procedure essentially escalates their privilege level while it is running.

If you wrap your administrative operations in (well-written) procedures, then you don't have to actually give the limited user permission to do anything, other than run the procedures.

DELIMITER $$

DROP PROCEDURE IF EXISTS `mysql`.`change_password` $$
-- root@localhost is an example; use an appropriate local user that has the
-- permissions that need to be available for the operation to succeed
CREATE DEFINER='root'@'localhost' PROCEDURE `mysql`.`change_password` (
  IN dirty_user VARCHAR(16), 
  IN dirty_host VARCHAR(40), 
  IN dirty_password VARCHAR(41))
BEGIN

  DECLARE encrypted_password TINYTEXT DEFAULT PASSWORD(dirty_password);
  DECLARE clean_user TINYTEXT DEFAULT NULL;
  DECLARE clean_host TINYTEXT DEFAULT NULL;

  SELECT user, host
    FROM mysql.user
   WHERE user = dirty_user
     AND host = dirty_host
    INTO clean_user, clean_host;

  IF clean_user IS NULL OR clean_host IS NULL THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'user/host provided does not exist';
  END IF;

  SET @_sql = CONCAT_WS('\'','SET PASSWORD FOR ',clean_user,'@',clean_host,' = ',encrypted_password,'');

  PREPARE stmt FROM @_sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
  SET @_sql = NULL;

END $$

DELIMITER ;

GRANT EXECUTE ON PROCEDURE mysql.change_password TO 'limited'@'%';

The 'limited'@'%' user can now change passwords for other users even though they don't have permission to do it themselves, by using CALL mysql.change_password('user','host','password');.

This user does not have permissions themselves, but does have execute on the proc; they can't do it directly:

mysql> set password for 'wombat'@'localhost' = PASSWORD('secret');
ERROR 1044 (42000): Access denied for user 'limited'@'%' to database 'mysql'

... but they can do it this way. Note that "0 rows affected" is not a meaningful value.

mysql> call mysql.change_password('wombat','localhost','secret');

Query OK, 0 rows affected (0.00 sec)

Notes:

String-concatenated queries with input parameters are unacceptable, but the SET PASSWORD statement does not accept ? positional-parameters, so there's no choice here. To sanitize the inputs, I take care of the password by encrypting it in advance, outside the prepared statement, and concatenated the encrypted password there; I've sanitized the user and host by actually selecting them from the mysql.user table and using the values I've selected, also outside the prepared statement. If they aren't found, we can't set their password, so we throw an exception using SIGNAL. If they are found, we concatenate the fetched values into the prepared statement, sanitizing them by this mechanism. It's still theoretically possible that bad data in the mysql.user table could render the quoting of the prepared statement invalid, but if you have bad data in the mysql.user table, then you have 2 problems (1 problem in addition to this one).
Any explicit GRANT EXECUTE you've given at the procedure level disappears from the mysql.procs_priv table if you drop and redefine the procedure, so if you make changes to the procedure, you have to re-grant the privileges to the limited user.
You need MySQL 5.5+ for the SIGNAL statement to be valid. Below 5.5 you can replace it with a hack, something like CALL mysql.`change_password(): the user/host provided`; (note the backticks) which will throw a not quite as pretty, but still serviceable message complaining that the bogus procedure name quoted in the backticks... does not exist. Heh... ERROR 1305 (42000): PROCEDURE mysql.change_password(): the user/host provided does not exist

Mysql – revoking thesql table level privileges

Use this script to help you create the grant script syntax : Run a root od mysql admin user.

  select 'grant all privileges on ', table_name, 'to 
someuser@somehost;' from information_schema.tables where table_schema = 'database name' and 
table_name not in ('table without access', 'table without access');

This will create an output that you can run after.

Just to replicate the error :

mysql> grant select on BASE_BIB.* to test123@'%' identified by 'test';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> use BASE_BIB;
Database changed
mysql> show tables ;

Now revoke select from a table form inside the BASE_BIB database:

mysql> revoke select on BASE_BIB.users from test123@'%';
ERROR 1147 (42000): There is no such grant defined for user 'test123'

on host '%' on table 'users'

No the right way to do it :

mysql> grant select on BASE_BIB.users to test123@'%' identified by 'test';
Query OK, 0 rows affected (0.00 sec)

mysql> revoke select on BASE_BIB.users from test123@'%';
Query OK, 0 rows affected (0.00 sec)

No error now !!

Managing access in mysql can be quite dificult !!

Once you gave him database.* you cannot revoke access for an object that is in that class. MySQL doesn't expand the Hotels.* wildcard to the individual tables The permissions tables store the granted permissions. Therefore, since you didn't actually grant anything on Hotels.AllHotels , there's nothing for MySQL to revoke. In this case you need to do it granular form the start !

Remove all privileges on database, table, column levels, etccc.

Grant privileges to EACH table, except 'you choose'.
Grant privilege to specified fields in table 'you choose'.

Best Answer

UPDATE 2017-08-02 13:20 EDT

Related Solutions

Mysql – “Set Password” with a limited user

Mysql – revoking thesql table level privileges

Related Question