Pluggable Authentication Overview

When a user attempts to log in, the authentication plugin controls how MariaDB Server determines whether the connection is from a legitimate user.

When creating or altering a user account with the GRANT, CREATE USER or ALTER USER statements, you can specify the authentication plugin you want the user account to use by providing the IDENTIFIED VIA clause. By default, when you issue one of these statements for a user account without specifying an authentication plugin, MariaDB uses the mysql_native_password plugin.

MariaDB starting with 10.4

In MariaDB 10.4 and later, there are some notable changes, such as:

  • You can specify multiple authentication plugins for each user account.
  • The root@localhost user created by mysql_install_db is created with the ability to use two authentication plugins. First, it is configured to try to use the unix_socket authentication plugin. This allows the the root@localhost user to login without a password via the local Unix socket file defined by the socket system variable, as long as the login is attempted from a process owned by the operating system root user account. Second, if authentication fails with the unix_socket authentication plugin, then it is configured to try to use the mysql_native_password authentication plugin. However, an invalid password is initially set, so in order to authenticate this way, a password must be set with SET PASSWORD.

Supported Authentication Plugins

The authentication process is a conversation between the server and a client. MariaDB implements both server-side and client-side authentication plugins.

Supported Server Authentication Plugins

MariaDB provides seven server-side authentication plugins:

Supported Client Authentication Plugins

MariaDB provides eight client-side authentication plugins:

MariaDB supports the following server options related to authentication plugins:

Server OptionDescription
old_passwords={1 | 0}If set to 1 (0 is default), MariaDB reverts to using the mysql_old_password authentication plugin by default for newly created users and passwords, instead of the mysql_native_password authentication plugin.
plugin_dir=pathPath to the plugin directory. For security reasons, either make sure this directory can only be read by the server, or set secure_file_priv.
plugin_maturity=levelThe lowest acceptable plugin maturity. MariaDB will not load plugins less mature than the specified level.
secure_authConnections will be blocked if they use the the mysql_old_password authentication plugin. The server will also fail to start if the privilege tables are in the old, pre-MySQL 4.1 format.

Most clients and utilities support some command line arguments related to client authentication plugins:

Client OptionDescription
--connect-expired-passwordNotify the server that this client is prepared to handle expired password sandbox mode even if --batch was specified. From MariaDB 10.4.3.
--default-auth=nameDefault authentication client-side plugin to use.
--plugin-dir=pathDirectory for client-side plugins.
--secure-authRefuse to connect to the server if the server uses the mysql_old_password authentication plugin. This mode is off by default, which is a difference in behavior compared to MySQL 5.6 and later, where it is on by default.

Developers who are using MariaDB Connector/C can implement similar functionality in their application by setting the following options with the mysql_optionsv function:

  • MYSQL_OPT_CAN_HANDLE_EXPIRED_PASSWORDS
  • MYSQL_PLUGIN_DIR
  • MYSQL_DEFAULT_AUTH
  • MYSQL_SECURE_AUTH

For example:

mysql_optionsv(mysql, MYSQL_OPT_CAN_HANDLE_EXPIRED_PASSWORDS, 1);
mysql_optionsv(mysql, MYSQL_DEFAULT_AUTH, "name");
mysql_optionsv(mysql, MYSQL_PLUGIN_DIR, "path");
mysql_optionsv(mysql, MYSQL_SECURE_AUTH, 1);

mysql_install_db supports the following installation options related to authentication plugins:

Installation OptionDescription
--auth-root-authentication-method={normal | socket}If set to normal, it creates a root@localhost account that authenticates with the mysql_native_password authentication plugin and that has no initial password set, which can be insecure. If set to socket, it creates a root@localhost account that authenticates with the unix_socket authentication plugin. Set to normal by default. Available since MariaDB 10.1.
--auth-root-socket-user=USERUsed with --auth-root-authentication-method=socket. It specifies the name of the second account to create with SUPER privileges in addition to root, as well as of the system account allowed to access it. Defaults to the value of --user.

Extended SQL Syntax

MariaDB has extended the SQL standard GRANT, CREATE USER, and ALTER USER statements, so that they support specifying different authentication plugins for specific users. An authentication plugin can be specified with these statements by providing the IDENTIFIED VIA clause.

For example, the GRANT syntax is:

GRANT <privileges> ON <level> TO <user> 
   IDENTIFIED VIA <plugin> [ USING <string> ]

And the CREATE USER syntax is:

CREATE USER <user> 
   IDENTIFIED VIA <plugin> [ USING <string> ]

And the ALTER USER syntax is:

ALTER USER <user> 
   IDENTIFIED VIA <plugin> [ USING <string> ]

The optional USING clause allows users to provide an authentication string to a plugin. The authentication string's format and meaning is completely defined by the plugin.

For example, for the mysql_native_password authentication plugin, the authentication string should be a password hash:

CREATE USER mysqltest_up1 
   IDENTIFIED VIA mysql_native_password USING '*E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB';

Since mysql_native_password is the default authentication plugin, the above is just another way of saying the following:

CREATE USER mysqltest_up1 
   IDENTIFIED BY PASSWORD '*E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB';

In contrast, for the pam authentication plugin, the authentication string should refer to a PAM service name:

CREATE USER mysqltest_up1 
   IDENTIFIED VIA pam USING 'mariadb';
MariaDB starting with 10.4

In MariaDB 10.4 and later, a user account can be associated with multiple authentication plugins.

For example, to configure the root@localhost user account to try the unix_socket authentication plugin, followed by the mysql_native_password authentication plugin as a backup, you could execute the following:

CREATE USER root@localhost 
   IDENTIFIED VIA unix_socket 
   OR mysql_native_password USING PASSWORD("verysecret");

See Authentication from MariaDB 10.4 for more information.

Authentication Plugins Installed by Default

Server Authentication Plugins Installed by Default

Not all server-side authentication plugins are installed by default. If a specific server-side authentication plugin is not installed by default, then you can find the installation procedure on the documentation page for the specific authentication plugin.


MariaDB starting with 10.4

In MariaDB 10.4 and later, the following server-side authentication plugins are installed by default:

  • The unix_socket authentication plugin is installed by default in all builds on Unix and Linux.
  • The named_pipe authentication plugin is installed by default in all builds on Windows.
MariaDB until 10.3

In MariaDB 10.3 and below, the following server-side authentication plugins are installed by default:

  • The unix_socket authentication plugin is installed by default in new installations that use the .deb packages provided by Debian's default repositories in Debian 9 and later and Ubuntu's default repositories in Ubuntu 15.10 and later. See Differences in MariaDB in Debian (and Ubuntu) for more information.
  • The named_pipe authentication plugin is installed by default in all builds on Windows.

Client Authentication Plugins Installed by Default

Client-side authentication plugins do not need to be installed in the same way that server-side authentication plugins do. If the client uses either the libmysqlclient or MariaDB Connector/C library, then the library automatically loads client-side authentication plugins from the library's plugin directory whenever they are needed.

Most clients and utilities support the --plugin-dir command line argument that can be used to set the path to the library's plugin directory:

Client OptionDescription
--plugin-dir=pathDirectory for client-side plugins.

Developers who are using MariaDB Connector/C can implement similar functionality in their application by setting the MYSQL_PLUGIN_DIR option with the mysql_optionsv function.

For example:

mysql_optionsv(mysql, MYSQL_PLUGIN_DIR, "path");

If your client encounters errors similar to the following, then you may need to set the path to the library's plugin directory:

ERROR 2059 (HY000): Authentication plugin 'dialog' cannot be loaded: /usr/lib/mysql/plugin/dialog.so: cannot open shared object file: No such file or directory

If the client does not use either the libmysqlclient or MariaDB Connector/C library, then you will have to determine which authentication plugins are supported by the specific client library used by the client.

If the client uses either the libmysqlclient or MariaDB Connector/C library, but the client is not bundled with either library's optional client authentication plugins, then you can only use the conventional authentication plugins (like mysql_native_password and mysql_old_password) and the non-conventional authentication plugins that don't require special client-side authentication plugins (like unix_socket and named_pipe).

Default Authentication Plugin

Default Server Authentication Plugin

The mysql_native_password authentication plugin is currently the default authentication plugin in all versions of MariaDB if the old_passwords system variable is set to 0, which is the default.

On a system with the old_passwords system variable set to 0, this means that if you create a user account with either the GRANT or CREATE USER statements, and if you do not specify an authentication plugin with the IDENTIFIED VIA clause, then MariaDB will use the mysql_native_password authentication plugin for the user account.

For example, this user account will use the mysql_native_password authentication plugin:

CREATE USER username@hostname;

And so will this user account:

CREATE USER username@hostname IDENTIFIED BY 'notagoodpassword';

The mysql_old_password authentication plugin becomes the default authentication plugin in all versions of MariaDB if the old_passwords system variable is explicitly set to 1.

However, the mysql_old_password authentication plugin is not considered secure, so it is recommended to avoid using this authentication plugin. To help prevent undesired use of the mysql_old_password authentication plugin, the server supports the secure_auth system variable that can be used to configured the server to refuse connections that try to use the mysql_old_password authentication plugin:

Server OptionDescription
old_passwords={1 | 0}If set to 1 (0 is default), MariaDB reverts to using the mysql_old_password authentication plugin by default for newly created users and passwords, instead of the mysql_native_password authentication plugin.
secure_authConnections will be blocked if they use the the mysql_old_password authentication plugin. The server will also fail to start if the privilege tables are in the old, pre-MySQL 4.1 format.

Most clients and utilities also support the --secure-auth command line argument that can also be used to configure the client to refuse to connect to servers that use the mysql_old_password authentication plugin:

Client OptionDescription
--secure-authRefuse to connect to the server if the server uses the mysql_old_password authentication plugin. This mode is off by default, which is a difference in behavior compared to MySQL 5.6 and later, where it is on by default.

Developers who are using MariaDB Connector/C can implement similar functionality in their application by setting the MYSQL_SECURE_AUTH option with the mysql_optionsv function.

For example:

mysql_optionsv(mysql, MYSQL_SECURE_AUTH, 1);

Default Client Authentication Plugin

The 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.

Setting the Default Client Authentication Plugin

Most clients and utilities support the --default-auth command line argument that can be used to set the default client-side authentication plugin:

Client OptionDescription
--default-auth=nameDefault authentication client-side plugin to use.

Developers who are using MariaDB Connector/C can implement similar functionality in their application by setting the MYSQL_DEFAULT_AUTH option with the mysql_optionsv function.

For example:

mysql_optionsv(mysql, MYSQL_DEFAULT_AUTH, "name");

If you know that your user account is configured to require a client-side authentication plugin that isn't mysql_old_password or mysql_native_password, then it can help speed up your connection process to explicitly set the default client-side authentication plugin.

According to the client-server protocol, the server first sends the handshake packet to the client, then the client replies with a packet containing the user name of the user account that is requesting access. The server handshake packet initially tells the client to use the default server authentication plugin, and the client reply initially tells the server that it will use the default client authentication plugin.

However, the server-side and client-side authentication plugins mentioned in these initial packets may not be the correct ones for this specific user account. The server only knows what authentication plugin to use for this specific user account after reading the user name from the client reply packet and finding the appropriate row for the user account in either the mysql.user table or the mysql.global_priv table, depending on the MariaDB version.

If the server finds that either the server-side or client-side default authentication plugin does not match the actual authentication plugin that should be used for the given user account, then the server restarts the authentication on either the server side or the client side.

This means that, if you know what client authentication plugin your user account requires, then you can avoid an unnecessary authentication restart and you can save two packets and two round-trips.between the client and server by configuring your client to use the correct authentication plugin by default.

Authentication Plugins

Server Authentication Plugins

mysql_native_password

The mysql_native_password authentication plugin uses the password hashing algorithm introduced in MySQL 4.1, which is also used by the PASSWORD() function when old_passwords=0 is set. This hashing algorithm is based on SHA-1.

mysql_old_password

The mysql_old_password authentication plugin uses the pre-MySQL 4.1 password hashing algorithm, which is also used by the OLD_PASSWORD() function and by the PASSWORD() function when old_passwords=1 is set.

ed25519

The ed25519 authentication plugin uses Elliptic Curve Digital Signature Algorithm to securely store users' passwords and to authenticate users. The ed25519 algorithm is the same one that is used by OpenSSH. It is based on the elliptic curve and code created by Daniel J. Bernstein.

From a user's perspective, the ed25519 authentication plugin still provides conventional password-based authentication.

gssapi

The gssapi authentication plugin allows the user to authenticate with services that use the Generic Security Services Application Program Interface (GSSAPI). Windows has a slightly different but very similar API called Security Support Provider Interface (SSPI).

On Windows, this authentication plugin supports Kerberos and NTLM authentication. Windows authentication is supported regardless of whether a domain is used in the environment.

On Unix systems, the most dominant GSSAPI service is Kerberos. However, it is less commonly used on Unix systems than it is on Windows. Regardless, this authentication plugin also supports Kerberos authentication on Unix.

The gssapi authentication plugin is most often used for authenticating with Microsoft Active Directory.

pam

The pam authentication plugin allows MariaDB to offload user authentication to the system's Pluggable Authentication Module (PAM) framework. PAM is an authentication framework used by Linux, FreeBSD, Solaris, and other Unix-like operating systems.

unix_socket

The unix_socket authentication plugin allows the user to use operating system credentials when connecting to MariaDB via the local Unix socket file. This Unix socket file is defined by the socket system variable.

The unix_socket authentication plugin works by calling the getsockopt system call with the SO_PEERCRED socket option, which allows it to retrieve the uid of the process that is connected to the socket. It is then able to get the user name associated with that uid. Once it has the user name, it will authenticate the connecting user as the MariaDB account that has the same user name.

For example:

$ mysql -uroot
MariaDB []> CREATE USER serg IDENTIFIED VIA unix_socket;
MariaDB []> CREATE USER monty IDENTIFIED VIA unix_socket;
MariaDB []> quit
Bye
$ whoami
serg
$ mysql --user=serg
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.2.0-MariaDB-alpha-debug Source distribution
MariaDB []> quit
Bye
$ mysql --user=monty
ERROR 1045 (28000): Access denied for user 'monty'@'localhost' (using password: NO)

In this example, a user serg is already logged into the operating system and has full shell access. He has already authenticated with the operating system and his MariaDB account is configured to use the unix_socket authentication plugin, so he does not need to authenticate again for the database. MariaDB accepts his operating system credentials and allows him to connect. However, any attempt to connect to the database as another operating system user will be denied.

named_pipe

The named_pipe authentication plugin allows the user to use operating system credentials when connecting to MariaDB via named pipe on Windows. Named pipe connections are enabled by the named_pipe system variable.

The named_pipe authentication plugin works by using named pipe impersonation and calling GetUserName() to retrieve the user name of the process that is connected to the named pipe. Once it has the user name, it authenticates the connecting user as the MariaDB account that has the same user name.

For example:

CREATE USER wlad IDENTIFIED VIA named_pipe;
CREATE USER monty IDENTIFIED VIA named_pipe;
quit

C:\>echo %USERNAME%
wlad

C:\> mysql --user=wlad --protocol=PIPE
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.1.12-MariaDB-debug Source distribution

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> quit
Bye

C:\> mysql --user=monty  --protocol=PIPE
ERROR 1698 (28000): Access denied for user 'monty'@'localhost'

Authentication Plugin API

The authentication plugin API is extensively documented in the source code in the following files:

  • mysql/plugin_auth.h (server part)
  • mysql/client_plugin.h (client part)
  • mysql/plugin_auth_common.h (common parts)

The MariaDB source code also contains some authentication plugins that are intended explicitly to be examples for developers. They are located in plugin/auth_examples.

The definitions of two example authentication plugins called two_questions and three_attempts can be seen in plugin/auth_examples/dialog_examples.c. These authentication plugins demonstrate how to communicate with the user using the dialog client authentication plugin.

The two_questions authentication plugin asks the user for a password and a confirmation ("Are you sure?").

The three_attempts authentication plugin gives the user three attempts to enter a correct password.

The password for both of these plugins should be specified in the plain text in the USING clause:

CREATE USER insecure IDENTIFIED VIA two_questions USING 'notverysecret';

Dialog Client Authentication Plugin - Client Library Extension

The dialog client authentication plugin, strictly speaking, is not part of the client-server or authentication plugin API. But it can be loaded into any client application that uses the libmysqlclient or MariaDB Connector/C libraries. This authentication plugin provides a way for the application to customize the UI of the dialog function.

In order to use the dialog client authentication plugin to communicate with the user in a customized way, the application will need to implement a function with the following signature:

extern "C" char *mysql_authentication_dialog_ask(
  MYSQL *mysql, int type, const char *prompt, char *buf, int buf_len)

The function takes the following arguments:

  • The connection handle.
  • A question "type", which has one of the following values:
    • 1 - Normal question
    • 2 - Password (no echo)
  • A prompt.
  • A buffer.
  • The length of the buffer.

The function returns a pointer to a string of characters, as entered by the user. It may be stored in buf or allocated with malloc().

Using this function a GUI application can pop up a dialog window, a network application can send the question over the network, as required. If no mysql_authentication_dialog_ask function is provided by the application, the dialog client authentication plugin falls back to fputs() and fgets().

Providing this callback is particularly important on Windows, because Windows GUI applications have no associated console and the default dialog function will not be able to reach the user. An example of Windows GUI client that does it correctly is HeidiSQL.

See Also

Comments

Comments loading...