How to grant root account remote access to MariaDB

Yes

Summary

This guide is a simple how-to on giving remote access to the database root user to a specific host or all hosts. The scope covered in this article is specifically the creation of the user-host record in the mysql.user table, which governs logins to a MySQL or MariaDB instance.

Synopsis

If you have decided to set up your Software Vulnerability Manager (SVM) On-Premises servers in dual-mode configuration with one server housing Apache, PHP and the SVM configuration, and the other server hosting the SVM database, then you have to assign your database user appropriate privileges to allow it remote access to the database from the SVM server. A common configuration makes use of the database root account. By default accounts on the database will not be enabled for remote login.

Procedure

Enter the MySQL database on the database server using the existing configured account (e.g. "root"):

mysql -u root -p

Once logged in, run this query to grant permissions to your user:

grant all privileges on *.* to 'root'@'remotehost' identified by password 'secrets' with grant option;

Replace remotehost with the hostname or IP address of the SVM application server.
Replace secrets with the password for your MariaDB root account.

When you specify the remote host as the application server like in this example, you may need to have a record of the fully qualified hostname as well as the unqualified (shortened) version.

It's also possible to set up this query to allow logins from all remote locations using the following database command example:

grant all privileges on *.* to 'root'@'%' identified by password 'secrets' with grant option;

The key difference here is that we've replaced the remote host value with a % character. The reason we do this is that the % character represents a wildcard character in MariaDB and MySQL which in this case is used as an expression to match all possible host names and IPs.

As per the mysql documentation (which in this case also applies to MariaDB) any time you apply a grant statement with the 'grant option' (among others) it should automatically reload the privileges table in memory and as a result, the permission you set in the previous step should take effect.

That being said, all things are imperfect, so if your access does not seem to be working it is wise to run the following statement as a first step before attempting to troubleshoot further.

flush privileges;

Example

Example hostname: csi7server.network.local

grant all privileges on *.* to 'csi'@'csi7server.network.local' identified by password 'Sekret1' with grant option;

Example IP address: 10.0.0.127

grant all privileges on *.* to 'csi'@'10.0.0.127' identified by password 'Sekret1' with grant option;

Executing the grant twice, once for host name, once for IP, will allow the application server to connect if it's being recognized by either host name or IP.

How to grant root account remote access to MariaDB