How to login in MariaDB with OS user without password

What is the solution if I don’t want to give password in command line (i.e mysql -uroot -p ) OR don’t want to store password in files(in .my.cnf) and still can login into MySQL/MariaDB without password ? I was also bit curious to know but finally I found very easy solution called “unix_socket plugin” provided by MariaDB.

This plugin allows to use operating system user credentials while connecting to MariaDB via Unix socket. When we try to connect with OS user, it will retrieve uid of the process which has connected to the socket and allow it to connect to MariaDB with the same user.

You can simply install that plugin with command,

MariaDB [(none)]> INSTALL PLUGIN unix_socket SONAME 'auth_socket';
Query OK, 0 rows affected (0.02 sec)

After, then you need to identify the user which you want to use to login into MariaDB. Like for me,

[nil@centos68 ~]$ whoami
nil
[nil@centos68 ~]$

Now, create user in MariaDB with whatever required privileges.

MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'nil' IDENTIFIED VIA unix_socket;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> select user, host from mysql.user;
+-----------+-----------+
| user      | host      |
+-----------+-----------+
| nil       | %         |
| mysql.sys | localhost |
| root      | localhost |
+-----------+-----------+
3 rows in set (0.00 sec)

and try to login with only username

[nil@centos68 ~]$ mysql -unil
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 10.2.5-MariaDB MariaDB Server
Copyright (c) 2000, 2016, 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 nil@%                                                    |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'nil'@'%' IDENTIFIED VIA unix_socket |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)

Keep in mind that nil user is already authenticated by operating system so when it tries to login into database, it didn’t need password. If you’ll try to login with another OS user then it will not work even if you have added in MariaDB server.

Actually, this plugin can help in many scenarios where you don’t want to store user password in readable format. Like, you want to run some automated script or backup tool or mysql client on local server and your security policy says, you can’t save password, you can use this plugin. This way is really very secure because password will not be store in plain text files which is mostly unprotected way to store password and can be compromised.

Also, some people like me 🙂 alway poor to remember password, can take the advantage by allowing their own OS user accounts to authenticate with the database server.

Leave a Reply

Your email address will not be published. Required fields are marked *