Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
System user authentication plugin
+4 Vote Up -0 Vote Down

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:

  • Make up a password and forget it
  • Use a non-existent host name or address
  • Use an authentication plugin that’s not loaded
  • 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.



    You must be logged in with a MySQL account to vote on Planet MySQL entries. More information on PlanetMySQL voting.

    Planet MySQL © 1995, 2016, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

    Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.