Welcome, my name is Ian Gilfillan from the MariaDB Foundation, and this video looks at
Authentication from MariaDB 10.4.
So, what's changed with how you connect to MariaDB since the older releases.
Note that the unix_socket authentication plugin discussed here is not available on Windows,
so this is mostly applicable to Unix-like systems.
Now, older versions of MariaDB, by default were using the mysql_native_password authentication
plugin.
The big change in 10.4 is that it uses the unix_socket authentication plugin by default
when you begin.
This has been around a while.
It was pioneered by Otto Kekäläinen on Debian systems, since MariaDB 10.0, but from 10.4
it's now in all other distros as well.
unix_socket_authentication allows in this case the root@localhost user to login without
a password via the local Unix socket file, which is defined by the socket system variable,
as long as the login is attempted from a process owned by that operating system root user account.
So let's see how this works.
Firstly, we start to connect.
Now, if the usage you see here of mariadb client is unfamiliar to you, another change
from 10.4 is that there are now symlinks, so all of the old mysql binaries have a mariadb
symlink.
In fact from 10.5, the reverse applies, where the symlink is now the mysql version, and
the mariadb name is the actual name of the file.
All of the old clients will still work for the forseeable future, but its a good habit
to get into to start using the mariadb names.
So, we'll start to try and connect - this is a brand new 10.4 installation.
And we start to try and connect.
We do something like mariadb -uroot -p
and then we realise, oh, hang on, I haven't set a password yet.
Maybe I forgot it, or maybe the system didn't prompt me and you start to get cross with
MariaDB.
Actually, you weren't prompted for a password.
You no longer need to be bugged to set a password upon installation because you don't need one.
Now, if you just try to connect like this, you get Access Denied, and that is because
I'm not the correct user.
I'm logging in here as ian.
If I was logging from the root user, the Unix root user, I don't even need to specify the
user because I am root.
I can login fine without a password.
Now, when people first come across this concept, sometimes they worry about security, they
think surely logging in with no password is insecure...
No, as we saw here, that doesn't mean just anybody can login, you have to log in from
the correct process.
And if someone is trying to login as root and has root access, you have a lot more to
worry about than just your MariaDB database.
So, now I'm in.
This also means of course that there is no need for any config files lying around with
plain text passwords, which is another security improvement.
Now, 10.4 allows users to have multiple authentication plugins
So, by default, we use the unix_socket authentication plugin here, but let me just show you effectively
what's created upon a new installation.
There's a root user and there's a mysql user.
Identified via unix_socket, so that's what we just saw.
Or, the alternative and still active is the mysql_native_authentication plugin, the old
way of doing things.
Except that here, the password that's been specified is "invalid" which is, unsurprisingly,
not a valid password hash, so you can't log in with a password by default, but the plugin
is set and you're able to set your own password if you do want to do that.
So, let's try and create a new user now, that can login and connect with both these methods.
So, we're creating a new user "ian".
ian@localhost identified by unix_socket or mysql_native_password using the password "pwd".
Now, we can log in using unix_socket.
No password needed.
No username of course because I am ian in this particular instance.
No problem there.
If I try and log in from any other account, from the root account, my access is denied.
But I can login with a password from any other account.
And there I am.
I'm able to access MariaDB through both authentication plugins.
A possible use-case for using more than one authentication plugin is; perhaps you want
to migrate users to the more secure ed25519 authentication plugin, and at the same time
still allow users to access MariaDB using the old mysql_native_password plugin for a
transitional period.
This is now possible from MariaDB 10.4.
So, what if you want to go back to the old ways?
All of this new stuff is too much for you.
You just want to go back to how it was, password login only.
You can of course use an ALTER statement and change root@localhost identified via mysql_native_password
using password 'pwd'.
You can see that there was no mention of unix_socket, so that's been completely removed now, and
you're only able to login in the old way using a password.
We can test this of course.
We go back and try login without a password.
No go.
We can login from any unix account, it doesn't matter, with our password, and we're in.
No more unix_socket authentication.
Now of course, here you're at risk of forgetting your password again, so there are countless
tutorials on the internet about how to restore access once you've forgotten your root password,
so we'll tack one on here just for good measure, because there's a small change in the process
as well.
So let's see how we would get in.
We've now lost our password, we can't get in to MariaDB.
What can we do?
So, the first step is to restart MariaDB with the --skip-grant-tables option.
I've already set that up.
We've restarted MariaDB.
Now we can login without a password.
And we're in.
We run FLUSH PRIVILEGES.
And now, the final step, which wasn't necessary beforehand, but is necessary now from 10.4,
is that you need to set a password at this point in time for your root user.
So, SET PASSWORD FOR root@localhost = PASSWORD('new');
Now we have our password, remembered again.
And we can log in with the password - new - and we're in.
We've restored access.
So, there're a number of other changes from 10.4 that are not being covered in this video.
For example, mysql.user is no longer a table, it's actually a view.
And it's created from the mysql.global_priv table.
No need to worry, because all of your old tools that still access or try and read mysql.user
should continue to work as before.
The main reason for doing it is that mysql.user couldn't show alternative authentication plugins,
it couldn't handle complex authentication rules, hence the need to switch to mysql.global_priv.
Any questions, you're welcome to ask in the YouTube comments, and there will be links
below to relevant pages on the KB.
Thanks for watching.
Till next time.
