How to use MySQL without a password (and security risks)

They say that the best password is one that does not need to be remembered. In the case of MySQL, this is real thanks to the auth_socket plugin and its version for MariaDB - unix_socket .

Both of these plugins are not new at all, they were discussed a lot in the same blog, for example, in an article on how to change passwords in MySQL 5.7 using the auth_socket plugin . However, analyzing what's new in MariaDB 10.4, I found that unix_socket is now installed by default and is one of the authentication methods ("one of", because in MariaDB 10.4 more than one plugin is available for authentication for one user, which is explained in the document "Authentication" from MariaDB 10.04 ).

As I said, this is not news, and when you install MySQL using the .deb packages supported by the Debian team, a user with root privileges is created for authentication through the socket. This is true for both MySQL and MariaDB.

root@app:~# apt-cache show mysql-server-5.7 | grep -i maintainers Original-Maintainer: Debian MySQL Maintainers <pkg-mysql-maint@lists.alioth.debian.org> Original-Maintainer: Debian MySQL Maintainers <<a href="mailto:pkg-mysql-maint@lists.alioth.debian.org">pkg-mysql-maint@lists.alioth.debian.org</a>>
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

With Debian packages for MySQL, the root user authenticates as follows:

 root@app:~# whoami root= root@app:~# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.7.27-0ubuntu0.16.04.1 (Ubuntu) Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> select user, host, plugin, authentication_string from mysql.user where user = 'root'; +------+-----------+-------------+-----------------------+ | user | host   | plugin | authentication_string | +------+-----------+-------------+-----------------------+ | root | localhost | auth_socket |            | +------+-----------+-------------+-----------------------+ 1 row in set (0.01 sec)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

The same is the case with the .deb package for MariaDB:

 10.0.38-MariaDB-0ubuntu0.16.04.1 Ubuntu 16.04 MariaDB [(none)]> show grants; +------------------------------------------------------------------------------------------------+ | Grants for root@localhost                                   | +------------------------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED VIA unix_socket WITH GRANT OPTION | | GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION                 | +------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

The .deb packages from the official Percona repository also configure root authentication for auth-socket and for Percona Server. Here is an example with Percona Server for MySQL 8.0.16-7 and Ubuntu 16.04:

 root@app:~# whoami root root@app:~# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 8.0.16-7 Percona Server (GPL), Release '7', Revision '613e312' Copyright (c) 2009-2019 Percona LLC and/or its affiliates Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> select user, host, plugin, authentication_string from mysql.user where user ='root'; +------+-----------+-------------+-----------------------+ | user | host   | plugin | authentication_string | +------+-----------+-------------+-----------------------+ | root | localhost | auth_socket |            | +------+-----------+-------------+-----------------------+ 1 row in set (0.00 sec)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

So what's the magic? The plugin verifies that the Linux user matches the MySQL user using the SO_PEERCRED socket option — to collect information about the user running the client program. Thus, the plugin can only be used on systems that support the SO_PEERCRED option, like Linux itself. The SO_PEERCRED socket option allows you to recognize the uid of the socket-associated process. And after that he already gets the username associated with this uid.

Here is an example with user "vagrant":

 vagrant@mysql1:~$ whoami vagrant vagrant@mysql1:~$ mysql ERROR 1698 (28000): Access denied for user 'vagrant'@'localhost'
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

Since MySQL does not have the user "vagrant", we are denied access. Create such a user and try again:

 MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'vagrant'@'localhost' IDENTIFIED VIA unix_socket; Query OK, 0 rows affected (0.00 sec) vagrant@mysql1:~$ mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 45 Server version: 10.0.38-MariaDB-0ubuntu0.16.04.1 Ubuntu 16.04 Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> show grants; +---------------------------------------------------------------------------------+ | Grants for vagrant@localhost                          | +---------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'vagrant'@'localhost' IDENTIFIED VIA unix_socket | +---------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

Happened!

Well, what about a non-Debian distribution where this is not provided by default? Let's try Percona Server for MySQL 8 installed on CentOS 7:

 mysql> show variables like '%version%comment'; +-----------------+---------------------------------------------------+ | Variable_name  | Value                  | +-----------------+---------------------------------------------------+ | version_comment | Percona Server (GPL), Release 7, Revision 613e312 | +-----------------+---------------------------------------------------+ 1 row in set (0.01 sec) mysql> CREATE USER 'percona'@'localhost' IDENTIFIED WITH auth_socket; ERROR 1524 (HY000): Plugin 'auth_socket' is not loaded
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

Bummer. What was missing? Plugin not loaded:

 mysql> pager grep socket PAGER set to 'grep socket' mysql> show plugins; 47 rows in set (0.00 sec)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

Add a plugin to the process:

 mysql> nopager PAGER set to stdout mysql> INSTALL PLUGIN auth_socket SONAME 'auth_socket.so'; Query OK, 0 rows affected (0.00 sec) mysql> pager grep socket; show plugins; PAGER set to 'grep socket' | auth_socket           | ACTIVE | AUTHENTICATION | auth_socket.so | GPL   | 48 rows in set (0.00 sec)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

Now we have everything we need. Let's try one more time:

 mysql> CREATE USER 'percona'@'localhost' IDENTIFIED WITH auth_socket; Query OK, 0 rows affected (0.01 sec) mysql> GRANT ALL PRIVILEGES ON *.* TO 'percona'@'localhost'; Query OK, 0 rows affected (0.01 sec)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

You can now log in using the "percona" login.

 [percona@ip-192-168-1-111 ~]$ whoami percona [percona@ip-192-168-1-111 ~]$ mysql -upercona Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 19 Server version: 8.0.16-7 Percona Server (GPL), Release 7, Revision 613e312 Copyright (c) 2009-2019 Percona LLC and/or its affiliates Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> select user, host, plugin, authentication_string from mysql.user where user ='percona'; +---------+-----------+-------------+-----------------------+ | user  | host  | plugin  | authentication_string | +---------+-----------+-------------+-----------------------+ | percona | localhost | auth_socket |            | +---------+-----------+-------------+-----------------------+ 1 row in set (0.00 sec)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

And it worked again!

Question: will it be possible to log in using the same percona login, but from a different user?

 [percona@ip-192-168-1-111 ~]$ logout [root@ip-192-168-1-111 ~]# mysql -upercona ERROR 1698 (28000): Access denied for user 'percona'@'localhost'
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

No, it won’t work out.

Output

MySQL is quite flexible in several aspects, one of which is the authentication method. As can be seen from this post, access can be obtained without passwords, based on users of the OS. This can be useful in certain scenarios, and one of them is when you migrate from RDS / Aurora to regular MySQL, using IAM database authentication to still access, but without passwords.