CODEWITHSUNDEEP
mysql
Dacobi
Dacobi

Reputation: 437

Access denied for user 'root'@'localhost' to database 'drupal'

I'm working on a MySQL server I haven't configured my self and I'm a bit of a noob when it comes to SQL.

I've created a database like this:

root@localhost:~# mysqladmin -u root -p create drupal

And then tried to grant privileges like this:

root@localhost:~# mysql -u root -p

MariaDB [(none)]> GRANT ALL PRIVILEGES ON drupal.* TO root IDENTIFIED BY ...;

But get this error:

ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'drupal'

[Update] I get this error with all forms of GRANT commands.

But according to SHOW GRANTS I should have privileges:

MariaDB [(none)]> SHOW GRANTS;
+--------------------------------------------------------------------------- 
-------------------------------------------+
| Grants for root@localhost                                                                                            
|
+--------------------------------------------------------------------------- 
------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD 
'*...' |
+--------------------------------------------------------------------------- 
-------------------------------------------+
1 row in set (0.00 sec)

MySQL version is: mysql Ver 15.1 Distrib 10.1.26-MariaDB On Debian 9.5

Upvotes: 1

Views: 3313

Answers (1)

Dacobi
Dacobi

Reputation: 437

The problem was that the root user didn't have Grant privileges.

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

I fixed it with this command:

UPDATE mysql.user SET Grant_priv='Y', Super_priv='Y' WHERE User='root';
FLUSH PRIVILEGES;
GRANT ALL ON *.* TO 'root'@'localhost';

You have to log out and in again for it to work.

Upvotes: 4

Related Questions