Monday, November 29, 2010

MySQL 5.5 Authentication Goodies

MySQL 5.5 is currently in the Release Candidate phase and making good
progress on the way to being a Generally Available release. There
are many new features that will improve performance, make service
more robust, and generally make life better for DBAs. But since
5.5.7 was released for evaluation in October, there has not been a
lot of attention given to the changes in authentication.

To greatly simplify, MySQL has a table with a list of users and a
list of hosts from which those users are allowed access. So user
'jones' and the host they connect from are checked to make sure they
are allowed access. If they match, they can access the instance.

As of 5.5.7, MySQL authentication now supports pluggable
authentication and proxies. So now you can use PAM, Windows native
authentication, LDAP, or something similar to control user accounts.
Or use proxies for group of users. For example user 'smith' is in the
'programmer' group in the LDAP directory. But there is no entry in
the MySQL.user table for that user. The server will now use the
pluggable authentication to reference an outside authentication
service to determine the group. The connection will be established
with the USER() set to 'smith@host' and CURRENT_USER() set to
'programmer@%'. Please refer to for details.


Rob said...

Hi Dave,
First off, I applaud Oracle for working to get this change in. For those of use that run MySQL at large scale, managing grants is a night terror. From what I have read it does not appear that is a PAM plugin yet. Is this the case?

Also, this change being added in a RC is very worrisome in terms of stability. Hopefully the tree is not too badly destabilized.

/me crosses fingers


Rob Wultsch

Mark Callaghan said...

Are there more details about successful deployments for a high-throughput RDBMS? Would anyone use something like this for a MySQL Cluster deployment? I suppose if Cluster were used to run LDAP then one of my concerns might be addressed.

But maybe high-throughput workloads are not the target market?

My initial reaction to using external authentication services are:
1) login will be much slower
2) there will be more login failures from intermittent network glitches and failures of the authentication service.

Dave Stokes said...

@Rob & @Mark -- The pluggable authentication and proxies are still very new. I have worked in many environments were centralized authentication was mandated by corporate policies. And having the ability to authenticate against LDAP would have prevented some kludges in my past. I will try to document how these new tools can be used to make up for some of those transgressions in my past.

There has to be some overhead to go out to another service and return. Hopefully you can authenticate once per session to minimize this overhead.

Mark Callaghan said...

A white paper like that would help. I am sure there are many interesting scenarios where this can make account management much simpler. Especially when you want to immediately remove access for a user who might be crafty enough to restore a database backup. And if the MySQL privilege tables are used, then the stale backup has stale privs.

Georgi Kodinov said...

Yes, of course adding more network IO and more layers in general can slow things down. But the scheme is optional. If you want to use it, then you can. But it won't prevent you from sticking to the "native" 5.1-compatible authentication. You can even use a mix of the two in a single server.
I also think that modern authentication directories can minimize the usage of IO at login time by clever caching/broadcast mechanisms.
In fact : nothing prevents you from implementing one such in a plugin. You can e.g. start a listener thread in the server plugin, do the initial caching from the authentication server and subscribe to updates broadcasts (in Rendezvous style) should the authentication server supports it. The good thing about authentication plugins is that they're extremely flexible if used in a correct way.