From: Sergei Golubchik Date: May 19 2008 5:14pm Subject: Re: sql/password.c List-Archive: http://lists.mysql.com/internals/35589 Message-Id: <20080519171441.GB28179@janus.mylan> MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Hi! On May 18, Guus Leeuw jr wrote: > Hello, > > Sorry for the somewhat wide distribution, first of all. > (This message was originally cross-posted to contributions@stripped; > so please reply to them as well.) > > Background: > > I am implementing multiple websites using MySQL as a backend database. So > far, nothing new. However some of these websites will host applications that > are password protected and user-role authorized. Thus it would seem ideal if > we could somehow manage for the website password to be the same as the > database password, so that we can target specific tables / databases for > specific users. One of the databases is to hold financial data, which I > would not want a "role-based" user id (aka "web"/"web") to be able to see. > > I *assume* that a possibility like this would greatly benefit several > organizations using MySQL for similar purposes, if these organizations > already have centralized password databases. Nowadays with Microsoft finally > supporting Kerberos by default as the means to store Active Directory > passwords, it would seem to me that a large number of organizations actually > fit that bill. The first thought is - check Summer of Code projects: there're two related: http://code.google.com/soc/2008/mysql/appinfo.html?csaid=9CDE431A6856AD74 this is your roles. http://code.google.com/soc/2008/mysql/appinfo.html?csaid=1F94B07630EA06DA This is a possibility to use LDAP. > So to that affect and after a good long though about integrating LDAP Bind > facilities into MySQL for the purpose of central authentication (at first), > I would like to confirm my thoughts with the "crowd". I prefer to look at LDAP authentication in MySQL from the "pluggable" point of view. That is, a server provides an API where an authentication code could be plugged in. And there's an LDAP authentication plugin that is loaded in the server and does the real job. And from these two sides of the problem I'm mostly interested in the first - that is, what API a server provides (should provide, as there's none at the moment) and how it's all implemented internally. What a plugin does is "up to the plugin author", it can even implement a very organization-specific security policy, if necessary. > Functional Requirements: > > FR0001 Utilize centralized passwords so that in a web or > multi-tier environment true SSO can be implemented > and user's data modification can be audited It's up to the plugin. It could support centralized passwords, passwords in a MySQL table, passwords in $HOME/.mysql_password file, whatever. A server doesn't need to know where a plugin takes passwords from. > FR0002 At all times, the standard MySQL password mechanism > as from MySQL 4.1.1 is to preferred and used as default. Perhaps. > Technical Requirements: > > TR0001 Implement configuration option in the client > protocol (mysql, libmysqlclient, etc.) to accept > an LDAP based password > (e.g. -use-ldap or something similar). Okay, although it will be something more generic, not tied to ldap (ldap will be a value of the option, not a name). > TR0002 If the client opts for -use-ldap, the connection to > the server *must* be SSL enabled, otherwise the > connection is refused without the password being > checked (or otherwise made available to the server). > (WL#891, possibly others). > This requirement is already specified in the > GRANT privilege system as described in Section > 12.5.1.3 of the MySQL 5.0 Reference Manual. Up to the plugin. It could refuse non-encrypted connection, if desireable. > TR0003 The Client sends the decryptable password over the > wire, prefixed by 'L' rather than '*', so as to > indicate to the server that > an LDAP password is used. How to send a password over the wire is to the plugin. > TR0004 The normal password check routine is bypassed for > LDAP based passwords, which will be send over the > wire in a decryptable fashion. Sure. If an authentication plugin is used then the server will bypass a normal password check routine. > TR0005 Implement server-side configuration option for > "ldap-server" (in a URI compatible way, i.e. > ldaps://ldap.example.com) which *should* (at the > end-user's discretion) use LDAPS rather than > LDAP protocol, again, to keep the password safe. A plugin may add any configuration options to the server. > TR0006 The server checks the password against LDAP, > by decrypting the user.password field (retrieving > the user's DN) and binding to the configured LDAP > Server using the DN and the given password. If > this binds ucceeds, the password is correct. up to the plugin > TR0007 The MySQL Server *must* only support > LDAP_PROTOCOL_VERSION 3, as this allows for SSF and > SASL mechanisms between itself and the external > password database (e.g. Kerberos, RADIUS). up to the plugin > TR0008 Support for OpenLDAP *must* be implemented. > Possibilities to extend this scheme to SunOne, > Oracle Identity Server, Microsoft Active Directory, > or Novell Directory *can* be provided. you guessed it, up to the plugin ;) > Required implementation changes: > > 1) Implement command line option in the client called "-use-ldap" to > indicate that the user wants to use the LDAP based password (for GUI > or TTY based clients). right > 2) Implement connection option to indicate that the client wants to > use the LDAP based password (for wire-based clients, e.g. PHP). up to the plugin > 3) Implement server-side configuration change that allows the end-user > to set the URI of his/her intended LDAP server. up to the plugin > 4) make_scrambled_password() must detect the existence of the "{LDAP}" > prefix in the password (PASSWORD() function) and store that password, > which is of '"{LDAP}"' format, in a decryptable manner in the > user.password field. I think it should not be in the make_scrambled_password(). But otherwise it's correct - a client somehow notifies the server what authentication scheme it uses. There could also be a negotiation part - a server replies "I don't support this one, but only this and that", and a client decides to use another one. > 5) scramble() understands -use-ldap and uses my_crypt() to create a > decryptable form of the password that is prefixed with 'L' (as > opposed to '*'). after client and server has agreed what authentication plugin to use, the rest is done in the plugin, not in the server. > 6) check_scramble() understands that if the first character of message is > 'L', it should verify the password against the configured LDAP Server, by: > a) decrypting user.password > b) creating a connection to the LDAP server, and setting the connection > option for LDAP_PROTOCOL_VERSION to 3 > c) decrypting the client's password in memory > d) binding to the LDAP using the already established connection > e) destroying the client's password memory region > f) destroying the decrypted user.password memory region > g) verifying the result of the bind from d) above I don't quite understand why you need to store anything in user.password, but anyway it's up to the plugin. > Would this seem a do-able change? I am trying to adhere at all costs > to FR0002 above, so as not to break existing code / applications. > > I would think that the proposed change is also applicable to MySQL > Embedded, if that is configured to check passwords. Yes, why not. > Have I missed anything, do you see gaps in the above specification? > Please let me know!! For SSO you are supposed to store also the list of users in the LDAP, not only the passwords. That is the list of users in the mysql.user should be empty, at least it should not contain SSO users. I don't see how it works in your proposal. > I will start implementing the above from the 3rd of June onwards, if > there are no comments. I'd suggest to wait till the end of SoC, then - if everything will go as planned - you will have support for authentication plugins in the server, and all you need to do is LDAP quering, client-server communication, authentication scheme negotiation, etc will be done by the server. > If (enough) comments are of nature that this is not usable/acceptable > for MySQL at any rate, I will keep the change private, and will not > disturb you further. Not at all! I'm sure the community will greatly benefit from LDAP authentication plugin - it's one of popular feature requests, so the demand is high. > Otherwise, a discussion about the comments will sure commence, > whereupon an agreement shall be reached, whereupon the implementation > shall start. so be it! :) Regards / Mit vielen Grüssen, Sergei -- __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik / /|_/ / // /\ \/ /_/ / /__ Principal Software Engineer/Server Architect /_/ /_/\_, /___/\___\_\___/ Sun Microsystems GmbH, HRB München 161028 <___/ Sonnenallee 1, 85551 Kirchheim-Heimstetten Geschäftsführer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer Vorsitzender des Aufsichtsrates: Martin Häring