An authentication plugin is specified per user, in the CREATE USER or GRANT statement:

CREATE USER serg IDENTIFIED VIA three_attempts USING 'secret';
GRANT USAGE ON *.* TO ''@'%' IDENTIFIED VIA ldap;

The second statement specifies a plugin to use for an anonymous user, it means that if the username was not found in the mysql.user table it will be looked up in the LDAP. This allows storage of the list of users in the LDAP without duplicating it in the MariaDB (or MySQL).

Pluggable authentication adds a new concept to the MySQL plugin architecture—client-side plugins. Indeed, an authentication process is a dialog, if the server wants to use, for example, Kerberos, the client needs to support it too. Client-side plugins do just that: they are loadable modules that are loaded into the client by the libmysqlclient library, and provide the client side of the pluggable authentication.

According to a MySQL client-server protocol, on a new connection, the server sends to a client a so called handshake packet with the server version and capabilities. The client replies with a packet containing the client capabilities and the username. Both packets carry the authentication data provided by the corresponding plugins. They have to use default plugins, because neither a server nor a client can know what plugin was specified for this user in the mysql.user table until the server receives the username from the client. After the server finds out what plugin to use, it switches to the correct plugin and tells the client to do so, as necessary. But the switch happens—if at all—completely transparent for plugins; they do not need to implement anything special to support it.

Now, to get a taste of it, let's write a working plugin that uses USB sticks for logging into the MariaDB server. Sounds cool? A USB mass storage device should (according to the specifications, although not all manufactures adhere to them) have a unique serial number. In Linux, one can easily get serial numbers of all plugged in, even not mounted, USB devices from the /proc/bus/usb/devices file. We will use the serial number of a USB device to authenticate a user.

A server-side authentication plugin starts by including the necessary API header file:

#include <mysql/plugin_auth.h>

We also include a few system headers, we will need them:

#include <string.h>
#include <fcntl.h>
#include <unistd.h>

Just like any other plugin, this needs the usual plugin mumbo-jumbo:

mysql_declare_plugin(usbsn)
{
MYSQL_AUTHENTICATION_PLUGIN,
&usbsn_handler,
"usbsn",
"Sergei Golubchik",
"USB Serial Number",
PLUGIN_LICENSE_GPL,
NULL,
NULL,
0x0100,
NULL,
NULL,
NULL
}
mysql_declare_plugin_end;

We declare an authentication plugin, called usbsn, version 1.0. The plugin declaration refers to the authentication plugin handler, which we declare as follows:

static struct st_mysql_auth usbsn_handler=
{
MYSQL_AUTHENTICATION_INTERFACE_VERSION,
"usbsn",
usbsn_verify
};

This structure contains three members. It starts with the obligatory API version number. Then, it names the client-side plugin that our server-side plugin should work with. It does not need to have the same name as the server plugin. In fact, in many cases, the server plugin only needs to ask the user to enter some information—a password, a key phrase, or a PIN—and MariaDB comes with a useful client-side plugin called dialog that can do just that, ask questions as instructed by the server. That is, in many cases a server plugin can simply use the dialog client plugin. In our case, we need a client to retrieve the serial number of a USB device—and this requires us to write a dedicated client-side plugin. For simplicity, we have called it usbsn too.

The third member of the st_mysql_auth structure is the function that actually performs the authentication. Our authentication function needs to check whether a user—on the client side, not on the server side—has the correct USB stick plugged in. This is not too complex. We will let the client-side authentication plugin read the complete /proc/bus/usb/devices file and send it to the server. The server-side plugin will then see if it contains the serial number that is needed for a given user.

static int usbsn_verify(MYSQL_PLUGIN_VIO *vio, MYSQL_SERVER_AUTH_INFO *info)

This is how the authentication function has to be defined. The first argument, vio (from "Virtual I/O") structure, provides methods to communicate with the client—the read_packet() and write_packet() functions. The info structure gives us a username, that we should authenticate, and—in the auth_string member—the string that was specified in the USING clause of the GRANT or CREATE USER statement. In our case, it will be the serial number of the USB device:

CREATE USER test IDENTIFIED VIA usbsn USING '1A08051410110';

All we need to do is use the vio‑>read_packet() function to read the data that the client plugin has sent, and search it for the serial number, as specified in the info‑>auth_string. The /proc/bus/usb/devices file looks like:

T: Bus=01 Lev=01 Prnt=01 Port=06 Cnt=01 Dev#= 4 Spd=480 MxCh= 0
P: Vendor=0ea0 ProdID=2126 Rev= 2.00
S: Manufacturer=OTi
S: Product=USB Multi- Card Reader
S: SerialNumber=0123456789abcdef
....

We better search for the complete line, with the "S: SerialNumber=" prefix, to make sure we did not, accidentally, find a match in the middle of a longer serial number of some other device:

{
unsigned char *pkt;
int pkt_len;
size_t buflen=strlen(info->auth_string) + 20;
char *buf=alloca(buflen);
my_snprintf(buf, buflen, "S: SerialNumber=%s\n", info->auth_string);

First, we allocate a buffer, and create a string to search for. Note that it uses the my_snprintf() function, which is exported via the my_snprintf service, as explained earlier in this Appendix.

if ((pkt_len= vio->read_packet(vio, &pkt)) < 0)
return CR_ERROR;

Now we read the data, as sent by the client. The vio->read_packet() function returns the number of bytes read and stores the pointer to the data in pkt. The data itself is in the internal buffer of the MySQL network layer, and it will be overwritten on the next I/O operation. In our case it is fine, but if we need the data for a longer time, we will have to copy it.

return strstr(pkt, buf) ? CR_OK : CR_ERROR;
}

Done! After reading the data, all we need is one strstr() call to determine if any of the connected USB devices, on the client side, has the correct serial number or not.

Now, we need to write the client part of our USB authentication.

Similarly, it starts by including the header:

#include <mysql/client_plugin.h>

Client plugins are much simpler than their server counterparts. They cannot be unloaded at runtime. There can be only one plugin in the .so or .dll file and its name has to match the filename. There is only one structure—not two—that describes the plugin.

mysql_declare_client_plugin(AUTHENTICATION)
"usbsn",
"Sergei Golubchik",
"USB Serial Number",
{0,0,1},
NULL,
NULL,
usbsn_send
mysql_end_client_plugin;

The client plugin declaration starts with mysql_declare_client_plugin() with the argument being the plugin type. It is followed by the plugin name, plugin author, plugin description, plugin version (an array of three integers), and three function pointers—for the initialization, de-initialization, and authentication functions. Only the last function is required, the others are optional, and our plugin does not need and does not provide them.

The authentication function of the client plugin has a prototype similar to its server-side partner:

static int usbsn_send(MYSQL_PLUGIN_VIO *vio, struct st_mysql *mysql)

It also takes two arguments—the vio structure and the informational structure with the password in the username. The information structure in the client case is the st_mysql (better known as MYSQL) structure used everywhere in the MySQL client C API.

{
int len, res, fd;
char buf[10240];
fd=open("/proc/bus/usb/devices", O_RDONLY);
if (fd == -1)
return CR_ERROR;
len=read(fd, buf, sizeof(buf)-1);
close(fd);
if (len == 0)
return CR_ERROR;
buf[len++]=0;
res=vio->write_packet(vio, buf, len);
return res ? CR_ERROR : CR_OK;
}

The function itself is very simple, it only reads the file and sends it to the server.

This is all. When this plugin is built and loaded we can try it out. Plug any USB stick into the computer and look up its serial number in the /proc/bus/usb/devices file. For my Sony Micro Vault Tiny 8GB, it is 1A08051410110. Now, I can create a test user with:

CREATE USER test IDENTIFIED VIA usbsn USING '1A08051410110';

After that I can connect to the MariaDB server as this user only if my Micro Vault stick is plugged in—plugged in the computer where I start the command line client, not in the computer that runs the server. Congratulations, it works!