Grant Remote Access to Root in MariaDB [On-Prem]
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.
If you have decided to set up your SVM On-Prem 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 with the appropriate remote privileges to allow it remote access to the database from the SVM server. A common configuration makes use of the database root account, but by default accounts on the database will not be enabled for remote login.
1. 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 the remote host value with the hostname or IP address of the SVM application server.
- Replace the secrets value with the password for your 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 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.
To lock down the host so that your user will be allowed to connect only from the SVM Application server, you can create the grants for the host name and IP instead of a wild-character (%):
Example hostname: csi7server.network.local
ON *.* TO 'csi'@'csi7server.network.local' IDENTIFIED BY 'Sekret1' WITH GRANT OPTION;
Example IP address: 10.0.0.127
ON *.* TO 'csi'@'10.0.0.127' IDENTIFIED BY 'Sekret1' WITH GRANT OPTION;
Executing the grant twice, once for host name, once for IP, will allow the App server to connect if it?s being recognized by either host name or IP.
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.