MariaDB Authentication Plugins – Managing secured MariaDB connections

---

Introduction

Database Systems are the heart of the business, The information powers modern business and so they are equally sensitive. The data security management begins from user access and connection management, In this post we have explained how MariaDB Authentication Plugin works to build and manage secured MariaDB connection management. MariaDB Authentication Plugin control controls how MariaDB Server approves legitimate connections. By default MariaDB uses mariadb_native_password plugin but you can do user creation and management (GRANT, CREATE USER or ALTER USER) by specifying authentication plugin you want the user account to use by providing the IDENTIFIED VIA clause. MariaDB authentication process is a two way authorization and communication activity between MariaDB Server and MariaDB Client so MariaDB has implemented both server-side and client-side authentication plugins:

MariaDB Server Authentication Plugins	MariaDB Client Authentication Plugins
mysql_native_password	mysql_native_password
mysql_old_password	mysql_old_password
ed25519	client_ed25519
gssapi	auth_gssapi_client
pam (Unix only)	dialog
unix_socket (Unix only)	mysql_clear_password
named_pipe (Windows only)	sha256_password and caching_sha256_password

MariaDB (Server and Client) Default Authentication Plugin

Default MariaDB Server Authentication Plugin

Default MariaDB Client Authentication Plugin

In MariaDB Server the default authentication plugin is mysql_native_password when system variable old_passwords is set to "0" ( default value )

Default client-side authentication plugin depends on a few factors. If a client doesn't explicitly set the default client-side authentication plugin, then the client will determine which authentication plugin to use by checking the length of the scramble in the server's handshake packet. If the server's handshake packet contains a 9-byte scramble, then the client will default to the mysql_old_password authentication plugin. If the server's handshake packet contains a 20-byte scramble, then the client will default to the mysql_native_password authentication plugin.

MariaDB Authentication Plugin new features ( MariaDB 10.4 and later )

• Multiple authentication plugin for each user account (example copied below in the post )
• More secured user management with MariaDB 10.4:
  • MariaDB user root@localhost is created by mysql_install_db with ability to use two authentication plugins:
    • unix_socket : Allows user root@localhost to login without a password (as long as the login is attempted from a process owned by the operating system root user account ) via the local Unix Socket file defined by system variable socket
    • mysql_native_password : If user authentication fails with unix_socket then it’s configured with mysql_native_password with invalid password set initially in order to authenticate the login and later a password must be set with SET_PASSWORD

MariaDB 10.4 and later, You can create user account with multiple authentication plugins

For example, You can now configure root@localhost user with unix_socket authentication plugin, followed by mysql_native_password authentication plugin as an alternative: