MySQL 5.5 brings in new ways to authenticate users
- by Georgi Kodinov
Ever wanted to use your server's OS for authenticating MySQL users ? Or the corporate LDAP repository ?
Unfortunately options like the above are plentiful nowadays. And providing hard-coded support for protocol X or service Y is not the best possible idea.
MySQL 5.5 has taken the step into the right direction by providing an infrastructure allowing one to make the server understand different authentication protocols by creating a set of simple plugins (one for the client and one for the server).
So now you can easily extend MySQL to search for and authenticate users in your favorite user directory.
In fact the API supplied is so versatile that we took the possibility to re-design the current "native" authentication mechanism into a built-in always-on plugin !
OK, let me give you an example:
Imagine we have a bunch of users defined in your OS, e.g. we have a user joro with his respective password. And we have a MySQL instance running on the same computer.
It would not be unexpected to need to let joro access and/or modify MySQL data.
The first step is to define him as a MySQL user. And there's a problem right there : MySQL's
CREATE USER joro@localhost IDENTIFIED BY 'joros_password'
statement needs a password. And this is a password in no way related to the password that joro have set up in the OS. What's worse : if joro changes his OS password this will in no way be reflected in MySQL. So he'll need to change his MySQL password in a separate step. Not very convenient, specially when you have a lot of users.
This is a laborious setup for joro's DBA as well : he'll have to disable his access in both MySQL and the OS should he decides that joro's out of the "nice" list.
Now mysql 5.5 to the rescue:
Imagine that the smart DBA has created a MySQL server plugin that will check if the name of the user logging in is a valid and enabled OS name and if the password supplied to the mysql client matches the OS and has called this plugin 'auth_os'.
Now all that's left to do is to define joro as a MySQL user that will be authenticated externally.
This is done by the following command :
CREATE USER 'joro'@'localhost' IDENTIFIED WITH 'auth_os';
Now joro can login to MySQL using his current OS password.
Note : joro is still a valid MySQL user, so you can grant privileges to him just like you would for all other users. What's better: you can have users that authenticate using different mechanisms in the same server. So you can e.g. safely experiment with external authentication for selected users while keeping your current user base operational.
What happens under the hood when joro logs in ?
The server will find out by the user definition that it needs to use a non-default authentication and will ask the client to "switch" to using the appropriate client-side plugin (if of course the client is not already using it). If the client can't do this (e.g. because it's an old client or doesn't have the necessary plugin available) the server will reject the login. Otherwise the server will let the server-side plugin decide (while possibly talking to the client side plugin and the OS user directory) if this is a valid login or not.
If it is the login process will continue as usual, while if it's not the login will get rejected.
There's a lot more that MySQL 5.5 can do for you than just the simple case above. Stay tuned for more advanced use cases like mapping groups of external users to a single MySQL user (so you won't have to have 1-to-1 mapping between your external user directory and your mysql user repository) or ways to control the process as a DBA.
Or you can simply skip ahead and read the relevant topics from MySQL's excellent online documentation. Or take a look at the example plugins in plugin/auth. Or take a look at the test suite in mysql-test/t/plugin_auth.test.
Changelog entry:
http://dev.mysql.com/doc/refman/5.5/en/news-5-5-7.html
Primary new sections:
Pluggable authentication
Proxy users
Client plugin C API functions
Revised sections:
New PROXY privilege
New proxies_priv grant table
Passwords might be external
New external_user and proxy_user system variables
New --default-auth and --plugin-dir mysql options
New MYSQL_DEFAULT_AUTH and MYSQL_PLUGIN_DIR options for mysql_options()
CREATE USER has IDENTIFIED WITH clause to specify auth plugin
GRANT has PROXY privilege, IDENTIFIED WITH clause to specify auth plugin
The data structure for writing client plugins
