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
- CREATE USER to support use of ALTER USER … PASSWORD EXPIRE
- 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 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 */ /** @file 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= { MYSQL_AUTHENTICATION_INTERFACE_VERSION, 0, system_user_auth }; mysql_declare_plugin(socket_auth) { MYSQL_AUTHENTICATION_PLUGIN, &socket_auth_handler, "system_user_auth", "Todd Farmer", "System account authentication", PLUGIN_LICENSE_GPL, NULL, NULL, 0x0100, NULL, NULL, NULL } mysql_declare_plugin_end;
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 Bye 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 -> SQL SECURITY DEFINER -> 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 mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) mysql> exit Bye 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.