MariaDB Server 10.4 came with a whole lot ofSecurity related changes. Some of them are merely optimizations (likeMDEV-15649), some improve existing features to be more robust (MDEV-15473,MDEV-7598) or convenient (MDEV-12835,MDEV-16266). Some are MySQL compatibility features, requested by our users (MDEV-7597,MDEV-13095).

But the first thing any MariaDB Server user, whether an experienced veteran or a newbie, does — before even issuing the first SQL statement — is logging in. Authenticating to the database server. And 10.4 brings changes to this process too. If you are a new user, you will hopefully find MariaDB Servereasier and more intuitive to use with less struggling over passwords. But if you have used MariaDB Server for a while, the new behavior might feel at timesbaffling. This post explains what has changed and what MariaDB Server is doing now.

In a nutshell

The password storage has changed. All user accounts, passwords, and global privileges are now stored in amysql.global_priv table. What happened to themysql.user table? It still exists and has exactly the same set of columns as before, but it’s now aview overmysql.global_priv. If you happen to have tools that analyzemysql.user table — they should continue working as before.

One can specify more than one authentication method per account. They all will work as alternatives. For example, a DBA might start migrating users to the more secure ed25519 password plugin, but keep the old SHA1 one as an alternative for the transitional period.

The default authentication for new installations is now more secure. The open-for-everyone all-powerful root account is gone, at last. And installation scripts will no longer shout at you “PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER !”, because the root account is created secure automatically.

Details

Technically, a new MariaDB installation will have two all-powerful accounts —root and the OS user that owns the data directory, typicallymysql. They are created as

CREATE USER root@localhost IDENTIFIED VIA unix_socket OR mysql_native_password USING 'invalid'CREATE USER mysql@localhost IDENTIFIED VIA unix_socket OR mysql_native_password USING 'invalid'

Usingunix_socket means that if you are the system root user, you can login asroot@locahost without a password. This technique was pioneered by Otto Kekäläinen in MariaDB packages in Debian as early as MariaDB 10.0. It is based on a simple fact, that asking the system root for a password adds no extra security — root has full access to all the data files and all process memory anyway. Butnot asking for a password means, there is no root password to forget (bye-bye numerous tutorials “how to reset MariaDB root password”). And if you want to script some tedious database work, there is no need to store the root password in plain text for the scipt to use (bye-byedebian-sys-maint user).

Still, some users complained that they want to log in as MariaDB root without usingsudo. This is why in 10.4 the root user has a second authentication method — conventional MariaDB password. By default it is disabled (“invalid” is not a valid password hash), but one can set the password with a usualSET PASSWORD statement. And still retain the password-less access viasudo!

Now, what happens, if you install MariaDB locally (for example, from a tarball)? You definitely would not want to usesudo to be able to login. This is why MariaDB creates a second all-powerful user with the same name as a system user that owns the data directory. In local (not system-wide) installations, this will be the user, who installed MariaDB — she automatically gets convenient password-less root-like access, because, frankly, she can access all the data files anyway.

And even if MariaDB is installed system-wide, you may not want to run your database maintenance scripts as system root — now you can run them as systemmysql user. And you will know, that they will never destroy your entire system, even if youmake a typo in a shell script.

Cookbook

“This is all great”, you may be thinking, “but I’m a seasoned MariaDB DBA, I can write SQL in my sleep, do I need to do something different from now on”? Unfortunately, yes.

After installing MariaDB system-wide the first thing you’ve got used to doing is logging in into the unprotected root account and protecting it, that is, setting the root password:

$ sudo dnf install MariaDB-server$ mysql -uroot...MariaDB> set password = password("XH4VmT3_jt");

This is not only unnecessary now, it will simply not work — there is no unprotected root account. To login as root use

$ sudo dnf install MariaDB-server$ sudo mysql

Note that it implies you are connecting via the unix socket, not tcp. If you happen to haveprotocol=tcp in a system-wide/etc/my.cnf file, usesudo mysql --protocol=socket.

After installing MariaDB locally you’ve also used to connect to the unprotected root account usingmysql -uroot. It will not work either, use simplymysql without specifying a username.

You want passwords back, no unix_socket authentication anymore? Run

ALTER USER root@localhost IDENTIFIED VIA mysql_native_password USING PASSWORD("verysecret")

Forgot your root password? No problem — you can still connect usingsudo and change the password. Oh, you havealso removed unix_socket authentication? In that case, do as follows:

1. restart MariaDB with--skip-grant-tables
2. login into the unprotected server
3. runFLUSH PRIVILEGES (note, before 10.4 it would’ve been the last step, not anymore)
4. runSET PASSWORD FOR root@localhost to change the root password

You want to peek inside privilege tables? Oldmysql.user table still exists, you can select from it as before, although you cannot update it anymore. It doesn’t show alternative authentication plugins? Yes, this was one of the reasons for switching tomysql.global_priv table — complex authentication rules did not fit into rigid structure of a relational table. But you can, of course, select from the new table too. For example, with

select concat(user, '@', host, ' => ', json_detailed(priv)) from mysql.global_priv;

This is it. Remember, the best way to keep your password safe is not to have one. And have fun!

9 replies on “Authentication in MariaDB 10.4 — Understanding the Changes”