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?


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         | |          | unix_socket |
| root         | ::1       |          | unix_socket |
| serg         | localhost |          | unix_socket |
5 rows in set (0.00 sec)


Then in the shell:

sudo service mysql restart

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

  • I had to add @localhost to the CREATE USER... to get it to work. Otherwise, I would get Can't find any matching row in the user table – Rich Remer Nov 24 '17 at 21:08

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







sudo service mysql restart

mysql -h localhost -u user

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.