Search code examples
mysqldatabaseubuntumariadbdbeaver

Unable to connect to MariaDB using DBeaver

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?

Solution

  • 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;