Mariadb – Different ways to log in to MariaDB

authenticationmariadb

I'm trying to log in to a mariaDB database which is (at least the vendor of the product that it was installed for tells so) "protected" by SSL.

I tried several tools but none of them works fully.

  • MySQL Workbench exits silently when I try to connect but when I choose the "reverse engineering" menu entry it is able to extract and display the schema.

  • HeidiSQL fails with Access denied for user 'username'@'fqdn.of.my.computer' (using password: YES)

  • dBeaver shows the same: Access denied for user 'username'@'fqdn.of.my.computer' (using password: YES)

  • Database Workbench fails with the same error as the others: Access denied for user 'username'@'fqdn.of.my.computer' (using password: YES)

I am sure that the credentials are typed correctly because I copy them from a notepad window and they work with MySQL Workbench.
All tools are run with the current version available, community or free and not paid)

Are there different ways to authenticate with a MariaDB server ? Basically MySQL Workbench seems to work the best, but because it fails silently with a "full" connection this is also not feasible.

Best Answer

Yes, there are several ways to authenticate, but you have to specify this per database user. Before MariaDB 10.4 each user could only use one authentication method. You can change the authentication method for a user if you can log in as the root or other sufficiently privileged user. Use the ALTER USER statement if you're on MariaDB 10.2+, otherwise use UPDATE mysql.user SET plugin = '...' WHERE user = 'the_user' AND host = 'the_host';.

Authentication is done by authentication plugins. Run e.g. this to see which plugins you currently use for your database users:

SELECT user, host, plugin FROM mysql.user;

Some common authentication plugins are mysql_native_password and unix_socket. The former requires a password and works over TCP whereas the latter works over the MariaDB socket file (by default it's at /var/lib/mysql/mysql.sock in Linux) and doesn't require a password, but it assumes your OS user has the same name as your database user, and obviously only works if you're on the same host as the MariaDB server. Perhaps this is something you could try to avoid SSL?

Relevant MariaDB Knowledgebase documentation

Related Question