Pluggable Authentication

In MariaDB 5.2 the authentication of users is delegated to plugins. Two plugins are always available: mysql_native_password and mysql_old_password - they implement the compatible MySQL password authentication with 20 byte (Used in MySQL 4.0 or later) and 9 byte (used in MySQL 3.23) scrambles.

SQL extension

An authentication plugin is specified per user with the extended GRANT or CREATE USER statements:

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

The optional USING clause provides the authentication information for a plugin, its format is completely defined by the plugin. For example, for mysql_native_password it should be a password hash:

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

which is just another way of saying

CREATE USER mysqltest_up1 IDENTIFIED BY PASSWORD '*E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB';

MariaDB comes with three authentication plugins, but they are not installed by default. The unix_socket plugin allows a user to connect via a unix socket, if the user is already authenticated to the OS. For example:

$ 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 [test]> 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 system and has full shell access. Because he has identified himself to an OS, he does not need to do it again for the database, MariaDB trusts OS credentials. But he cannot connect to the database as another user. Both users were created to use the unix_socket plugin.

Two other plugins in the MariaDB distribution (two_questions and three_attempts) demonstrate how to perform a dialog with the user. The first plugin asks the user for a password and a confirmation ("Are you sure?"), the second plugin gives the user three attempts to enter a correct password. The password for these plugins should be specified in the plain text in the USING clause:

CREATE USER insecure IDENTIFIED VIA two_questions USING 'notverysecret';

Client tools

The authentication process is a conversation between the server and a client. MariaDB implements both server-side and client-side authentication plugins. Client plugins are loaded into the client library automatically on demand. Clients that do not use libmysqlclient library or that use a completely static build of it can only use the conventional MySQL authentication and server plugins (like unix_socket) that do not converse with a client.

The mysql command line client received two new command line arguments:

Two plugins are built into the client library and they provide the conventional MySQL authentication. The dialog plugin, which comes with the MariaDB distribution, reads the prompt from the server, prints it to the user, reads a line as entered by the user, and sends it back to the server. Both server example plugins use it to communicate with the user.

Client library extensions

Authentication plugins are loaded automatically and transparently for the client application, no special support from the client is needed. Still, a client may need to specify the location where the plugins will be loaded from or a default authentication plugin (see Default client plugin, below) to use. This is done with the mysql_options function:

mysql_options(mysql, MYSQL_PLUGIN_DIR, "path");
mysql_options(mysql, MYSQL_DEFAULT_AUTH, "name");

Default client 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 a user name.

Only after reading the client packet the server can find the appropriate row in the mysql.user table and know what authentication plugin to use for the given user. The server handshake packet is sent using the default server authentication plugin, the client reply uses the default client authentication plugin. If the server finds that either of the defaults do not match the actual plugin that should be used for the given user, the server restarts the authentication on either the server or the client side.

That is, by using a correct authentication plugin as a default, a client can avoid authentication restart and have two packets and two round-trips less.

Dialog client plugin

The Dialog client 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 library. This plugin provides a way for the application to customize the UI of the dialog function.

If the application implements a function such as:

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

the dialog plugin will use it. The function takes a question "type" (which is 1 for a normal question, and 2 for a password (no echo) question), a prompt, and a 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, a dialog 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.

Authentication Plugin API

The authentication plugin API is extensively documented in the mysql/plugin_auth.h (server part), mysql/client_plugin.h (client part), and mysql/plugin_auth_common.h files in the source code. See also the authentication plugin examples in plugin/auth/.

See also

1. MySQL manual about pluggable authentication
2. MySQL manual about writing authentication plugins