LINUXMAKER, OpenSource, Tutorials

Problem with root login together with MariaDB

In MariaDB there is the authentication plugin unix_socket (see also documentation). Under an Ubuntu and Debian installation, this plugin is enabled by default for a MariaDB installation.
In principle, this is a pleasant thing. As long as you work as root or with sudo, you can log in to the MySQL server without a password:

# mysql -u root
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 48
Server version: 10.1.29-MariaDB-6 Debian buildd-unstable
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> status;
Current user:           root@localhost

However, once you are not logged in as root, the login will fail.

~$ mysql -u root -p
Enter password:
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

Thus, MariaDB is now generally secured without a password. It only becomes problematic if you want to administer MariaDB, if you should not be root or sudo.

Set root password under MariaDB

Experienced users under MariaDB will know how to define the password for the root in the mysql.user database.

# mysql -u root
MariaDB [(none)]> update mysql.user set password=password('secret') where user='root';
MariaDB [(none)]> flush privileges;

From now on, you must authenticate yourself with a password as was customary in the past.

# sudo mysql -u root -p
Enter password:

The MySQL login as an ordinary user

If you are not logged in as root on Linux or do not use sudo, the login will still not work.

MariaDB now runs two authentication methods in parallel: The DB server checks whether you have root privileges as a Linux user AND whether you specify the correct password. The password alone is no longer enough.

If MariaDB should behave as it used to, ie that you can log in as a regular user as a MariaDB root after entering a password, then you must deactivate the unix_socket plugin. Interesting is first a look in the table mysql.user be ...

MariaDB [(none)]> select user,host,password,plugin from mysql.user;
| user | host      | password                                  | plugin      |
| root | localhost | secrethashcode                            | unix_socket |
1 row in set (0.00 sec)

... and then set the plugin column to '':

update mysql.user set plugin='' where user='root';
flush privileges;

Now the login works again as in the past.

Particularities (Update 7.3.2016)

MariaDB is started under Debian Jessie by the Init-V script /etc/init.d/mysql and quit again. To exit, this script uses mysqladmin, relying on mysqladmin to work with root privileges without a password. This is not the case after the above changes.

In order for the init script to work again, you must enter your MySQL root password twice in plain text in /etc/mysql/debian.cnf (always in the already provided line password = ...). Of course, this is not desirable in terms of safety.

In that sense, it is probably better to leave the default configuration for the MySQL root user as it is after a MariaDB reinstallation. In order to be able to perform MySQL administration work without root login, set up the MySQL user root2 with full rights:

sudo -s
  grant all on *.* to root2@localhost
  identified by 'secret' with grant option;