WILT – mysql, root and sudo

I came across an interesting behavior that helped me learn something about MySQL (MariaDB to be precise) authentication. In this WILT blog I will talk about the “unix socket” authentication plugin in MySQL/MariaDB. The steps shown in this blog have been tried on Ubuntu 16.04 and MariaDB Server 10.

MariaDB Install and root user

In some earlier versions of MySQL as well as MariaDB server, the installation process required you to enter password for root user. This is the root user for the database. But in Ubuntu 16.04 and MariaDB server 10, the installation does not require you enter a root password. Post install you can either use the mysqladmin command or the mysql_secure_installation command to set root password.

Setting password using mysql_secure_installation command

As a first step we will set a specific password for root user using mysql_secure_installation command. The screenshot below shows how this can be performed. Note: after setting password the command will ask few more questions whose answer are not directly relevant for this blog.

Logging into the mysql client

Now we will see what happens if I try to access the mysql client using the root DB user. As shown below, even though mysql client prompts for root password, the authentication actually fails.

Logging in using sudo

Next I will try to access the mysql client with sudo.

As shown above, now when you enter the correct password for root DB user, you will be able to access the database. And this is where things start to get interesting. Even if you enter a wrong password, you will notice that you can access the MySQL client as root user.

Next try accessing the MySQL without the -p option and without even the -u option, **but with sudo_._ **As shown below, in both cases you will be able to login into the MySQL client.

So it seems that with sudo mysql, we can get access to root DB user without any other authentication other than the one prompted by sudo.

What is going on?

To understand what is going on, let us look at the user table inside the mysql database. Once you have successfully logged into the MySQL client, you can execute the SQL query shown below.

The most important column here is the plugin column. For root DB user, we can see that the plugin is unix_socket. The plugin column indicates the pluggable authentication mechanism to authenticate the DB user. In case of root, the plugin of **unix_socket **means that MySQL will authenticate based on the ID of the user creating the client side socket for communicating with the MySQL server.

When you use sudo, the client program runs with **root **privilege. Therefore the client socket is created with **root **privilege as well. The MySQL authentication cross checks the user ID of the client socket against the same DB user ID and if the DB user is authenticated using unix_socket, it will allow connection to the MySQL client.

How is this useful?

This plugin mechanism is helpful when DB client programs are running on the same server as that of the DB server. If all users are allowed access using Unix sockets, only applications running locally can access the DB. This gives some basic protection against unauthorized remote access to the DB. Moreover local users can continue to use their Unix passwords instead of using yet another password to access their DB accounts.