| Bug #75097 | WITH GRANT OPTION not functioning as expect | ||
|---|---|---|---|
| Submitted: | 3 Dec 2014 16:14 | Modified: | 4 Dec 2014 20:00 |
| Reporter: | Ryan Masse | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Security: Privileges | Severity: | S3 (Non-critical) |
| Version: | 5.6.21 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | grant, wildcard | ||
[3 Dec 2014 18:09]
MySQL Verification Team
You actually have to add .... WITH GRANT OPTION in the GRANT command in order that the user in question can grant privileges.
[4 Dec 2014 0:48]
Ryan Masse
My apologies.. I accidentally omitted this in my "How to repeat" code, however in production this user does in fact have this privilege. Can you please have another look? mysql> select user(); +---------------------+ | user() | +---------------------+ | test_user@localhost | +---------------------+ 1 row in set (0.00 sec) mysql> grant all on clientA_test1.* to test_client1@localhost; ERROR 1044 (42000): Access denied for user 'test_user'@'localhost' to database 'clientA_test1' mysql> show grants; +------------------------------------------------------------------------------------------------------------------------+ | Grants for test_user@localhost | +------------------------------------------------------------------------------------------------------------------------+ | GRANT CREATE USER ON *.* TO 'test_user'@'localhost' IDENTIFIED BY PASSWORD '*49D3CEBD189B8C5D4A47C975133BB2357A327585' | | GRANT ALL PRIVILEGES ON `clientA\_%`.* TO 'test_user'@'localhost' WITH GRANT OPTION | +------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql>
[4 Dec 2014 16:54]
MySQL Verification Team
Thank you for your feedback. We need some more test in order to pinpoint the problem. When you are logged as test_user, can you INSERT , UPDATE or SELECT from any table in the clientA_test1 database. Thanks in advance.
[4 Dec 2014 17:08]
Ryan Masse
No problem executing dml running as test_user@localhost. Granting perms to another user still generate error.
mysql> show tables;
+-------------------------+
| Tables_in_clientA_test1 |
+-------------------------+
| settings |
+-------------------------+
1 row in set (0.00 sec)
mysql> select user();
+---------------------+
| user() |
+---------------------+
| test_user@localhost |
+---------------------+
1 row in set (0.00 sec)
mysql> show grants;
+------------------------------------------------------------------------------------------------------------------------+
| Grants for test_user@localhost |
+------------------------------------------------------------------------------------------------------------------------+
| GRANT CREATE USER ON *.* TO 'test_user'@'localhost' IDENTIFIED BY PASSWORD '*49D3CEBD189B8C5D4A47C975133BB2357A327585' |
| GRANT ALL PRIVILEGES ON `clientA\_%`.* TO 'test_user'@'localhost' WITH GRANT OPTION |
+------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> insert into settings(name,value) values('pointsperswipe',10),('intervalbetweenswipe',1),('winningproduct',456);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> update settings set value=50 where name='pointsperswipe';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from settings;
+----------------------+-------+
| name | value |
+----------------------+-------+
| pointsperswipe | 50 |
| intervalbetweenswipe | 1 |
| winningproduct | 456 |
+----------------------+-------+
3 rows in set (0.00 sec)
mysql> grant all on clientA_test1.* to test_client1@localhost;
ERROR 1044 (42000): Access denied for user 'test_user'@'localhost' to database 'clientA_test1'
mysql>
[4 Dec 2014 17:20]
MySQL Verification Team
Hhhhhhmmmmm ..... I must admit that it looks like a bug. We need one last check. Logged as a test_user, instead of: grant all on clientA_test1.* to test_client1@localhost; try: grant all on `clientA\_%`.* to test_client1@localhost; Thanks in advance.
[4 Dec 2014 18:46]
Ryan Masse
Yes this works. Appears to be a confirmed bug. Are you able to reproduce this on your end? mysql> select user(); +---------------------+ | user() | +---------------------+ | test_user@localhost | +---------------------+ 1 row in set (0.00 sec) mysql> show grants; +------------------------------------------------------------------------------------------------------------------------+ | Grants for test_user@localhost | +------------------------------------------------------------------------------------------------------------------------+ | GRANT CREATE USER ON *.* TO 'test_user'@'localhost' IDENTIFIED BY PASSWORD '*49D3CEBD189B8C5D4A47C975133BB2357A327585' | | GRANT ALL PRIVILEGES ON `clientA\_%`.* TO 'test_user'@'localhost' WITH GRANT OPTION | +------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> grant all on clientA_test1.* to test_client1@localhost; ERROR 1044 (42000): Access denied for user 'test_user'@'localhost' to database 'clientA_test1' mysql> grant all on `clientA\_%`.* to test_client1@localhost; Query OK, 0 rows affected (0.02 sec)
[4 Dec 2014 19:57]
MySQL Verification Team
Yes, I can repeat all the steps that you have performed. It is a bug, but a low severity one. It will be transferred to verified bugs and will be scheduled for one of the future versions.
[4 Dec 2014 20:00]
Ryan Masse
Thanks for the confirmation. Will I be notified when/if this gets resolved in the future?
[4 Dec 2014 20:03]
MySQL Verification Team
When the bug gets resolved, pushed , documented and published in a release of some future version, this bug will be closed and you will be notified.
Description: When allowing a user the grant option on a wildcarded database. This user can't in turn grant permissions to another user that matches this wildcard. This user can only grant the exact pattern to which he/she was granted without generating an "Access denied for user" error. Example: We grant the user clientA "with grant option" on "'clientA\_%'.*". We expect that clientA can in turn grant permissions to say the clientA_accounting to userA_1234. This generates an error. What works (odd behavior), is that the user clientA can only regrant the same privileges that was bestowed onto his account ("'clientA\_%'.*") and not target the individual databases that match this pattern. The above has been tested on 5.6.14 and 5.6.21 How to repeat: create database clientA_test1; create database clientA_test2; grant create user on *.* to 'test_user'@localhost identified by 'access'; grant all on `clientA\_%`.* to 'test_user'@localhost; flush privileges; show grants for test_user@localhost; Now log in as "test_user@localhost" create user test_client1@localhost identified by 'access'; grant all on clientA_test1.* to test_client1@localhost; ERROR 1044 (42000): Access denied for user 'test_user'@'localhost' to database 'clientA_test1' THIS SHOULD HAVE WORKED!! WHAT DOES WORK IS: grant all on `clientA\_%`.* to test_client1@localhost; Query OK, 0 rows affected (0.00 sec) mysql> show grants for test_client1@localhost; +---------------------------------------------------------------------------------------------------------------------+ | Grants for test_client1@localhost | +---------------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'test_client1'@'localhost' IDENTIFIED BY PASSWORD '*49D3CEBD189B8C5D4A47C975133BB2357A327585' | | GRANT ALL PRIVILEGES ON `clientA\_%`.* TO 'test_client1'@'localhost' | +---------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) Suggested fix: Appears to warrant a bug fix.