Mysql – DROP command denied to user ‘root’@’localhost’

MySQLpermissions

Okay. Apparently someone removed the DROP privilege from root (they have every other privilege granted), presumably to prevent accidental deletion of tables. Problem is now no users have the privilege. Upon trying to reassign the privilege, mysql responds with

mysql> GRANT ALL ON *.* TO 'root'@'localhost';
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

I tried creating a new user with the same privilege; same error.

Error creating account root@[hostname]: Access denied for user 'root'@localhost (using password: YES)

I double checked my who I'm authenticated as: root@localhost.

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql>  select current_user();
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

MYSQL Grants

SELECT COUNT(1) MySQLGrantsCount,VERSION() MySQLVersion
FROM information_schema.columns
WHERE table_schema='mysql' AND table_name='user';

returns

+------------------+--------------+
| MySQLGrantsCount | MySQLVersion |
+------------------+--------------+
|               43 | 5.6.34       |
+------------------+--------------+
1 row in set (0.00 sec)

How then do I grant the privilege?

Best Answer

OK, then the problem is simpler than I expected.

It's time to hack the mysql.user table.

CREATE TABLE mysql.user_new LIKE mysql.user;
INSERT INTO mysql.user_new SELECT * FROM mysql.user;
UPDATE mysql.user_new SET drop_priv='Y',grant_priv='Y'
WHERE user='root' AND host='localhost';
UPDATE mysql.user A INNER JOIN mysql.user_new B USING (user,host)
SET A.drop_priv = B.drop_priv;
FLUSH PRIVILEGES;

Then, run this to verify:

SHOW GRANTS FOR 'root'@'localhost';

The reason you could not just run the GRANT command to fix this is the fact that you cannot grant a privilege that you currently do not own. Similarly, you can't use RENAME TABLE mysql.user TO mysql.user_old,mysql.user_new TO mysql.user; because RENAME requires the DROP privilege.

Related Question