Friday, October 22, 2021

MySQL/MariaDB can not grant privileges [email protected]

Leave a Comment

Problem

You can not grant MySQL table access to any users, getting the error: "access denied grant *.* root localhost". You might have also searched for:

  • 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 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';

0 comentarii:

Post a Comment