Unable to Grant Privileges on MariaDB

WordPress requires an SQL like database. On RPI there is no MySQL database, but there is an equivalent fully compatible DB called MariaDB. After a fresh install there’s no password set up initially for the user root, so is very important to fix that. You should use the secure installation script that come along.

sudo mysql_secure_installation

However, I wanted to fix that manually, so i typed:

DROP USER 'root'@'localhost';
CREATE USER 'root'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost';

Where basically I dropped the local user “root”, and created a new user root with a password. I also made sure that i granted all the privilege to it. You can Even add a “flush privilege” as ending command.

So after securing the root password in the DB, is a good practice to create a new user for each of your WordPress installation (Especially if you are hosting multiple site). The reason is easy. If one of the website gets compromised and you use the same db account for all the websites, then the attacked also compromised all the other Databases! However if each website has its own associated user with permission only on the specific database, then an attacker controlling that user cannot “see” or modify other tables in other databases.

Anyway, after login with the new root account, I created a new user and then a new table for the website and then I tried to give privilege on the new DB to the new account!:

CREATE USER 'wordpress'@'localhost' IDENTIFIED BY 'password';
GRANT ALL ON wordpress.* TO 'wordpress'@'localhost';

Anyway the last command was failing saying that i did not had permission to WordPress database! This was very strange because I had just created it and i could list table create new entries in it and so on.. So after some investigation i found out that the problem was that when i granted *.* privileges to user root, the “GRANT OPTION” was not set. This was a problem because I had already dropped the other user “root”. So in order to fix that I need to manually edit the mysql.user table in the DB. My user could not issue grants, but it still had full power on all the Databases. So firstly i made sure that the grant privilege flag was not set with:

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

and indeed was showing “N” to the grant_priv column. That had to be changed to “Y”.. for doing that i just typed:

UPDATE mysql.user SET Grant_priv='Y', Super_priv='Y' WHERE User='root';

With after fixing that and logging off and on again, i was finally able to grant all the permission to the new created user for the WordPress database!

GRANT ALL ON wordpress.* TO 'wordpress'@'localhost';

I hope that this small guide will help you to fix this annoying issue. Also I hope makes you understand why using the user root for your WordPress installation is a really bad idea. They can access MySQL.user table and basically control all the users privileges, passwords etc. They can create new root users, revoke permission, granting them-self more permissions and so on.. So just create a new user for each installation and give permission just to the specific DB.

Click to rate this post!
[Total: 2 Average: 5]

Leave a Reply

Your email address will not be published. Required fields are marked *