MySQL/MariaDB can not grant privileges root@localhost

Problem

You can not grant MySQL table access to any users, getting the error: "access denied grant *.* root localhost".
The suggested fixes should fix any of those problems

• mariadb can not grant access denied for user 'root'@'localhost'
• MySQL grant access dennied root%localhost
• create remote mysql users, can not grant access
• grant identified via mysql_native_password

 

Solution

First, make sure the MySQL users table looks ok:

SELECT host,user,plugin,password,Grant_priv,Super_priv FROM mysql.user

Grant_priv - should be 'Y'

Super_priv - should be 'Y' 

plugin - should be 'mysql_native_password'

Fixing Grant_priv and Super_priv

UPDATE mysql.user SET Grant_priv='Y', Super_priv='Y' WHERE User='root';
FLUSH PRIVILEGES;
exit;
service mysql restart;

Update grants or reset root password

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY '<password>';
GRANT ALL ON *.* TO 'root'@'localhost';
FLUSH PRIVILEGES;
exit;
service mysql restart;

Fixing plugin:

UPDATE user SET plugin='mysql_native_password' WHERE User='root';