Cannot grant if Drop_priv removed
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Percona Server moved to https://jira.percona.com/projects/PS |
Invalid
|
Undecided
|
Unassigned |
Bug Description
I noticed I was unable to grant global root perms when Drop_priv = 'N'.
Version: 5.6.28-76.1-log
OS: root@sql03:
Kernel: Linux sql03 3.13.0-74-generic #118-Ubuntu SMP Thu Dec 17 22:52:10 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux
Aside: I had attempted to revoke drop to all users after reading about the Rainforest outage where they dropped their entire db.
To reproduce:
mysql> select user, host, drop_priv from user where user = 'root' and host = '127.0.0.1';
+------
| user | host | drop_priv |
+------
| root | 127.0.0.1 | Y |
+------
1 row in set (0.00 sec)
mysql> update user set Drop_priv = 'N' where user = 'root' and host = '127.0.0.1';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select user, host, drop_priv from user where user = 'root' and host = '127.0.0.1';
+------
| user | host | drop_priv |
+------
| root | 127.0.0.1 | N |
+------
1 row in set (0.00 sec)
mysql> flush privileges ; flush hosts ;
<exit mysql, connect to mysql>
mysql> grant all privileges on *.* to 'new_user'
ERROR 1045 (28000): Access denied for user 'root'@'127.0.0.1' (using password: NO)
mysql> update user set Drop_priv = 'Y' where user = 'root' and host = '127.0.0.1';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> flush privileges ; flush hosts ;
<exit mysql, connect to mysql>
mysql> grant all privileges on *.* to 'new_user'
Query OK, 0 rows affected, 1 warning (0.00 sec)
Changed in percona-server: | |
assignee: | nobody → Muhammad Irfan (muhammad-irfan) |
Users with "Grant_priv" GRANT are able to assign GRANT privileges to other users which is true for root user too which contains WITH GRANT OPTION privilege
Now scenarios you described seems expected behavior to me as DROP privilege enables you to drop (remove) existing databases, tables, and views. So, when root doesn’t have that privilege so failed to assign “ALL PRIVILEGES” which also includes “DROP PRIVILEGE” regardless root have GRANT privilege. However, you can still assign individual privilege excluding DROP privilege.
Let me show you with below example:
mysql> SELECT CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| root@localhost |
+----------------+
mysql> SELECT User, Host, Drop_priv, Grant_priv FROM mysql.user WHERE user='root'; +------ -----+- ------- ---+--- ------- --+ +------ -----+- ------- ---+--- ------- --+ +------ -----+- ------- ---+--- ------- --+
+------
| User | Host | Drop_priv | Grant_priv |
+------
| root | localhost | Y | Y |
+------
1 row in set (0.00 sec)
mysql> SHOW GRANTS FOR 'root'@'localhost'; ------- ------- ------- ------- ------- ------- ------- ------- ------- + ------- ------- ------- ------- ------- ------- ------- ------- ------- + ------- ------- ------- ------- ------- ------- ------- ------- ------- +
+------
| Grants for root@localhost |
+------
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+------
2 rows in set (0.00 sec)
mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON test.* TO 'test_user' @'localhost' IDENTIFIED BY 'fame_and_ fortUne12# 3';
Query OK, 0 rows affected, 1 warning (0.00 sec)
So, you can see no issues when assigning DROP privilege to test_user when Drop_priv is true for root user.
2nd test:
mysql> SELECT CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| root@localhost |
+----------------+
mysql> UPDATE mysql.user set Drop_priv = 'N' where user = 'root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW GRANTS FOR 'root'@'localhost'; ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- -+ ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ...
+------
| Grants for root@localhost |
+------