MariaDB: Create and grant a new user using unix sockets plugin (passwordless)

Question

I just installed MariaDB on Kubuntu 15.10. I am able to log in with the root user via the plugin that authenticates the user from the operating system. (This is new to me, so I am learning about it rather than removing the plugin authentication as most tutorials seem to recommend.)

Now I want to create a non-root user and grant all privileges to that user and allow the user to log into mysql (on localhost) without a password (using just the plugin). How would I do this? Do I need to give the user a password too?

Answer 1

Found the answer. The part I needed was "IDENTIFIED VIA unix_socket" as shown below:

MariaDB [(none)]> CREATE USER serg IDENTIFIED VIA unix_socket;
MariaDB [(none)]> GRANT ALL PRIVILEGES on mydatabase.* to 'serg'@'localhost';

MariaDB [(none)]> select user, host, password, plugin from mysql.user;
+--------------+-----------+----------+-------------+
| user         | host      | password | plugin      |
+--------------+-----------+----------+-------------+
| root         | localhost |          | unix_socket |
| root         | mitra     |          | unix_socket |
| root         | 127.0.0.1 |          | unix_socket |
| root         | ::1       |          | unix_socket |
| serg         | localhost |          | unix_socket |
+--------------+-----------+----------+-------------+
5 rows in set (0.00 sec)

MariaDB [(none)]> FLUSH PRIVILEGES;

Then in the shell:

sudo service mysql restart

To log in using user 'serg' do not use sudo. Just use mysql -u serg.

Answer 2

You can assign password for that user and do the entry in my.cnf file.

• grant all privileges on mydatabas. to user@localhost identified by 'password' ;*

edit my.cnf

[mysql]

user=user

password=password

[mysqladmin]

user=user

password=password

sudo service mysql restart

mysql -h localhost -u user

Answer 3

According to the doc:

If SQL_MODE does not have NO_AUTO_CREATE_USER set, then you can also create the user account via GRANT. For example:

so this is enough:

GRANT SELECT ON db.* TO username@hostname IDENTIFIED VIA unix_socket;