11

I just installed MariaDB 10.1.29 on Ubuntu 18.04. From the command line I can connect using sudo:

sudo mysql -u root -p

But not without sudo.

Also, if I try to connect to the database through DBeaver, I get:

Could not connect: Access denied for user 'root'@'localhost'

While the credentials are correct. I tried installing MySQL 5.7, but I'm experiencing the exact same issue there as well.

What am I missing?

wout
  • 2,477
  • 2
  • 21
  • 32
  • same problem. Didn't find out how to establish a sudo connection in DBeaver – Pawana May 24 '18 at 07:00
  • 1
    I didn't find a solution yet. The problem only exits with the `root` user. In the meantime I created an additional user using the cli and granted permissions on all tables. So now I can manage those databases with the additional user. Hope that helps. – wout May 26 '18 at 13:10
  • 1
    Did you also notice that if you login using `sudo mysql -u root -p` you can write whatever you want for password and it works! And this is why also this works `sudo mysql -u root`... This is a big security issue if you ask me... – 71GA Dec 31 '19 at 07:06

1 Answers1

15

As it turns out, this is expected behaviour for MariaDB and MySQL. To overcome the issue, it is advisable to create a separate user and grant access to all databases created. Here is a step by step guide on how to create databases using the command line and grant permissions to the user of your choice.

Log in to MariaDB/MySQL

$ sudo mysql -u root -p

Create a database

mysql> CREATE DATABASE `database_name`;

Create a user

mysql> CREATE USER 'user_name' IDENTIFIED BY 'a_secure_password';

Grant user permissions

mysql> GRANT ALL PRIVILEGES ON database_name.* TO 'user_name'@'%' WITH GRANT OPTION;

Apply changes

mysql> FLUSH PRIVILEGES;
wout
  • 2,477
  • 2
  • 21
  • 32