Expert MySQL, Second Edition (2012)

PART 2. Extending MySQL

CHAPTER 9. Developing MySQL Plugins

During our tour of the MySQL source code and architecture in Chapter 3, we touched on a special feature of MySQL called plugins. MySQL plugins are specially designed dynamic libraries that allow you to add new functionality to the server without taking the server offline. There are several forms of plugins supported today, but as the server continues to evolve, expect to see more features offered using this architecture.

This chapter examines the MySQL plugin architecture in more detail. You will learn more about how plugins work, how they are constructed, and what types of plugins are supported by the server. I will also demonstrate how to create a plugin by creating a unique authentication plugin.

MySQL Plugins Explained

MySQL plugins are contained in dynamically loadable modules called libraries. A library can contain one or more plugins and can be installed (loaded) or uninstalled (unloaded) individually. Plugins provide extensions to the server in the form of specialized features. Aside from the feature itself, plugins can also contain their own status and system variables. A plugin is developed using a standardized architecture called an application programming interface (API).

The plugin architecture (called the MySQL Plugin API) uses a special set of structures that contain information as well as function pointers to common methods. Using a common structure allows the server to call specific methods whereby the function pointers remap the calls to the specific implementation of the method for that plugin. I will explain the plugin architecture in more detail in a later section.

Types of Plugins

The MySQL server currently supports several types of plugins. We have already seen an example of a very early form of plugin—user defined functions¹ (UDF). Chapter 7 presented this form of plugin in great detail. Some would say UDFs are not true plugins, despite the fact they are dynamically loadable and use the same commands to install and uninstall. This is because they do not use the standard plugin architecture.

Table 9-1 lists the types of plugins that are supported using this architecture, including the name of the plugin, the type name, a short description, and the location of an example in the source code, if one exists.

Table 9-1. Plugins Supported by the MySQL Plugin API

Type	Description	Example
UDF	Special functions for use in SQL commands.	/sql/udf_example.cc
Storage Engine	Storage engines for reading and writing data.	/storage/*
Full Text Parser	Full text parser for searching text columns in tables.	/plugin/fulltext
Daemon	Permits the loading of discrete code modules into the server without interacting with the server itself, such as replication heartbeat and monitoring	/plugin/daemon_example
Information Schema	Permits the creation of new INFORMATION_SCHEMA views for communicating information to the user.
Audit	Enables server auditing. An audit log plugin exists in the commercial release of MySQL.	/plugin/audit_null
Replication	Specialized replication features, such as changing the synchronization method of event execution.	/plugin/semisync
Authentication	Change the authentication method for logging into the server.	/plugin/auth
Validate Password	Enforce password rules for more secure passwords.	/plugin/password_validation

As you can see, there are many types of plugins, and they provide a wide range of features. With emphasis on more modular design, we are likely to see more plugin types in the future.

Using MySQL Plugins

Plugins can be loaded and unloaded either with special SQL commands, as startup options, or via the mysql_plugin client application.

To load a plugin using an SQL command, use the LOAD PLUGIN command as follows. Here, we are loading a plugin named something_cool that is contained in the compiled-library module named some_cool_feature.so. These libraries need to be placed in theplugin_dir path so that the server can find them.

Note The MySQL documentation uses the terms install and uninstall for dynamically loading and unloading plugins. The documentation uses the term load for specifying a plugin to use via a startup option.

mysql> INSTALL PLUGIN something_cool SONAME some_cool_feature.so;

Uninstalling the plugin is easier and is shown below. Here we are unloading the same plugin we just installed.

mysql> UNINSTALL PLUGIN something_cool;

Plugins can also be installed at startup using the --plugin-load option. This option can either be listed multiple times—once for each plugin—or can accept a semicolon-separated list (no spaces). Examples of how to use this option include:

mysqld ... --plugin-load=something_cool ...
mysqld ... --plugin-load=something_cool;something_even_better ...

Plugins can also be loaded and unloaded using the mysql_plugin client application. This application requires the server to be down to work. It will launch the server in bootstrap mode, load or unload the plugin, and then shut down the bootstrapped server. The application is used primarily for maintenance of servers during downtime or as a diagnostic tool for attempts to restart a failed server by eliminating plugins (to simplify diagnosis).

The client application uses a configuration file to keep pertinent data about the plugin, such as the name of the library and all of the plugins contain within. A plugin library can contain more than one plugin. The following is an example of the configuration file for the daemon_exampleplugin.

#
# Plugin configuration file. Place on a separate line:
#
# library binary file name (without .so or .dll)
# component_name
# [component_name] - additional components in plugin
#
libdaemon_example
daemon_example

To use the mysql_plugin application to install (enable) or uninstall (disable) plugins, specify the name of the plugin, ENABLE or DISABLE, basedir, datadir, plugin-dir, and plugin-ini options at a minimum. You may also need to specify the my-print-defaultsoption if that application is not on your path. The application runs silently, but you can turn on verbosity to see the application in action (−vvv). The following depicts an example of loading the daemon_example plugin using the client application.

cbell$ sudo ./mysql_plugin --datadir=/mysql_path/data/ --basedir=/mysql_path/ --plugin-dir=../plugin/daemon_example/ --plugin-ini=../plugin/daemon_example/daemon_example.ini --my-print-defaults=../extra daemon_example ENABLE -vvv
# Found tool 'my_print_defaults' as '/mysql_path/bin/my_print_defaults'.
# Command: /mysql_path/bin/my_print_defaults mysqld > /var/tmp/txtdoaw2b
# basedir = /mysql_path/
# plugin_dir = ../plugin/daemon_example/
# datadir = /mysql_path/data/
# plugin_ini = ../plugin/daemon_example/daemon_example.ini
# Found tool 'mysqld' as '/mysql_path/bin/mysqld'.
# Found plugin 'daemon_example' as '../plugin/daemon_example/libdaemon_example.so'
# Enabling daemon_example...
# Query: REPLACE INTO mysql.plugin VALUES ('daemon_example','libdaemon_example.so');
# Command: /mysql_path/bin/mysqld --no-defaults --bootstrap --datadir=/mysql_path/data/ --basedir=/mysql_path/ < /var/tmp/sqlft1mF7
# Operation succeeded.

Notice in the output that I had to use super-user privileges. You will need to use this if you are attempting to install or uninstall plugins from a server installed on platforms that isolate access to the mysql folders, such as Linux and Mac OS X. Notice also that the verbose output shows you exactly what this application is doing. In this case, it is replacing any rows in the mysql.plugin table with the information for the plugin we specified. Similar delete queries would be issued for disabling a plugin.

You can discover which plugins are loaded or have been loaded in one of three ways. You can use a special SHOW command, select information from the mysql.plugin table, or select information from the INFORMATION_SCHEMA.plugins view. Each of these displays slightly different information. The following demonstrates each of these commands. I use excerpts of the output for brevity.

Notice the information shown in the SHOW PLUGINS command output. This view is a list of all known plugins, some of which are loaded automatically via either command-line options or from special compilation directives. It shows the plugin type as well as the license type. Now, let’s see the output of the mysql.plugin table.

mysql> select * from mysql.plugin;
Empty set (0.00 sec)

But wait, there is no output! This is because the mysql.plugin table stores only those dynamic plugins that have been installed—more specifically, those that were installed with the INSTALL PLUGIN command. Since we did not install any plugins, there is nothing to display. The following shows the output when a plugin has been installed.

mysql> install plugin daemon_example soname 'libdaemon_example.so';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from mysql.plugin;
+−−--------------+−−--------------------+
| name | dl |
+−−--------------+−−--------------------+
| daemon_example | libdaemon_example.so |
+−−--------------+−−--------------------+
1 row in set (0.00 sec)

Now let’s see the output of the INFORMATION_SCHEMA.plugins view. The following shows the output of the view.

mysql> select * from information_schema.plugins \G
*************************** 1. row ***************************
PLUGIN_NAME: binlog
PLUGIN_VERSION: 1.0
PLUGIN_STATUS: ACTIVE
PLUGIN_TYPE: STORAGE ENGINE
PLUGIN_TYPE_VERSION: 50606.0
PLUGIN_LIBRARY: NULL
PLUGIN_LIBRARY_VERSION: NULL
PLUGIN_AUTHOR: MySQL AB
PLUGIN_DESCRIPTION: This is a pseudo storage engine to represent the binlog in a transaction
PLUGIN_LICENSE: GPL
LOAD_OPTION: FORCE
*************************** 2. row ***************************
PLUGIN_NAME: mysql_native_password
PLUGIN_VERSION: 1.0
PLUGIN_STATUS: ACTIVE
PLUGIN_TYPE: AUTHENTICATION
PLUGIN_TYPE_VERSION: 1.0
PLUGIN_LIBRARY: NULL
PLUGIN_LIBRARY_VERSION: NULL
PLUGIN_AUTHOR: R.J.Silk, Sergei Golubchik
PLUGIN_DESCRIPTION: Native MySQL authentication
PLUGIN_LICENSE: GPL
LOAD_OPTION: FORCE
*************************** 3. row ***************************
PLUGIN_NAME: mysql_old_password
PLUGIN_VERSION: 1.0
PLUGIN_STATUS: ACTIVE
PLUGIN_TYPE: AUTHENTICATION
PLUGIN_TYPE_VERSION: 1.0
PLUGIN_LIBRARY: NULL
PLUGIN_LIBRARY_VERSION: NULL
PLUGIN_AUTHOR: R.J.Silk, Sergei Golubchik
PLUGIN_DESCRIPTION: Old MySQL-4.0 authentication
PLUGIN_LICENSE: GPL
LOAD_OPTION: FORCE
...

*************************** 41. row ***************************
PLUGIN_NAME: partition
PLUGIN_VERSION: 1.0
PLUGIN_STATUS: ACTIVE
PLUGIN_TYPE: STORAGE ENGINE
PLUGIN_TYPE_VERSION: 50606.0
PLUGIN_LIBRARY: NULL
PLUGIN_LIBRARY_VERSION: NULL
PLUGIN_AUTHOR: Mikael Ronstrom, MySQL AB
PLUGIN_DESCRIPTION: Partition Storage Engine Helper
PLUGIN_LICENSE: GPL
LOAD_OPTION: ON
*************************** 42. row ***************************
PLUGIN_NAME: daemon_example
PLUGIN_VERSION: 1.0
PLUGIN_STATUS: ACTIVE
PLUGIN_TYPE: DAEMON
PLUGIN_TYPE_VERSION: 50606.0
PLUGIN_LIBRARY: libdaemon_example.so
PLUGIN_LIBRARY_VERSION: 1.4
PLUGIN_AUTHOR: Brian Aker
PLUGIN_DESCRIPTION: Daemon example, creates a heartbeat beat file in mysql-heartbeat.log
PLUGIN_LICENSE: GPL
LOAD_OPTION: ON
42 rows in set (0.01 sec)

mysql>

We see the similar information as with the SHOW PLUGINS command, but with additional information. Aside from the name, type, and license information, we also see the author, version, and description of the plugin. Notice also that the dynamically loaded plugin, thedaemon_example, is also displayed in the view.

Now that you know what each of these commands does, you can use the appropriate command for working with plugins. For example, if you want to see which plugins are available at a glance, use the SHOW PLUGINS command. If you want to see which plugins are loaded, query themysql.plugin table. If you want to see the metadata about the available plugins, query the INFORMATION_SCHEMA.plugins view.

The MySQL Plugin API

The plugin architecture is defined in /include/mysql/plugin.h. There are many elements in this file, including specialized code for some of the plugin types. A complete explanation of every line of code is beyond the scope of this work; rather, in this section, we focus on the key elements you need to be familiar with in order to build your own plugins.

Near the top of the file, you will find the defines for the symbols and values used in creating plugins. Listing 9-1 shows the definitions of the most frequently used symbols. There are definitions for each plugin type as well as definitions for license type.

Listing 9-1. Definitions from plugin.h

/*
The allowable types of plugins
*/
#define MYSQL_UDF_PLUGIN 0 /* User-defined function */
#define MYSQL_STORAGE_ENGINE_PLUGIN 1 /* Storage Engine */
#define MYSQL_FTPARSER_PLUGIN 2 /* Full-text parser plugin */
#define MYSQL_DAEMON_PLUGIN 3 /* The daemon/raw plugin type */
#define MYSQL_INFORMATION_SCHEMA_PLUGIN 4 /* The I_S plugin type */
#define MYSQL_AUDIT_PLUGIN 5 /* The Audit plugin type */
#define MYSQL_REPLICATION_PLUGIN 6 /* The replication plugin type */
#define MYSQL_AUTHENTICATION_PLUGIN 7 /* The authentication plugin type */
#define MYSQL_VALIDATE_PASSWORD_PLUGIN 8 /* validate password plugin type */
#define MYSQL_MAX_PLUGIN_TYPE_NUM 9 /* The number of plugin types */

/* We use the following strings to define licenses for plugins */
#define PLUGIN_LICENSE_PROPRIETARY 0
#define PLUGIN_LICENSE_GPL 1
#define PLUGIN_LICENSE_BSD 2

#define PLUGIN_LICENSE_PROPRIETARY_STRING "PROPRIETARY"
#define PLUGIN_LICENSE_GPL_STRING "GPL"
#define PLUGIN_LICENSE_BSD_STRING "BSD"

There are definitions for the type of license the plugin supports. For most standard MySQL plugins, the license is GPL. For those plugins that are available only with the commercial license of MySQL, the license is set to PROPRIETARY. If you need to add more license types, add them to the file, increment the value, and provide a text string to identify it in the plugin views.

The mechanism used by the MySQL Plugin API to communicate with the server is the st_mysql_structure, also defined in the /include/mysql/plugin.h file. Listing 9-2 shows the definition of the st_mysql_structure.

Listing 9-2. The st_mysql_plugin Structure in plugin.h

/*
Plugin description structure.
*/

struct st_mysql_plugin
{
int type; /* the plugin type (a MYSQL_XXX_PLUGIN value) */
void *info; /* pointer to type-specific plugin descriptor */
const char *name; /* plugin name */
const char *author; /* plugin author (for I_S.PLUGINS) */
const char *descr; /* general descriptive text (for I_S.PLUGINS) */
int license; /* the plugin license (PLUGIN_LICENSE_XXX) */
int (*init)(MYSQL_PLUGIN); /* the function to invoke when plugin is loaded */
int (*deinit)(MYSQL_PLUGIN);/* the function to invoke when plugin is unloaded */
unsigned int version; /* plugin version (for I_S.PLUGINS) */
struct st_mysql_show_var *status_vars;
struct st_mysql_sys_var **system_vars;
void * __reserved1; /* reserved for dependency checking */
unsigned long flags; /* flags for plugin */
};

The first six attributes in the structure contain metadata information about the plugin, including the type, description, name, author information, and license information. The next two attributes are function pointers to functions for loading and unloading the plugin. Following that are structures to contain status and system variables defined for the plugin. Last, there is an attribute for setting flags for communicating the plugin capabilities to the server.

Of special note is the info attribute. This is a pointer to a structure dedicated to each type of plugin. These are defined in header files in /include/mysql with the name plugin_* where * represents the plugin type. For example, the plugin_auth.h file contains the structure definition for the authentication plugin type.

The structure defined is named for the plugin as well. Each structure contains attributes and function pointers for specific methods for each plugin type. In this way, the server can successfully navigate and call the specific methods for each plugin type. The following shows the st_mysql_auth structure from the plugin_auth.h file.

/**
Server authentication plugin descriptor
*/
struct st_mysql_auth
{
int interface_version; /** version plugin uses */
/**
A plugin that a client must use for authentication with this server
plugin. Can be NULL to mean "any plugin".
*/
const char *client_auth_plugin;
/**
Function provided by the plugin which should perform authentication (using
the vio functions if necessary) and return 0 if successful. The plugin can
also fill the info.authenticated_as field if a different username should be
used for authorization.
*/
int (*authenticate_user)(MYSQL_PLUGIN_VIO *vio, MYSQL_SERVER_AUTH_INFO *info);
};

A special version number located in the file is unique for each plugin type. It defines the version number for the plugin type and is used to help identify and confirm architecture compatibility with the server when installed.

#define MYSQL_AUTHENTICATION_INTERFACE_VERSION 0x0100

PLUGINS AND VERSION NUMBERS

The st_mysql_plugin structure contains a version attribute. This is not used directly by the server other than to display it in the plugin views. There are two other version numbers that we should know. The first is PLUGIN_LIBRARY_VERSION, the version number set by the server that indicates the version of the plugin API. This permits the server to know if a plugin has compatible architecture. The second, PLUGIN_VERSION_TYPE, is specific to each plugin type. We can see these in /library/mysql/plugin.h as:

#define MYSQL_PLUGIN_INTERFACE_VERSION 0x0104

The value for the 5.6.6 server is 1.4. You can see this in the output of the INFORMATION_SCHEMA.plugins view above.

#define MYSQL_DAEMON_INTERFACE_VERSION (MYSQL_VERSION_ID << 8)

The above shows the specific plugin type for the daemon_example. In this case, the version of the server is placed in the higher-order bytes to help further identify the plugin. For server version 5.6.6, this value would be calculated as 50606.0. You can see this in the output of the INFORMATION_SCHEMA.plugins view above.

Note As we see above, most plugin types have specific values. The daemon_example, being an early example, has version number 0.

To create a plugin, first create a new folder in the /plugin folder named something easily associated with your plugin. Place in this folder, at a minimum, a source file containing an implementation of the st_mysql_plugin structure along with a specific implementation for the information structure associated with the plugin type. You should populate the plugin structure with the correct metadata, implement the methods for initialization and deinitialization, and implement the specific methods for the plugin type.

As an alternative, you could create a folder outside of the source tree, compile it, and link it with the server libraries. Advanced developers may want to explore this option if they desire to keep the plugin code separate from the server source code.

You would also create an ini file containing the information about the plugin as described in the section “Using MySQL Plugins.” If you have specific structures, variables, definitions, etc., for your plugin, you can create the appropriate files and place them in the same folder.

Now that we have seen the building blocks for creating a plugin, we will see how to compile the plugin and then begin creating a plugin of our own.

Compiling MySQL Plugins

You may be thinking that there is some arcane mechanism used to make your plugins compile. I have good news for you—there isn’t. The only thing you need is a CMakeList.txt file containing the cmake directives to compile your plugin. For simple plugins that have only a single plugin in the library, the content of the file is short. Listing 9-3 shows the complete contents for the example authentication plugin.

Listing 9-3. CMakeLists.txt for Authentication Plugin Example

# Copyright (c) 2010, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License as
# published by the Free Software Foundation; version 2 of the
# License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110–1301 USA

MYSQL_ADD_PLUGIN(auth dialog.c
MODULE_ONLY)

MYSQL_ADD_PLUGIN(auth_test_plugin test_plugin.c
MODULE_ONLY)

MYSQL_ADD_PLUGIN(qa_auth_interface qa_auth_interface.c
MODULE_ONLY)

MYSQL_ADD_PLUGIN(qa_auth_server qa_auth_server.c
MODULE_ONLY)

MYSQL_ADD_PLUGIN(qa_auth_client qa_auth_client.c
MODULE_ONLY)

CHECK_CXX_SOURCE_COMPILES(
"#define _GNU_SOURCE
#include <sys/socket.h>
int main() {
struct ucred cred;
getsockopt(0, SOL_SOCKET, SO_PEERCRED, &cred, 0);
}" HAVE_PEERCRED)

IF(HAVE_PEERCRED)
MYSQL_ADD_PLUGIN(auth_socket auth_socket.c
MODULE_ONLY)
ENDIF()

This example has five plugins defined. For each, the MYSQL_ADD_PLUGIN directive is used to define the plugin name and associate it with a source file. At the top of the file there is defined a module that is not associated with a plugin. This is how you would specify additional source files to compile if your plugin code were to use special methods, functions, or classes that you defined in other source files.

Now that we know what the building blocks are for building a plugin, where to find plugin-type-specific structures and definitions, and how to build a plugin, we can build a new plugin.

In the following sections, I show you how to build an authentication plugin that uses a hardware device to further secure your server by restricting login to users who must possess a special key card as well as a personal identification code (PIN).

The RFID Authentication Plugin

To illustrate how to create a MySQL plugin, I show you how to create an authentication plugin, because it is likely one of the most useful, and also one of the areas that developers seeking to customize their MySQL installations may want to create. I chose to make the project more interesting by introducing a hardware device to make the solution more secure than a typical user-name and password pair. In this case, the hardware device reads a special identification card that an administrator would give to a user that contains a unique number read only by the hardware device. This provides one-level-greater security than a simple password.²

The keycard device I chose to use is a radio frequency identification card (RFID).³ A RFID tag is typically a credit-car- sized plastic card, label, or something similar, that contains a special antenna, typically in the form of a coil, thin wire, or foil layer that is “tuned” to a specific frequency, so that when the reader emits a radio signal, the tag can use the electromagnet energy to transmit a nonvolatile message embedded in the embedded coil, which is then converted to an alphanumeric string. This form of RFID tag is a passive device, because it contains no battery. RFID systems with a need for greater range or power typically include a battery in the RFID tag itself. These are called active tags.

Tags and readers must be tuned to the same frequency. There are many RFID tag manufacturers and numerous frequencies. Thus, when choosing to incorporate a RFID system into your project, ensure that you purchase tags that are tuned to the same frequency as the reader. I recommend purchasing a kit that includes both the reader and several tags to be sure you avoid issues with compatibility.

For this project, we will use that string as part of the authentication mechanism. If we also include a prompt for the user to remember a PIN it further secures the solution because no one other than the intended user can use the card to login (unless, of course, they shared their PIN but in that case you’ve got a more serious problem).

The solution is more secure because instead of relying only on something the user must know, such as a password, the user must also have a physical item that they must present to complete the authentication. Thus, the solution provides two of the three elements considered to be very secure authentication. The third element is a biometric such as a finger or palm print that further identifies the user.

In the next section, I will describe how this authentication mechanism works. As you will see, it involves a client software component, a client hardware component, and a server software component. The software component is a special authentication plugin.

Concept of Operations

This project uses a RFID reader and a tag or keycard in place of the traditional user password. The setup on the server side involves using a variant of the CREATE USER command to create the user and associate with her account the RFID authentication plugin. The user is also assigned or allowed to choose a special personal identification number (PIN) using a short numeric string (I use 4 digits like most banks and credit cards). We use the RFID code with the PIN concatenated to form the passcode for that user.

When the user desires to login to the server, she starts her mysql client and is then prompted to swipe her card and once read correctly asked to enter her PIN. This information is then transmitted to the server to validate the combined code and PIN. Once validated, the user is logged into the server and the client proceeds. If the codes do not match, the user is given the appropriate error message indicating that her login attempt has failed.

If this sounds familiar to some of you, it isn’t an accident. I have used several similar systems to gain access to resources I was assigned. Not only are these systems more secure, they are also easier for the user because, unless they loose their keycard,⁴ she only has to remember a short PIN number.

Now that you are familiar with how the system operates, lets see how to build the system.

RFID Module

The first component you will need is a RFID reader (module). I chose to purchase a starter kit that contains a RFID tag reader that reads 125kHz tags and three tags (keycards). I am a Maker, so I often turn to electronics vendors who cater to the Maker community. One is SparkFun Electronics (http://www.sparkfun.com).

Note If you have purchased a different RFID system, follow the vendor’s setup guide, but read the following in case your system is similar.

SparkFun’s RFID starter kit (item# RTL-09875 $49.95 USD) was a great choice because it is USB based and therefore will work on all modern platforms. It is relatively inexpensive and exposes pins on the module that permit you to explore the hardware features of the RFID module should you want to make a more complex solution. While it does not contain a case (more on this later), it does provide a no-solder solution complete with an audible read chime. Last, the kit includes three keycards with unique RFID codes.

Figure 9-1 shows the RFID Starter Kit in its retail package. It contains a RFID module, the module board, and three keycards. You will have to supply your own USB to mini-USB cable (also available from SparkFun). Figure 9-2 shows a detail of the module board itself with the extra pins for developers (show to the right). Figure 9-3 shows a sample keycard included with the kit.

I recommend reading through this chapter and following along with the walk through of the code. Once you have ordered your own RFID kit, you can return to the chapter and complete the example.

Figure 9-1. RFID Start Kit from SparkFun Electronics

Figure 9-2. RFID module board

Figure 9-3. Keycard

One reason I like to buy from electronics vendors who cater to hobbyists and professionals alike is that they typically include a wealth of information about their products. Their Web sites have everything from datasheets (detailed specifications including everything an electronics professional would need to use the component in a project) and schematics to links to example projects and, in some cases, tutorials and quick-start guides.

SparkFun is an excellent vendor in this regard. For example, the RFID Starter Kit contains links to the datasheet, schematics, Eagle files for creating your own module board, and a quick-start guide to using the kit (http://www.sparkfun.com/tutorials/243).

I will not reproduce the quick-start guide in this book, but I will walk you through the things you need to do to use the RFID module for this example project in the following sections. I recommend reading the quick-start guide once you’ve read through this chapter to see an alternative presentation of the setup.

Note The quick-start guide is written for use with Windows, so if you use Windows, you may find the guide more useful than if you use another platform.

Installing a Driver

If you want to use the RFID module to read the codes via a serial interface⁵ like a modem reads from a COM port, you will need a special driver called an FTDI chip driver. Fortunately, the folks at SparkFun included a link for that too(http://www.ftdichip.com/FTDrivers.htm).

This driver is required for most platforms so that existing software can read from the device via those standardized COM ports. Once the driver is installed and the RFID module is connected, the driver will map the USB port to a COM port (Windows) or to a tty device (Linux and Mac OS X). You can discover the USB device in Linux and Mac OS X platforms by listing the contents of the /dev folder as shown below.

Chucks-iMac:∼ cbell$ ls /dev/tty.usb*
/dev/tty.usbserial-A501D94V

In the above output, we see a single device named so that it gives you a clue as to what it is. The FTDI driver will take care of interfacing the USB port with the standard communication-port protocols.

On Windows, you can discover the COM port assigned by opening the device manager, expanding the tree for COM and LPT ports, right-clicking on a port, and then choosing the advanced-settings dialog. Not only can you see the COM port assigned, you can also change it if you need to.Figure 9-4 shows an example of this dialog.

Figure 9-4. COM Port advanced settings on Windows

Tip I found it necessary to change the COM port from COM13 to COM1 on my Windows laptop. The RFID module was not working with any terminal client on Windows until I changed the mapping. Once changed, it worked flawlessly in a terminal client as well as the authentication plugin.

Once you have the driver installed and the module connected via the USB cable, open a terminal client and change the settings to connect to the COM port. The SparkFun RFID Starter Kit uses a configuration of 9600,8,N,1 for baud rate, bits, parity, and stop bits. This is a typical default for most terminal clients. If your client has a connect button or switch, click that now, and then try swiping a card. If everything works, you should hear a loud beep from the RFID module indicating that it has read the keycard, and you should see a string of 12 characters or more appear in the terminal client. If it doesn’t, go back and diagnose the settings for the COM port, your USB port, and the terminal client.

Discovering the Card Identification Numbers

If you looked at the keycards carefully (perhaps trying to see the antenna), you may have noticed that they do not have any codes written on them. So how do you find out what the codes are? If you setup your RFID module as described in the previous section, you would have seen the code for that keycard.

Thus, you will need to read each keycard and note the code returned. Take a moment now to discover all of the codes and make a mark on each card so that you can discover (recall) the code associated with the keycard. Figure 9-5 shows an example of a terminal client reading the code from a connected RFID module.

Figure 9-5. Terminal client reading RFID code

Depending on your terminal clients settings (some have a hexadecimal view option), you may see several extra characters that may appear as dots or other strange symbols. These are the control codes sent by the RFID module and for our uses they can be safely ignore. What you are looking for are the 12 characters that represent the RFID code.

Now that you know the codes associated with the keycards, let’s take a short detour and talk about how to make that bare printed circuit board (the RFID module board) a bit more secure, rugged, and user friendly.

Securing the RFID Module

Aside from the lack of protection for the RFID reader itself, a criticism of a solution such as this could be the lack of security for the RFID reader itself. As you have seen, it would be very easy for a savvy reader to pluck the reader out of its USB tether and use it on another computer to discover his or others’ keycard numbers.

Fortunately, this will get the errand user only so far. He would also have to discover another user’s PIN. In this way, securing the RFID reader itself may be considered a lesser concern. If you are concerned about securing the reader, I offer some possible solutions.

If you are experienced with hardware development or electronics, you could incorporate the RFID module into the user’s computer case. One possible location is behind an empty drive bay. Many PC manufacturers have additional USB connectors on the motherboard, and some vendors do not connect these additional ports. In some cases, the motherboard may have dedicated internal USB connections. If one such is available, you could route the RFID readers’ USB cable to the internal port. Finally, you can use special security locks to secure the case from tampering.

I chose to use a small project case from Radio Shack (item# 270–1801 $3.99) to mount the RFID module. Figure 9-6 shows the project case. I began by drilling a small hole in one end of the case so that the read tone would not be stifled (it won’t be nearly as loud), and I cut a slot in the other end to allow the USB cable to fit snuggly.

Figure 9-6. RFID case unmodified

I then used a piece of double-sided tape to secure the RFID module to the metal lid. I oriented the module so that the reader would face upward and the plastic case would sit upside down (lid down) on the desk. I then closed the case with the provided screws, placing a nonskip, self-adhesive foot to conceal each screw. Figure 9-7 shows the case unassembled, and Figure 9-8 shows the completed solution. Let’s call the completed unit simply the RFID reader.

Figure 9-7. Assembling the RFID reader unit

Figure 9-8. Assembled RFID reader

Now that we have a working RFID reader, let’s dive into the code for making this work with MySQL. In the next section, I explain how authentication plugins are constructed and show you the details for constructing an authentication plugin that uses the RFID reader to validate user logins.

Architecture of Authentication Plugins

Now that we have seen how the solution will work, and how to configure the RFID reader and discover the key card identification strings, let’s look at the composition and architecture of an authentication plugin.

Authentication plugins contain two components: a client-side plugin and a server-side plugin. For convenience, both of these can reside in the same code module.

As with all plugins, you must configure the plugin on the server before you can use it. The procedure described earlier in this chapter and in Chapter 7 is the same for authentication plugins. Specifically, you must place the compiled library in the folder designated by the plugin-dirvariable and use the INSTALL PLUGIN command to install the plugin. The following is an example command to install the plugin we are about to build on Linux.

INSTALL PLUGIN rfid_auth SONAME 'rfid_auth.so';

To associate a user with an authentication plugin, use the IDENTIFIED WITH clause of the CREATE USER command (see below). This tells the server to replace normal MySQL authentication with a request to the client to initiate the client-side component of the plugin specified.

CREATE USER 'test'@'localhost' IDENTIFIED WITH rfid_auth AS 'XXXXXXXPPPPP';

In the two example commands above, I refer to the plugin as rfid_auth, the name I have chosen to name the RFID authentication plugin. You will need to have the same consistency for any authentication plugin you may wish to create.

Notice also the AS clause. This clause allows you to specify a phrase that the server-side authentication plugin can use to help identify the user. For the purposes of illustration, brevity, and ease to develop, I chose to use this string to store the user’s keycard code appended with her PIN. While this is stored in the mysql.user table as clear text, it is still safe, because most users will not have read access to this table. I offer some more secure ways of storing this value in a later section. Now let’s turn our attention to how an authentication plugin works.

How Does an Authentication Plugin Work?

When a user is associated with an authentication plugin, and the user attempts to connect to the server, the server will request a packet from the server that contains a response that will be used by the server to complete the validation. This mechanism mirrors the challenge-and-response sequence of the traditional MySQL server-authentication protocol.

In this case, the client is designed to attempt to load the corresponding client-side plugin by the same name as the server-side plugin. The client knows this because the name of the plugin is returned in a special area of the packet sent from the server. In this way, we are assured the authentication-plugin segments (server and client) communicate only to each other.

You may be wondering, “How can this work? Wouldn’t the client need to know how to load the plugin?” The answer to the second question is, “Yes.” The client must be capable of loading client-side plugins. Thus, you cannot use an older version of the mysql client application to log into a server via a user account that is associated with a server-side authentication plugin.

The MySQL client applications will attempt to load the plugin from the --plugin-dir specified in the MySQL configuration file. You can also specify its location by providing the --plugin-dir option like:

cbell@ubuntu $ ../client/mysql -utest -h 127.0.0.1 --port=13000 --plugin-dir=../lib/plugin

Now that we have an idea for how the authentication plugin works, let’s take a moment to see how each is constructed.

Creating an Authentication Plugin

To create an authentication plugin, you need to create the following three files.

· CMakeLists.txt – cmake configuration file

· rfid_auth.cc – source file

· rfid_auth.ini – plugin ini file (described above and used by the mysql_plugin client application)

That’s it! Easy, eh? Go ahead and create the folder now.

Building the RFID Authentication Plugin

Let’s begin our coding effort with the CMakeLists.txt file. Open a text editor of your choice and enter the directives shown in Listing 9-4. The first line calls a macro that set ups up everything needed to correctly compile a MySQL plugin (hence the name). The macro takes as parameters the name of the plugin, the name of the source file(s), and any special directives. In this case, we use MODULE_ONLY⁶ to build the module but not to link it to the server and MODULE_OUTPUT_NAME to set the name of the compiled plugin.

Listing 9-4. CMakeLists.txt File

# cmake configuration file for the RFID Authentication Plugin

MYSQL_ADD_PLUGIN(rfid_auth rfid_auth.cc
MODULE_ONLY MODULE_OUTPUT_NAME "rfid_auth")

INSTALL(FILES rfid_auth.ini DESTINATION ${INSTALL_PLUGINDIR})

This is another example of the tireless efforts of the Oracle MySQL engineers to make the server code more modular and easier to extend via the plugin interface.

Now we are ready to start coding the solution. Open your favorite code editor, start a new file, and name it rfid_auth.cc. Place it in the /plugin/rfid_auth folder. Rather than list the entire contents of the rfid_auth.cc file, I will walk through the code a portion at a time. I start with the include file section, then describe and list the client-side plugin code, and later describe and list the server-side plugin code. All of the code in Listings 9-5 through 9-10 should be placed in the same source file.

Include Files and Definitions

First, list all include files and any definitions you want to make for the code. Listing 9-5 shows the include files needed for the RFID authentication plugin. These are the include files needed for both plugins.

Listing 9-5. Include and Definition Code

#include <my_global.h>
#include <mysql/plugin_auth.h>
#include <mysql/client_plugin.h>
#include <string.h>
#include <stdio.h>
#include <stdlib.h>
#include <stdio.h>
#include <fcntl.h>
#include <errno.h>
#include <mysql.h>

#ifndef __WIN__

#include <unistd.h>
#include <pwd.h>

#else

#include <windows.h>
#include <conio.h>
#include <iostream>
#include <string>
using namespace std;

/*
Get password with no echo.
*/
char *getpass(const char *prompt)
{
string pass ="";
char ch;
cout << prompt;
ch = _getch();
while(ch != 13) //character 13 is enter key
{
pass.push_back(ch);
ch = _getch();
}
return (char *)pass.c_str();
}

#endif

#define MAX_RFID_CODE 12
#define MAX_BUFFER 255
#define MAX_PIN 16

Notice that there is a conditional compilation statement. This is because the Windows platform has different mechanisms for reading from serial ports. Also, the Windows platform does not have a native getpass() method.

Now that the preamble for the source file is done, let’s see how the code looks for the client-side plugin.

Client-side Plugin

A client-side authentication plugin is constructed using a specific structure that resembles the st_mysql_plugin structure described above. Fortunately, there are macros that you can use to simplify the creation.

The client-side plugin is responsible for sending the user’s credentials to the server for validation. For the RFID-authentication plugin, this means prompting the user to swipe her card, reading the keycard, ask the user to enter her PIN, and then sending the concatenated RFID code and PIN to the server.

Let’s start by looking at the code to read the keycode. Once again, we need to use a conditional compile, because the code to read the COM port on Windows is very different from that of Linux and Mac OS X. Listing 9-6 shows the code for reading the RFID code from the RFID reader.

Listing 9-6. Reading RFID Code

/*
* Read a RFID code from the serial port.
*/
#ifndef __WIN__
unsigned char *get_rfid_code(char *port)
{
int fd;
unsigned char *rfid_code= NULL;
int nbytes;
unsigned char raw_buff[MAX_BUFFER];
unsigned char *bufptr = NULL;

fd = open(port, O_RDWR | O_NOCTTY | O_NDELAY);
if (fd == −1)
{
printf("Unable to open port: %s.\n", port);
return NULL;
}
else
fcntl(fd, F_SETFL, 0);

bufptr = raw_buff;
while ((nbytes = read(fd, bufptr, raw_buff + sizeof(raw_buff) - bufptr - 1)) > 0)
{
bufptr += nbytes;
if (bufptr[−1] == '\n' || bufptr[−2] == '\n' || bufptr[−3] == '\n' ||
bufptr[−1] == '\r' || bufptr[−2] == '\r' || bufptr[−3] == '\r' ||
bufptr[−1] == 0x03 || bufptr[−2] == 0x03 || bufptr[−3] == 0x03)
break;
}
*bufptr = '\0';

rfid_code = (unsigned char *)strdup((char *)raw_buff);
return rfid_code;
}

#else

unsigned char *get_rfid_code(char *port)
{
HANDLE com_port;
DWORD nbytes;
unsigned char raw_buff[MAX_BUFFER];
unsigned char *rfid_code= NULL;

/* Open the port specified. */
com_port = CreateFile(port, GENERIC_READ, 0, 0, OPEN_EXISTING,
FILE_ATTRIBUTE_NORMAL, 0);
if (com_port == INVALID_HANDLE_VALUE)
{
int error = GetLastError();
if (error == ERROR_FILE_NOT_FOUND)
{
printf("Unable to open port: %s.\n", port);
return NULL;
}
printf("Error opening port: %s:%d.\n", port, error);
return NULL;
}

/* Configure the port. */
DCB com_config = {0};
com_config.DCBlength = sizeof(com_config);
if (!GetCommState(com_port, &com_config))
{
printf("Unable to get port state.\n");
return NULL;
}
com_config.BaudRate = CBR_9600;
com_config.ByteSize = 8;
com_config.Parity = NOPARITY;
com_config.StopBits = ONESTOPBIT;
if (!SetCommState(com_port, &com_config))
{
printf("Unable to set port state.\n");
return NULL;
}

/* Set timeouts. */
COMMTIMEOUTS timeouts = {0};
timeouts.ReadIntervalTimeout=50;
timeouts.ReadTotalTimeoutConstant=50;
timeouts.ReadTotalTimeoutMultiplier=10;
if (!SetCommTimeouts(com_port, &timeouts))
{
printf("Cannot set timeouts for port.\n");
return NULL;
}

/* Read from the port. */
if (!ReadFile(com_port, raw_buff, MAX_BUFFER, &nbytes, NULL))
{
printf("Unable to read from the port.\n");
return NULL;
}

/* Close the port. */
CloseHandle(com_port);

rfid_code = (unsigned char *)strdup((char *)raw_buff);
return rfid_code;
}

#endif /* __WIN__ */

I leave the details of the Windows portion to those familiar with Windows programming, because this code is a well-proven and frequently duplicated section of code.

Note The non-Windows code is easy to write, but it has a particularly interesting wrinkle. In testing the RFID reader on several platforms, I discovered that the RFID code returned from the reader can contain a number of different patterns of control codes. Thus, I had to write the code to take into consideration all of the permutations I encountered. Your own experience with your platform may result in similar observations.

Now, let’s turn our attention to the core method of this plugin. Listing 9-7 shows the code used to control the client-side plugin. This method is mapped to the client-side plugin structure (described below) and called when the client detects that the server is requesting data from the rfid_auth plugin.

Listing 9-7. Sending RFID Code to the Server

static int rfid_send(MYSQL_PLUGIN_VIO *vio, st_mysql *mysql)
{
char *port= 0;
char pass[MAX_PIN];
int len, res;
unsigned char buffer[MAX_BUFFER];
unsigned char *raw_buff= NULL;
int start= 0;

/* Get the port to open. */
port= getenv("MYSQL_RFID_PORT");
if (!port)
{
printf("Environment variable not set.\n");
return CR_ERROR;
}

printf("Please swipe your card now...\n");

raw_buff = get_rfid_code(port);
if (raw_buff == NULL)
{
printf("Cannot read RFID code.\n");
return CR_ERROR;
}
len = strlen((char *)raw_buff);

// Strip off leading extra bytes.
for (int j= 0; j < 2; j++)
if (raw_buff[j] == 0x02 || raw_buff[j] == 0x03)
start++;

strncpy((char *)buffer, (char *)raw_buff+start, len-start);
len = strlen((char *)buffer);
/* Check for valid read. */
if (len >= MAX_RFID_CODE)
{
// Strip off extra bytes at end (CR, LF, etc.)
buffer[MAX_RFID_CODE] = '\0';
len = MAX_RFID_CODE;
}
else
{
printf("RFID code length error. Please try again.\n");
return CR_ERROR;
}

strncpy(pass, getpass("Please enter your PIN: "), sizeof(pass));
strcat((char *)buffer, pass);
len = strlen((char *)buffer);

res= vio->write_packet(vio, buffer, len);

return res ? CR_ERROR : CR_OK;
}

The above method begins with checking which port to use to read from the RFID reader. I use an environment variable named MYSQL_RFID_PORT for the user to specify the text string for the port to open. Examples include COM1, COM2, /dev/ttyUSB0, etc. There may be other, more elegant ways to specify the port, but this is the easiest to code and the easiest to deploy (simply add it to the user’s login script).

After the environment variable containing the port is read, the method prompts the user to swipe her keycard and calls the get_rfid_code() method defined above to read the RFID code. Some simple error handling is included to ensure that the code was read correctly (all 12 characters are available). Once the code is read, the method prompts the user for her PIN and then reads the PIN from standard input (keyboard).

This information is then concatenated and sent to the server using the vio class method write_packet(). That’s it! The client-side authentication plugin has turned control over to the server-side plugin to validate the string. If it is validated, the write_packet() returns CR_OKotherwise it returns CR_ERROR. If the server-side validation succeeds, the client application takes over to complete the handshake with the server and completes the login.

The last item to discuss and code is the client-side plugin definition structure. There are macros that make this definition easier. Listing 9-8 shows the use of the prefix macro mysql_declare_client_plugin and the mysql_end_client_plugin postfix macro. The contents of the structure begins with the name of the plugin, the author, a description of the plugin, the version array, and the license. Following this are function pointers to the MySQL API (internal use only), initialization, de-initialization, and option-handling helper methods. Since the RFID authentication plugin is rather simple, we don’t use any of these methods, and therefore, we set them to NULL. The last entry is the function pointer to the method called when the server requests the validation data from the client. As you can see, this is the rfid_send() method described above.

Listing 9-8. Defining the Client-side Plugin

mysql_declare_client_plugin(AUTHENTICATION)
"rfid_auth",
"Chuck Bell",
"RFID Authentication Plugin - Client",
{0, 0, 1},
"GPL",
NULL,
NULL,
NULL,
NULL,
rfid_send
mysql_end_client_plugin;

Caution The name you assign to the client-side plugin (the first entry in the structure) must match the name of the server-side plugin (see below). If it does not match, you will encounter some unusual error messages from the client or the server.

As you can see, the client-side plugin isn’t that complicated (aside from reading from the serial port). Now let’s look at the server-side plugin code.

Server-side Plugin

The server-side plugin code is simpler. This is because it only has to validate the RFID code received from the client. Listing 9-9 shows the validation code.

Listing 9-9. Validating the RFID Code

/*
* Server-side plugin
*/
static int rfid_auth_validate(MYSQL_PLUGIN_VIO *vio,
MYSQL_SERVER_AUTH_INFO *info)
{
unsigned char *pkt;
int pkt_len, err= CR_OK;

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

info->password_used= PASSWORD_USED_YES;

if (strcmp((const char *) pkt, info->auth_string))
return CR_ERROR;

return err;
}

The above code only has to read a packet from the client via the vio->read_packet() class method and match it to the code stored in the mysql.user table.

The server-side definition also uses macros to define the structure. It requires the definition of two structures, however. We have a similar structure for defining the plugin, but there is also a special structure, the plugin handler structure, to store the version, text string descriptor, and a function pointer to the server-side validation code (also called the authentication method in some documentation). The handler structure is also used to present the information about the plugin via the plugin utility commands. Listing 9-10 shows both structures used to define the server-side plugin.

Listing 9-10. Defining the Server-side Plugin

static struct st_mysql_auth rfid_auth_handler=
{
MYSQL_AUTHENTICATION_INTERFACE_VERSION,
"rfid_auth",
rfid_auth_validate
};

mysql_declare_plugin(rfid_auth_plugin)
{
MYSQL_AUTHENTICATION_PLUGIN,
&rfid_auth_handler,
"rfid_auth",
"Chuck Bell",
"RFID Authentication Plugin - Server",
PLUGIN_LICENSE_GPL,
NULL,
NULL,
0x0100,
NULL,
NULL,
NULL,
0,
}
mysql_declare_plugin_end;

The second structure is the same structure used to define any server plugin. The macros mysql_declare_plugin and mysql_declare_plugin_end help simplify the code. As you can see, it contains the type of the plugin, the address to the handler structure, name of the plugin, the author, the description string, the type of license, the function pointers to initialization and deinitialization, the version (in hex), function pointers to status variables, system variables, an internal use-only position, and finally, a set of flags used to further describe the plugin’s capabilities. See the online reference manual for more details about this structure.

Caution The name you assign to the server-side plugin (the first entry in the structure) must match the name of the client-side plugin (see above). If it does not, you will encounter some unusual error messages from the client or the server.

Now that we have all of the code for the RFID authentication plugin, we can compile the plugin and test it. First, let’s look at the last file—the rfid_auth.ini file.

The rfid_auth.ini File

To complete our plugin, we also need to create the initialization file for use with the mysql_plugin client application. If you do not plan to bundle the plugin with a special build of the server or to initiate the make install command from the source tree, you need not create this file. Listing 9-11shows the contents of the file.

Listing 9-11. The rfid_auth.ini file

#
# Plugin configuration file. Place the following on a separate line:
#
# library binary file name (without .so or .dll)
# component_name
# [component_name] - additional components in plugin
#
librfid_auth
rfid_auth

Now that the source files are complete, lets compile the plugin.

Compiling the Plugin

Compiling the plugin is even easier. The base cmake files for the server code contain all the macros needed to ensure that any plugin placed in the /plugin folder that has the correctly formatted CMakeLists.txt file will be automatically configured when the following commands are issued from the root of the source tree.

cmake .
make

That’s right. No special, complicated, or convoluted commands are needed. Just create a folder, say /plugin/rfid_auth, and place your files in it. When ready to compile, navigate to the root of the tree and enter the commands above.

Go ahead and compile the plugin, and then copy it to the plugin directory of your server. If you encounter errors, go back and fix those until the plugin code compiles without errors or warnings.

RFID Authentication in Action

Before you rush off to buy a RFID reader and start coding, let’s take a look at this example executing on a real server. Recall that once compiled, we need to copy the plugin (e.g. rfid_auth.so or rfid_auth.dll) to the location on the server that corresponds with the setting for --plugin-dir.

The server does not need to be restarted, but if you are attempting to copy an existing, installed plugin, you may encounter some unusual and potentially damaging behavior by the server. For example, on Windows, the server may crash, but on Ubuntu, the server is not affected.

For authentication plugins, the plugin must also be placed in a location that the clients can find (or specified via the --plugin-dir option for the client applications).

Once the plugin is in the correct locations, we must go to the server and install the plugin as shown below.

INSTALL PLUGIN rfid_auth SONAME 'rfid_auth.so';

The command should return without errors. You can verify that the plugin is loaded by issuing a query against the INFORMATION_SCHEMA.plugins view, as shown in Listing 9-12. Notice the name, type, description, author, and versions of the plugin. Compare these to those defined in the code above.

Listing 9-12. Verifying the Plugin is Installed

mysql> select * from information_schema.plugins where plugin_name like 'rfid%' \G
*************************** 1. row ***************************
PLUGIN_NAME: rfid_auth
PLUGIN_VERSION: 1.0
PLUGIN_STATUS: ACTIVE
PLUGIN_TYPE: AUTHENTICATION
PLUGIN_TYPE_VERSION: 1.0
PLUGIN_LIBRARY: rfid_auth.so
PLUGIN_LIBRARY_VERSION: 1.4
PLUGIN_AUTHOR: Chuck Bell
PLUGIN_DESCRIPTION: RFID Authentication Plugin - Server
PLUGIN_LICENSE: GPL
LOAD_OPTION: ON
1 row in set (0.00 sec)

Once we know that the plugin is installed, we can create users that are associated with the plugin. The following shows an example of creating a user to authenticate with the RFID authentication plugin.

CREATE USER 'test'@'localhost' IDENTIFIED WITH rfid_auth AS '51007BB754C91234';

Now we can go to the client and attempt to log in using the plugin. Listing 9-13 shows the user attempting to log in to the server. Notice the use of the --plugin-dir option to specify the location of the RFID authentication plugin. Notice also the prompts from the client-side plugin to swipe the keycard and to enter the PIN. When prompted to swipe my card, I simply placed the card on top of the RFID reader case until I heard it signal a correct read with a beep. The process took less than two seconds (I had my card on my desk ready to use,) and typing the PIN was a simple matter of typing a four-digit number.

Listing 9-13. Logging in with the RFID Authentication Plugin

cbell@ubuntu:$ ../client/mysql -utest -h 127.0.0.1 --port=13000 --plugin-dir=../lib/plugin
Please swipe your card now...
Please enter your PIN:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.6-m9 Source distribution

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql>

You may be wondering if only the mysql client application is client-side plugin enabled. The good news is that all of the supplied client applications are plugin enabled for use with authentication plugins. Listing 9-14 shows an example of the mysqladmin client application using the authentication plugin.

Listing 9-14. Logging in with the mysqladmin Client Application

Note Using the authentication plugin does not violate or circumvent the user-security layer in the MySQL server.

As you can see, using an authentication plugin changes the way that users log in to the server. In this example, I created a unique method of logging in to the server that requires the use of a special keycard and does not use passwords (PIN code excluded).

In the next section, I present some suggestions for improving and hardening the plugin for use in production environments or for creating your own professional RFID reader based authentication mechanism.

Further Work

If you find the RFID authentication plugin a worthwhile model for building your own, more-secure user-login mechanism, you can improve the solution in a number of ways. You could change the size of the PIN, use the validate_password plugin to help create more secure PINs, or add an additional hardware element, such as the MAC address of the client workstation. In this case, the MAC address would further restrict login to a user with a specific keycard and a matching PIN read from a specific machine.

Perhaps the best and most secure option is to use SHA1 or MD% algorithms to encrypt the keycode stored in the mysql.plugin table. The client-side plugin would have to use the same seed to form the encrypted string, so using the MySQL methods may be problematic. A simple hash or even a scramble, however, should be sufficient to protect the code from accidental discover. Even then, the use of the code is limited, since the infiltrator would have to construct a facsimile of the client-side plugin, and that, I hope you agree, is beyond the skills of the average user.

Another option is to use the RFID code itself as the seed and encrypt a known phrase. I would recommend the use of a random byte stream, so that someone reading the code—or if an extremely persistent snoop attempts to read the binary code for the plugin—will not discover the passcode in clear text. Either way, encrypting what is stored in the mysql.user table should be considered a vital requirement for production use.

Beyond securing the RFID reader itself, you could also incorporate a biometric element, such as a fingerprint reader. Implementing such a device may require a bit more programming that you may expect, but if you are after a highly secure solution, a biometric device will complete your quest.

Summary

You may be thinking, “Wow, that’s a lot to take in.” That may be true, but once you have worked with the code and seen it in action, I hope that you will see this as a good example of how to build a plugin for authentication that you can use as a boilerplate for your own authentication plugin.

In this chapter, I discussed the most import feature of the MySQL server—he ability to add new features without stopping or reconfiguring the server. You were introduced to the types of plugins available and how they form the basis for future expansion of server features. I then examined the architecture of MySQL plugins and how they are installed and uninstalled, as well as demonstrated how to create a plugin through the creation of an authentication plugin using a RFID reader.

The next chapter will explore the most complex type of plugin, a MySQL storage engine. You will see how to create your own storage engines. You should be impressed with the ease of extending the MySQL system to meet your needs. Just the embedded server library alone opens up a broad realm of possibilities. Add to that the ability to create your own storage engines and even (later) your own functions in MySQL, and it is easy to see why MySQL is the “world’s most popular open-source database.”

¹ UDFs predate the plugin architecture, first appearing in version 3.21.24. They have not been changed to use the new architecture.

² I will describe some modifications that make the example even more secure.

³ http://en.wikipedia.org/wiki/Radio-frequency_identification

⁴ While it is still possible to lose a keycard, having one avoids the much more frequent forgotten-password event.

⁵ While a USB port is by definition a serial connection, most people associate the older ports with nine or more pins as a “serial port.” It is this type of port to which I refer.

⁶ These directives are defined in the cmake/plugin.cmake file.