How to Grant Remote Login Privilege to a MariaDB User Account

Previous: How To Enable Remote Access To MariaDB Database Server On AlmaLinux 8

Suppose, we want to grant remote login privilege to the root user from any IP address or hostname. The root user’s password is asDFgh12345.

Granting remote login privilege to the root user is a bad practice.

a) Generating Hashed Password

At first, you have to generate the hashed password for the user. Excute the following SQL statement.
SELECT PASSWORD("asDFgh12345");
And copy the result which may be similar to the following string.

`*E459E5F12492FC860EDCF3739D82506B3BED1C79

b) Making Entry

Now, grant the privilege by executing the following SQL statement!
grant all privileges on *.* to 'root'@'%' identified by password '*E459E5F12492FC860EDCF3739D82506B3BED1C79' with grant option;
The wildcard % means all IP addresses including localhost.

c) Checking For Duplicate Entries

You must check for duplicate entries.
SELECT user, authentication_string, plugin, host FROM mysql.user;
The mysql.user table should contain only one entry for one user. If you find another entry, you must delete that entry.

d) Reloading Grant Tables

Reload the grant tables by executing the following SQL statement.
FLUSH PRIVILEGES;

Documentation

• https://mariadb.com/kb/en/configuring-mariadb-for-remote-client-access/