It is said that the best password is one that you don’t need to remember. In the case of MySQL, this is possible thanks to the plugin and its version for MariaDB — .
Both of these plugins are not new at all; they have been discussed extensively in this very blog, for instance in the article on . However, while examining what’s new in MariaDB 10.4, I found that unix_socket is now set by default and is one of the authentication methods ("one of the", because in MariaDB 10.4, more than one plugin is available for user authentication, which is explained in the document ).
As I mentioned, 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 socket authentication. 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></a>>With the Debian packages for MySQL, the root user is authenticated 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 true for 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 user authentication for root privileges under auth-socket for Percona Server. Let’s provide an example with 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 is the magic? The plugin checks that the Linux user matches the MySQL user by using the socket option SO_PEERCRED — to gather information about the user running the client program. Thus, the plugin can only be used on systems that support the SO_PEERCRED option, such as Linux. The socket option SO_PEERCRED allows for determining the uid associated with the socket process. After that, it retrieves the username associated with this uid.
Let's take an example with the user 'vagrant':
vagrant@mysql1:~$ whoami
vagrant
vagrant@mysql1:~$ mysql
ERROR 1698 (28000): Access denied for user 'vagrant'@'localhost'Since there is no 'vagrant' user in MySQL, access is denied. Let's create this 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)Done!
But 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 loadedOops. What was missing? The plugin didn't load:
mysql> pager grep socket
PAGER set to 'grep socket'
mysql> show plugins;
47 rows in set (0.00 sec)Let's add the 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 again:
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)Now you can log in with the username 'percona'.
[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: Can you log in with the same username percona but from another 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, you cannot.
Output
MySQL is quite flexible in several aspects, one of which is the authentication method. As shown in this post, access can be obtained without passwords, based on OS users. This can be useful in certain scenarios, one of which is when migrating from RDS/Aurora to standard MySQL, using , to still gain access but without passwords.
Source: habr.com
