System user authentication plugin

I’ve been working on revising my password policy scripts, and in the process, thought about the privileges required.  My first draft added tables to the mysql system database and leveraged the root@localhost account.  I’m looking to lock that down for the next iteration.  It’s easy to move the tables and procedures out of the mysql system database into a new password_policy database, but what to do about the use of the root account?

Ideally we would use an account that has the minimum privileges necessary to successfully execute the stored procedures.  But these aren’t trivial permissions:

  • SELECT from mysql.user table
  • EXECUTE for called stored procedures
  • SELECT, INSERT, UPDATE on tables in the new password_policy database

The user account would only be used as the DEFINER for the password policy stored procedures; it would never be something which an end user would use to connect.  If nobody should ever connect as the user account, how can we configure an account that refuses connections?  A few ways:

  1. Make up a password and forget it
  2. Use a non-existent host name or address
  3. Use an authentication plugin that’s not loaded
  4. Add REQUIRE CIPHER ‘non-existing-cipher’ to the account definition

These are ultimately all hacks of one kind or another or have limitations or weaknesses.  But the third option got me to thinking – why not have an authentication plugin that always refuses all connections?  How hard can that be?  The answer is, of course, “exceptionally easy.”  Here’s the code in full:


/*  Copyright (c) 2012, 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

    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    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 */


  system_user_auth authentication plugin.

  This plugin exists to support system user accounts, which
  cannot be accessed externally.  This is useful for privileged
  stored programs, views and events.  Such objects can be created
  with DEFINER = [sys account] SQL SECURITY DEFINER.

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

static int system_user_auth(MYSQL_PLUGIN_VIO *vio, MYSQL_SERVER_AUTH_INFO *info)
   return CR_ERROR;

static struct st_mysql_auth socket_auth_handler=

  "Todd Farmer",
  "System account authentication",

That’s about as basic of an authentication plugin as you can ever create – the only method returns CR_ERROR to indicate authentication failed.  Here’s a quick example of it solving the use case I had in mind:

mysql> INSTALL PLUGIN system_user_auth SONAME 'auth_system_plugin.dll';
Query OK, 0 rows affected (0.20 sec)

mysql> CREATE USER pwd_sys_user@localhost IDENTIFIED WITH system_user_auth;
Query OK, 0 rows affected (0.11 sec)

mysql> exit

D:\mysql>bin\mysql -upwd_sys_user -P3307
ERROR 1045 (28000): Access denied for user 'pwd_sys_user'@'localhost' (using password: NO)

D:\mysql>bin\mysql -upwd_sys_user -P3307 -ptest
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'pwd_sys_user'@'localhost' (using password: YES)

Confirming that one cannot connect using the account configured with the auth plugin.

Then as root (or another privileged user), we can create a view or stored program that executes as the system user account:

mysql> GRANT SELECT ON mysql.user TO pwd_sys_user@localhost;
Query OK, 0 rows affected (0.08 sec)

mysql> CREATE DEFINER=pwd_sys_user@localhost
-> VIEW test.v AS SELECT * FROM mysql.user;
Query OK, 0 rows affected (0.09 sec)

We can also create a limited-privilege normal user account:

mysql> CREATE USER test@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT ON test.v TO test@localhost;
Query OK, 0 rows affected (0.00 sec)

That user can then connect and query the view:

mysql> SELECT current_user();
| current_user() |
| test@localhost |
1 row in set (0.00 sec)

mysql> SELECT user, host, password FROM test.v LIMIT 1;
| User | Host      | Password |
| root | localhost |          |
1 row in set (0.00 sec)

mysql> SELECT * FROM mysql.user;
ERROR 1142 (42000): SELECT command denied to user 'test'@'localhost' for table 'user'

In the event that you actually need to log in as the user, you can do so with direct manipulation of the mysql.user table:

mysql> UPDATE mysql.user SET plugin = ''
-> WHERE user = 'pwd_sys_user';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

Query OK, 0 rows affected (0.00 sec)

mysql> exit

D:\7362718\mysql-5.6.9-win32>bin\mysql -upwd_sys_user -P3307
Welcome to the MySQL monitor. Commands end with ; or \g.

Direct manipulation of the mysql.user table isn’t recommended, but it’s currently the only way to change the authentication plugin associated with a given account short of dropping and recreating the user account.

Long term, I would love to see a more comprehensive, standard-friendly solution, such as ALTER USER ... [DISABLE|ENABLE] LOGIN.  But the above plugin can usefully solve the same problem within the infrastructure provided by MySQL 5.5 and 5.6.


2 thoughts on “System user authentication plugin

  1. How do I make a working auth_system_plugin.dll (or ?? ) on a Linux-server?

    Can you show me a syntax example?

    1. Hello Guus,

      Great timing on your question! We’ve actually included this plugin (renamed as mysql_no_login) in the just-released development milestone release of 5.7:

      This includes support for all platforms, and can be built from source using the documented process. For 5.6, you’ll want to look at the changes in plugin/auth/CMakeLists.txt, which really only added the following lines:

      MYSQL_ADD_PLUGIN(mysql_no_login mysql_no_login.c

      Take the mysql_no_login.c source from 5.7.5, add it to plugin/auth directory, remove one of the last NULL arguments in the initialization function (the API changes in 5.7), so that the file ends with:


      And you should be good on 5.6. We’re also contemplating including this in later 5.6 releases, if there are no objections.

      Hope that helps!


Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.