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.
This makes sense, as there are different start-up scripts that would not work without this passwordless authentication. 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. That does not work, even if you would give the root a password.

Not recommended, the removal of unix_socket

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, that is, that you can log in as a regular user as MariaDB root after entering a password, then you would have to deactivate the unix_socket plugin.

However, this is definitely not recommended, as otherwise many scripts of the start-ups would have to be adjusted. This may well pose a security risk again. So this solution is the worse.

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,

which is definitely not recommended.

Second Admin under MariaDB with administrative rights set

Instead of removing these amenities of the root under MariaDB, we better create another database administrator.

# mysql -u root
MariaDB [(none)]> CREATE USER 'adminsql'@'localhost' IDENTIFIED BY 'secrect';

For a remote connection - which is uncertain - this looks like this:

MariaDB [(none)]> CREATE USER 'adminsql'@'%' IDENTIFIED BY 'secret';
MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'adminsql'@'%' WITH GRANT OPTION;

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;