Cannot grant if Drop_priv removed

[Sean Kelly]

I noticed I was unable to grant global root perms when Drop_priv = 'N'.

Version: 5.6.28-76.1-log
OS: root@sql03:/var/log# cat /etc/issue Ubuntu 14.04.3 LTS \n \l
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'@'laffytaffy' identified by 'fame_and_fortune';
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'@'laffytaffy' identified by 'fame_and_fortune';
Query OK, 0 rows affected, 1 warning (0.00 sec)

[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 |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------...

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-3377