MySQL 5.5 brings in new ways to authenticate users

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.
Comments:

Hello, I have read the MySQL manual and cannot find information about setting up configuration files so that MySQL server can communicate with a Windwos Active Directory. I would be greatful if you could post notice about the next steps to set this up.

Posted by Joe P on Май 23, 2011 at 05:47 PM EEST #

@Joe P, The authentication API is just an API. It needs plugins that talk to various authentication backends.

Posted by guest on Май 25, 2011 at 04:55 AM EEST #

Hey Joe, I'm currently trying to hack out an Auth Module to do something similar in Windows. Once I fugure out what's going on, I'll update you, via this thread...

CT

Posted by CrazyTasty on Юли 08, 2011 at 08:18 AM EEST #

Hi, there are bad referenced link.

New PROXY privilege

http://www.blogger.com/:%20http://dev.mysql.com/doc/refman/5.5/en/privileges-provided.html

New external_user and proxy_user system variables
[ http://www.blogger.com/:%20http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html ]

New MYSQL_DEFAULT_AUTH and MYSQL_PLUGIN_DIR options for mysql_options()
[ http://www.blogger.com/:%20http://dev.mysql.com/doc/refman/5.5/en/mysql-options.html ]

I wish to continue the good work

Posted by Reader on Септември 16, 2011 at 04:59 AM EEST #

@ Reader : thanks for spotting this ! corrected.

Posted by Joro on Септември 16, 2011 at 06:24 AM EEST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Writing about the interesting features and oddities of the MySQL server and related code.

Search

Categories
Archives
« Април 2014
НдПнВтСрЧтПтСб
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today