After creating a new user in MariaDB / MySQL, flush permissions and MYSQL_SECURE_INSTALLATION

After installing LAMP on new servers (similar to as described here for CentOS), I’ve recently had two errors after the creation of MySQL users.

These user accounts required additional steps after creation – flushing permissions, and re-running the MySQL secure installation.

Creating a new User and assigning permissions

The first user was created in shell, via the MySQL prompt and assigned access to a new database.

Login to MySQL via SSH

mysql -u root -p

Create the new user “newuser” on localhost. We could also use % (any host), or any other specific hosts defined in MySQL.

mysql> CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

And assign privileges to a database called “mydatabase” – the format is database.table, so for access to all use “*.*”.

mysql> GRANT ALL PRIVILEGES ON mydatabase . * TO 'newuser'@'localhost';

And now – crucially, flush permissions. Often it works just fine, but on some setups this is required.

mysql> FLUSH PRIVILEGES;

 

The anonymous user issue

The second issue I’ve had recently was with a new user being unable to use their account. After some trial and error it turned out that there was an issue with anonymous access, which was easily solved by re-running the MySQL install helper.

MYSQL_SECURE_INSTALLATION

Remove all anonymous access!

Leave a Reply

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