Authentication in MariaDB 10.4 — Understanding the Changes
MariaDB Server 10.4 came with a whole lot of Security related changes. Some of them are merely optimizations (like MDEV-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 Server easier 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 times baffling. 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 a mysql.global_priv table. What happened to the mysql.user table? It still exists and has exactly the same set of columns as before, but it’s now a view over mysql.global_priv. If you happen to have tools that analyze mysql.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, typically mysql. 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'
Using unix_socket means that if you are the system root user, you can login as root@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. But not 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-bye debian-sys-maint user).
Still, some users complained that they want to log in as MariaDB root without using sudo. 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 usual SET PASSWORD statement. And still retain the password-less access via sudo!
Now, what happens, if you install MariaDB locally (for example, from a tarball)? You definitely would not want to use sudo 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 system mysql user. And you will know, that they will never destroy your entire system, even if you make 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 have protocol=tcp in a system-wide /etc/my.cnf file, use sudo mysql --protocol=socket.
After installing MariaDB locally you’ve also used to connect to the unprotected root account using mysql -uroot. It will not work either, use simply mysql 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 using sudo and change the password. Oh, you have also removed unix_socket authentication? In that case, do as follows:
- restart MariaDB with
--skip-grant-tables - login into the unprotected server
- run
FLUSH PRIVILEGES(note, before 10.4 it would’ve been the last step, not anymore) - run
SET PASSWORD FOR root@localhostto change the root password
You want to peek inside privilege tables? Old mysql.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 to mysql.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!
> CREATE USER root@localhost IDENTIFIED VIA unix_socket OR mysql_native_password USING ‘invalid’
Why isn’t this enough?
CREATE USER root@localhost IDENTIFIED VIA unix_socket
This is what Debian used since 10.0. It worked fine for the majority of the users. With that approach anybody who logs in as root@localhost must have sudo rights. But we have got complains that some users wanted their old setup to work and login as root@localhost without sudo’ing to system root.
With the 10.4 approach one can use sudo, as a conveinent password-less option. But one can also do
sudo mysql -e ‘SET PASSWORD=PASSWORD(“foobar”)’
and let someone use “mysql –user=root –password=foobar” without sudo.
That is, SET PASSWORD will succeed, and after that both unix_socket and password=foobar will work.
Does `set password` fail if `OR mysql_native_password USING ‘invalid’` was missing when a user was created?
If so, that’s not nice.
If not, what’s the point of `OR mysql_native_password USING ‘invalid’` ?
I agree multiple auth methods for a single user is great, I’ve wanted that ever since socket based auth was introduced.
Yes. If you have just `CREATE USER root@localhost IDENTIFIED VIA unix_socket` and you do `SET PASSWORD` it will fail, because it won’t be able to set the password for the unix_socket plugin. You need to have at least one plugin that supports the concept of a “password” for `SET PASSWORD` to succeed.
At first I was very happy to read all these changes, however I’m a bit worried about this statement regarding the unix_sockets authentication:
“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.”
I only agree with this partly: yes as a root user you would be able to access the files, but you wouldn’t be able to extract data from files directly. Allowing the root user to login passwordless via the unix socket would allow anyone to extract data more easily. Maybe the attacker isn’t interested in the full data set, but rather learn more about the setup of an application or just a portion of sensitive data. Now an attacker is able to get that specific data without having to copy the entire dataset first by simply issuing a query.
Of course an attacker could reset the root password, but this would probably get noticed due to the restarts of the daemon. It’s also an extra step required before an attacker could extract/export data. And more importantly: some users who have the ability to become root (sysadmins) may not have a reason to be able to log into the database, so why grant them this privilege?
To make an analogy with some real world example: leaving the front door of your garage open wouldn’t add any additional security as a burglar could dress up as you and you can walk freely over the premises of your house anyway. This is only partly true, as the burglar wouldn’t be able to enter your garage directly and browse around. Instead the burglar has to steal the entire garage or brute force it if he/she is only interested in that precious Ferrari that is parked inside. Of course, I trust my next door neighbor when he/she enters my premises but I still rather not have them snoop around in my garage.
I personally think having a unix_socket login is a good improvement as an authentication methos, but I don’t think it’s a good idea to enable this by default. As we all have learned from the past: an empty (root) password was a bad idea and most users simply don’t change it out of laziness. I think it’s will be welcomed by many who run their own (private) server or run docker, but not by those who run it in large(r) organisations. But that’s just my 2 cents.
Yes, that’s precisely the point. That you cannot really *protect* against a root user, you can only make it more annoying and less convenient to extract the data. A root user can always copy all data files and start a separate mysqld process on them. Or a root can edit mysqld image in memory and change the root password only there, without a restart. Or — a much easier approach — a root can attach gdb to the mysqld process and make the authentication pass for any password.
Note, that I certainly agree there might be cases when a DBA does not want root to have a password-less access for some reasons. But that’s why it can be easily disabled, it’s not an inherent hard-coded pass-through backdoor.
I believe that for most installations not having root password increases the ease of use and actually improves the security. And when it does not — a DBA can disable it with one command.
Let me defend for once MariaDB here. 🙂
unix_socket was one of the best inventions for the problem it wanted to solve- Art, you are not required to use it (I can see other people not wanting it) but it is not less secure. Passwords in general are a bad way to authenticate, and socket based authentication solves use cases like the ugly debian-sys-maint user- it won’t solve others use cases, for which there are other solutions (encryption, TLS, other authentication methods). I wouldn’t use it for regular application authentication, but it is great to avoid maintaining compromisable passwords for admin-type accounts.
I am not so sure about the new authentication tables, will have to wait to see how it works on practice, too early to tell.
HI
I have a fresh installation. And Everything seems not working. How can I login to mariadb 10.04. Its making like hell.
Thanks
If you have problem installing or working with MariaDB, please, don’t hesitate to seek for help on Zulip or mailing lists, as listed at https://mariadb.org/get-involved/