Hi Joro, can you give us some background on yourself? What are you currently working on at MySQL?
A: I used to be an IT manager at a large Bulgarian bank. After that I went into software development, specifically database development. I originally joined MySQL as a member of the Optimizer Team. Now I am responsible for MySQL Security as well as MySQL architecture as it relates to security. I am also helping my team with their respective areas including Performance Schema, Partitioning, Character Sets and Client APIs.
Before we talk about the enhancements around the Pluggable Authentication API, can you tell us how MySQL has traditionally handled security?
A: MySQL handles security like most other databases. There is a way to define local users and grant privileges so they can access existing database objects. However, all authentication was done in local MySQL tables which has its benefits and shortcomings.
You can also provide encryption of the client server communication via SSL. MySQL is also able to understand certain aspects of SSL certification like subject name and checking the validity of a certificate.
Can you tell us about the latest enhancements around the Pluggable Authentication API?
A: The Authentication API is an abstraction layer between the MySQL Server and the authentication mechanisms. It provides a logical divide that allows for changes on one side of the interface without affecting the other. So now, instead of the MySQL Server being hard coded to always look at local tables, it can basically dynamically load a plugin and ask it if a certain user is allowed to log in and how exactly. People can plug in different authentication schemes.
The default built-in plugin implements the traditional authentication against the mysql.user table as was used prior to the implementation of pluggable authentication.
Can you tell us the benefits of external authentication using the Pluggable Authentication API?
A: Typically large companies with a lot of employees or users of their information systems, keep a list of users in some sort of centralized directory. When employees join the organizations or leave the organization they don't have to manually assign or remove an employee from each individual system. This is what MySQL was lacking. There was no way to address this kind of centralized registry. This is the main thing that the pluggable authentication was designed to solve. It allows MySQL to authenticate users from centralized directories.
What are the benefits of external authentication for the DBA?
A: It is more convenient because it creates less strain on the MySQL administrators by enabling them to only administer and control access to a central repository rather than manually having to go to each MySQL Server instance and make sure the information is consistent with other sources. It is less overhead.
What are some of the advantages of using an external authentication source over MySQL authentication?
A: Most organizations have centralized directories, with clean rules and processes. For example, organizations have rules for password expiration, weak passwords, etc. Now MySQL can leverage these capabilities.
Are there other benefits for DBAs and SysAdmins that make user administration easier.
A: There are a number of tools such as Active Directory GUI tools that work with those external directories because they have well defined APIs. DBAs can use those tools to be more productive. Chances are that this infrastructure is already deployed within an organization, so users can automatically log into MySQL applications without having to resupply their credentials time and time again. So it can be considered a Single Sign On implementation that is more convenient for users and DBAs.
What types of plugins could developers create using the Plugin Authentication API?
A: The Pluggable Authentication API is really simple. The MySQL Server asks the plugin: "Ok, here is the credential data from the connecting client, can this person login?" and gets a yes/no answer.
The possibilities are limitless. You can use things like simple HTTP server authentication; a simple page that requires HTTP authentication to check credentials. You can talk to an LDAP server, you can use the Windows Active Directory. There's an API that the server can use to obtain the user id of a client connecting to the server through a unix domain socket
Pluggable Authentication introduces the concept of "Proxy Users". Can you talk about that?
A: In the simplest scenario a local MySQL users table stores all the access rights for each user logging into the MySQL Server. It's a 1-to-1 mapping between the login names and the set of permissions that each user account in MySQL defines. Pluggable Authentication gives you the flexibility to do 1-to-many mapping. For example, you might have multiple accounts that all get the same basic set of rights to the MySQL database. That is what Proxy Users are for. Proxy Users are basically an alias. When you login to a mysql user account (either specific or wildcard account, e.g. ''@'') that has proxy rights to other local mysql user accounts and is defined to use a plugin to authenticate, the authentication plugin can ask the mysql server to impersonate some of the other local mysql user accounts it has proxy rights to. If you use a wildcard account you can map multiple login ids to one local mysql user. They will still have different login names, but they will re-use the set of privileges defined for the local user they're proxied to by the plugin.
In a typical proxy user setup you define several generic local mysql users that have sets of privileges corresponding to types of users : e.g. "dba", "data entry", "developer" etc. You also setup a wildcard account that uses plugin authentication and has proxy rights on these generic users. Now when real users like Joe, Fred or whomever login, the plugin can redirect them to one of the generic mysql users, e.g. Joe gets the access rights of a 'dba', Fred gets the 'developer' rights etc.
Are there other feature of the Pluggable Authentication API that you want to talk about?
A: Client authentication plugins are a really powerful feature for scenarios where you need additional information from the client. In the typical MySQL authentication scenario, when authenticating, the client sends a user name and a password digest to the server. This is enough to authenticate using the MySQL local tables.
But some authentication backends may require more (or totally different set of) data from the connecting client. e.g. socket authentication requires no data from the client at all, whereas HTTP authentication requires the password in clear text. Other backends may send generated challenge questions to the client to answer etc.
It's obvious that you need to be able to customize the client-server dialog to fully support pluggable authentication in these cases. MySQL does that through the client authentication plugin API. The server requests certain client authentication plugin by name and if the client has it, it just calls it and lets the client authentication plugin handle the dialogue with the server authentication plugin.
Of course this means that you may need to develop a client plugin that provides the data your server authentication plugin needs. The good news is that mysql provides a bundled set of client plugins that handle most common scenarios. And these client plugins work across all client types : Java, PHP etc.
Where can users find documentation?
A: Our documentation team created detailed documentation on Pluggable Authentication API: http://dev.mysql.com/doc/refman/5.5/en/pluggable-authentication.html
Do you have a blog that users can follow?
A: Yes, and I have created a blog post describing some of the scenarios that users can implement using Pluggable Authentication: http://blogs.oracle.com/mysql_joro/2011/01/mysql_55_brings_in_new_ways_to_authenticate_users.html
Joro, thanks for your time. Any final thoughts you want to share?
A: The ultimate goal is to simplify life for the DBA, by enabling MySQL to access external authentication sources.
Full article...
