Use MySQL Without a Password (and Still be Secure)

Use MySQL Without a PasswordSome say that the best password is the one you don’t have to remember. That’s possible with MySQL, thanks to the auth_socket plugin and its MariaDB version unix_socket.

Neither of these plugins is new, and some words have been written about the auth_socket on this blog before, for example: how to change passwords in MySQL 5.7 when using plugin: auth_socket. But while reviewing what’s new with MariaDB 10.4, I saw that the unix_socket now comes installed by default and is one of the authentication methods (one of them because in MariaDB 10.4 a single user can have more than one authentication plugin, as explained in the Authentication from MariaDB 10.4 document).

As already mentioned this is not news, and even when one installs MySQL using the .deb packages maintained by the Debian team, the root user is created so it uses the socket authentication. This is true for both MySQL and MariaDB:

Using the Debian packages of MySQL, the root is authenticated as follows:

Same for the MariaDB .deb package:

For Percona Server, the .deb packages from the official Percona Repo are also setting the root user authentication to auth_socket. Here is an example of Percona Server for MySQL 8.0.16-7 and Ubuntu 16.04:

So, what’s the magic? The plugin checks that the Linux user matches the MySQL user using the SO_PEERCRED socket option to obtain information about the user running the client program. Thus, the plugin can be used only on systems that support the SO_PEERCRED option, such as Linux. The SO_PEERCRED socket option allows retrieving the uid of the process that is connected to the socket. It is then able to get the user name associated with that uid.

Here’s an example with the user “vagrant”:

Since no user “vagrant” exists in MySQL, the access is denied. Let’s create the user and try again:

Success!

Now, what about on a non-debian distro, where this is not the default? Let’s try it on Percona Server for MySQL 8 installed on a CentOS 7:

Failed. What is missing? The plugin is not loaded:

Let’s add the plugin in runtime:

We got all we need now. Let’s try again:

And now we can log in as the OS user “percona”.

Success again!

Question: Can I try to log as the user percona from another user?

No, you can’t.

Conclusion

MySQL is flexible enough in several aspects, one being the authentication methods. As we see in this post, one can achieve access without passwords by relying on OS users. This is helpful in several scenarios, but just to mention one: when migrating from RDS/Aurora to regular MySQL and using IAM Database Authentication to keep getting access without using passwords.

Share this post

Comments (3)

  • Jan Steinman Reply

    Cool!

    But I’m not sure about the concept of UNIX users as SQL users.

    For the most part, I think of UNIX users as real human users, whereas I think of MySQL users more as roles. I typically set up MySQL users as (for example) browsers (SELECT only) editors (SELECT, INSERT, DELETE, UPDATE), and admins (do anything).

    It seems to me that user authentication happens at the application level, and role authentication takes place between the application and the database.

    Or perhaps I completely miss what you were demonstrating.

    November 1, 2019 at 2:43 pm
    • Art van Scheppingen Reply

      There isn’t that much changing compared to what you are describing: in your application you would still be required to use normal authentication. What Daniel is showing here is that in addition to the old scheme you can also use the Unix socket authentication. This means you will have just another way of authenticating of your Unix users than to put the password in some .my.cnf or mylogin.cnf. It’s basically saying you trust your Unix logins well enough to provide them passwordless access to MySQL, so your analogy of seeing Unix users as real human users is quite spot on.

      Back when MariaDB announced to enable this by default for the root user in 10.4 I wrote a lengthy reply. I have no problem with people enabling this in a safe and confined environment, however I’m not convinced that enabling this _by default_ is a good idea as the majority installations will not be performed by sysadmins, security experts or DBAs. But I think this is a bit too off topic here.

      November 4, 2019 at 5:51 am
  • Jan Steinman Reply

    I have no problem with people enabling this in a safe and confined environment

    My database server is on a non-routable network (10.*.*.*). It is accessed by my web server via the same network, which also has a state IP facing the Internet.

    I can access my LAN and MySQL via VPN, but otherwise, would you think this is a “safe and confined environment?”

    November 4, 2019 at 11:37 am

Leave a Reply