Add, for MySQL Server and clients, a way to use external authentication methods (like we have for OpenSSL now) as well as external users. Consider OLDAP, Kerberos, Unix Passwords, Windows Users. Later we can add some particular authentication modes using the model that we supply with this task. We also need an option for control of allowed authentication types for server and client.
===Plan=== When Joe logs in, we want to verify: is this really Joe, and may he connect? That's authentication. Authentication is possible via a plugin. While connecting, MySQL finds the AUTHENTICATION plugin that applies for the particular user, invokes it, and returns "okay" or "reject". If the answer is "reject", the whole connection fails. The plugin may call other functions, such as OpenLDAP, PAM, or Kerberos APIs. Supply a full example for one case, emphasizing that it's only an example. We don't supply guaranteed authentication code ourselves. We only "authenticate users". We do not "authorize users". We do not "authenticate roles". ===Syntax=== CREATE USER user_name [ IDENTIFIED BY 'password' ] | [ IDENTIFIED WITH 'authentication plugin name' [AS 'authentication string'] ] We already support "CREATE USER ... [ IDENTIFIED BY 'password']". Current functionality of CREATE USER (who can do it, what rows it produces) is unchanged. The new clause is "IDENTIFIED WITH 'authentication plugin name' AS 'authentication string'". It is Oracle-like but non-standard, there's no problem changing the wording to whatever people want. An example from Oracle's documentation: CREATE USER global_user IDENTIFIED GLOBALLY AS 'CN=analyst, OU=division1, O=oracle, C=US'; With our syntax and recommendations this would be: CREATE USER global_user IDENTIFIED WITH AUTHENTICATION_OPENLDAP AS 'CN=analyst, OU=division1, O=oracle, C=US'; The 'authentication plugin name' is a value that we will use to determine which authentication plugin we want. Generally it will be a short identifier like 'AUTHENTICATION_OPENLDAP' or 'AUTHENTICATION_WINDOWS'. The general rules for plugin names apply: * It should be UTF8. * We don't allow a @variable here. * We allow the 'quote marks' to be omitted. * We check whether plugin exists at CREATE time, and return an error if it is not available for the current user. * We store this value in a new column in mysql.user. The 'authentication string' is an optional string that we will pass to an authentication plugin. * It should be UTF8. * We don't allow a @variable here. * We don't allow the 'quote marks' to be omitted. * We store this value in a new column in mysql.user. The column definition is: TEXT CHARACTER SET UTF8 COLLATE UTF8_BIN but the implementor may change it to BLOB. We don't allow "CREATE USER ... IDENTIFIED BY ... IDENTIFIED WITH ...", although I (Peter Gulutzan) suppose that would be cute (it would mean that we check passwords AND we invoke the plugin). We don't allow "CREATE USER ... IDENTIFIED WITH ... IDENTIFIED WITH ...", although I suppose that would be cute too (it would mean that we can authenticate using two different plugins). There is no convenient way to alter all users. We'll suggest using "UPDATE mysql.user ...". ===GRANT ... IDENTIFIED WITH=== We already have a clause GRANT ... IDENTIFIED BY 'password'. We will also have a clause GRANT ... IDENTIFIED WITH 'authentication plugin' AS .. . GRANT ... IDENTIFIED WITH is a convenience shortcut to the following sequence of commands that either succeeds or fails as a whole: CREATE USER ... IDENTIFIED WITH ... [ AS ... ] GRANT ... TO ... This means that GRANT ... IDENTIFIED WITH ... AS ... will fail if there already is such user defined (this is unlike GRANT ... IDENTIFIED BY ...). Creation of the user through GRANT ... IDENTIFIED WITH ... happens even if sql_mode=no_auto_create_user is true. Note also that GRANT ... IDENTIFIED BY will not change the authentication method. ===Proxy users=== Plugins may request that certain externally defined users should authenticate with the database as differently named mysql users. Consider the following definitions: CREATE USER external_auth IDENTIFIED WITH 'auth_plugin' AS ... CREATE USER employee IDENTIFIED BY ... CREATE USER manager IDENTIFIED BY ... GRANT PROXY ON employee TO external_auth; Now when somebody logs in through 'external_auth' the plugin based on some external criteria may request that this login should become the 'employee' local user by returning 'employee' from the authentication plugin function. In this case 'external_auth' is a "proxy user" (a user who can impersonate or become known as another user) and employee is a "proxied user" (a user whose identity can be taken by a proxy user). MySQL will find the best match when given user name alone, just as it does now. The server must then make sure that external proxy authentication through 'employee' is possible through the 'external_auth' user. It does that by checking for the right 'PROXY ON employee' privilege that should be granted to external_auth. PROXY will be a new reserved word. ===Default proxy user=== If we want all or some users to connect via external plugin we can create a blank user in MySQL, set it up to use plugin authentication and let the plugin return the real authenticated user name (if different from the blank user). Example : CREATE USER ''@'' IDENTIFIED WITH 'ldap_plugin' AS 'O=Oracle, OU=MySQL'; CREATE USER developer IDENTIFIED BY 'test'; CREATE USER manager IDENTIFIED BY 'test2'; GRANT PROXY ON manager TO ''@''; GRANT PROXY ON developer TO ''@''; Now assume that somebody tries to login as follows : mysql --user=joro --password='joros_pass' ... The server will not find 'joro' defined as a mysql user. But because there's a blank user (''@'') it will invoke 'ldap_plugin' passing it 'joro' and 'joros_pass'. Now assume that the 'ldap_plugin' finds in the ldap directory that 'joro' is a developer. It will return 'developer' to the mysql server. The server will then check if ''@'' can authenticate as 'developer' and will accept the login, setting USER() and CURRENT_USER() as follows: mysql> select user(); +----------------+ | user() | +----------------+ | joro@localhost | +----------------+ mysql> select current_user(); +----------------+ | current_user() | +----------------+ | developer@% | +----------------+ For simplicity external authentication cannot be multilevel : neither the credentials for 'manager' nor the ones for 'developer' are taken into account in the example above. However they're still used if one tries to authenticate directly against the 'developer' account. ====Proxy user status variables==== There will be status variables to help trace the proxy login process: - 'proxy_user' : the proxy user account name used when logging in. This will be null if proxying is not used. Using the above example SELECT @@proxy_user; will return ''@'' - 'external_user' : sometimes the authentication plugin may use an external user to login. E.g. when using windows native authentication the plugin authenticates using the windows API and doesn't need the login id passed to it. However it still uses an Windows user id to authenticate. The plugin may return this external user id (or the first 512 utf-8 bytes of it) to the server via the 'external_user' read only session variable. When using native authentication this will contain an empty string. ===GRANT PROXY ... TO ... ==== A special privilege is needed for an external authentication account to connect as another user. It's granted by e.g. the following statement : GRANT PROXY ON <proxied_user> TO <proxy_user> ... <proxied_user> must represent a valid locally authenticated user at login time or the login will fail. <proxy_user> must represent a valid externally authenticated mysql user at login time or the login will fail. Note that the usual MySQL GRANT syntax extensions, e.g. * GRANT PROXY ON a TO b,c,d; * GRANT PROXY ON * TO d; * GRANT PROXY ON a TO d IDENTIFIED BY ...; * GRANT PROXY ON a TO d WITH GRANT OPTION; will work as usual. In this example "GRANT PROXY ON * TO d;" the asterisk means "any user". The corresponding REVOKE syntax is: REVOKE PROXY ON <proxied_user> FROM <proxy_user> The same applies to syntax extensions for REVOKE. The PROXY privilege can be granted by : - <proxied_user> for itself : the value of USER() must exactly match CURRENT_USER() and <proxied_user>, both user and the host name. - somebody that has GRANT PROXY ... WITH GRANT OPTION on that name Note that the 'root' user created by default will also have a PROXY privilege to all users with GRANT option. Example : root> CREATE USER ldap_admin IDENTIFIED BY 'test'; root> GRANT PROXY ON * TO ldap_admin WITH GRANT OPTION; (Now log out and log back in as ldap_admin) ldap_admin> GRANT PROXY ON sally TO joe; and thus 'ldap_admin' can manage all the specific GRANT PROXY mappings ===How The Authentication works=== We try to preserve the backward compatibility, that is to keep the protocol  compatible, and to extend it, not to replace it. Thus, the authentication goes as follows: --> client connects to the server --> server sends the Handshake Initialization Packet. Note the packet carries the following new fields: * The upper 16 bit of the server capabilities (after server status) + CLIENT_PLUGIN_AUTH bit set in them * Name of the plugin the server uses (after the second part of the scramble) if the CLIENT_PLUGIN_AUTH bit in server capabilities is set --> client invokes the default (or specified) client plugin. * If the plugin is not the default one (native backward compatible internal authentication plugin) the client will not feed it with the scramble sent in server's handshake packet. --> client makes sure the first packet sent is the auth packet as usual (and sets CLIENT_PLUGIN_AUTH). * If it uses non-backward compatible authentication the content of the scramble_buff is defined by the plugin * sends plugin name as ASCIIZ string after the database name if the server supports plugin authentication. --> server checks that * host is allowed to connect * user name is found in the mysql.user table or there is a default proxy user that gets used instead. * plugin specified in the "plugin" column of the mysql.user table is loaded ** if either of the above fails - authentication fails * and it's the same plugin as client has sent in the auth packet ** if it fails, see below and then it calls the plugin with the received username/scramble data --> if the client has used a wrong plugin, server requests the client to use the correct one. This includes the case when a client has sent the long hash while the short was required: * a server sends a special "Use this plugin: XXX" packet, for XXX=="old 3.23 MySQL short scramble" this packet is one byte with the value 254. * a client replies as requested (or disconnects) --> now auth plugin can communicate with the client to do any further conversation, as necessary (see Callbacks). When SSL is negotiated it works as it does now - client only sends client flags and not the authentication information in the handshake reply packet. Parties switch to SSL, client resends handshake reply packet with the authentication information Plugin returns with "Authentication Succeed, Username is ABC" or "Authentication Failed". Note - plugin tells MySQL what the user name is. This user name is used as the authorization token in the established session. We will also call the plugin during reconnect, since we don't distinguish between connect and reconnect. ===Callbacks=== The plugin may need extra information from the client, or the plugin may need to pass extra information to the client. We do not expect direct plugin-client communication. We expect extra information to always go via the server, that is, there's plugin-server-client communication. The server is not expected to decipher any such messages. Server provides communication methods "send()" and "receive()" (these names are only examples). These methods tunnel the data through the already established client-server connection. ===Passwords=== The password is, as before, the value that the user enters with -p, or the value in MYSQL_PWD. For users with IDENTIFIED WITH, the server itself does no password checking, it just passes the value for the plugin. The plugin could just ignore the password. In that case the mysql.user.password column value is irrelevant for users with IDENTIFIED WITH. The SET PASSWORD statement still works, but there should be a warning: * "Warning: SET PASSWORD has no significance for users created with 'CREATE USER ... IDENTIFIED WITH ...' statements". Utilities that use -p won't have to change. But some plugins might cause a dialog-box "challenge" to appear, so we'll have to warn users that unattended operation isn't guaranteed. The :password in a FEDERATED connection string won't have to change. ===Existing, non-password, checks=== When you use IDENTIFIED WITH 'authentication plugin name' instead of IDENTIFIED BY 'password', you are saying: don't check the password the current way. But what about the other checks that happen at CONNECT time? We should continue to enforce them. So, even with IDENTIFIED WITH: * If there are no mysql.user entries or a default proxy user, continue to return ** "Access denied for user ..." * If the host name isn't good, continue to return ** "Host ... is not allowed to connect to this MySQL server" * Continue to check MAX_QUERIES_PER_HOUR, MAX_UPDATES_PER_HOUR, MAX_CONNECTIONS_PER_HOUR, MAX_USER_CONNECTIONS. This isn't great, because the server that the plugin connects to might have redundant or contradictory policies. But we can't remove current checks as part of a security "enhancement". ===Metadata=== Peter Gulutzan thinks that "there should be no metadata, other than the mysql database. SHOW GRANTS will not show a user's IDENTIFIED option. INFORMATION_SCHEMA.USERS doesn't exist, and won't exist. The mysqldump utility won't print out users. The mysqlaccess utility won't show new mysql.user columns. system_user() won't be like the standard SYSTEM_USER whose value represents the operating system user." The following two fields will be added to the mysql.user table: plugin char(64) DEFAULT '' NOT NULL authentication_string TEXT NOT NULL Both need to be text fields so that we can ensure that the plugins will always get their strings as UTF-8. ===Server-side plugins=== Server side plugins are accessed through the server side plugin API. The specific API for this type of plugins is defined as follows: /* A plugin that a client must use for authentication with this server plugin. Can be NULL to mean "any plugin". */ const char *client_auth_plugin; /* Function provided by the plugin which should perform authentication (using the vio functions if necessary) and return 0 if successful. The plugin can also fill the info.authenticated_as field if a different username should be used for authorization. */ int (*authenticate_user)(MYSQL_PLUGIN_VIO *vio, MYSQL_SERVER_AUTH_INFO *info); MYSQL_SERVER_AUTH_INFO is defined as : /** User name as sent by the client and shown in USER(). NULL if the client packet with the user name was not received yet. */ const char *user_name; /** Length of user_name */ unsigned int user_name_length; /** A corresponding column value from the mysql.user table for the matching account name */ const char *auth_string; /** Length of auth_string */ unsigned long auth_string_length; /** Matching account name as found in the mysql.user table. A plugin can override it with another name that will be used by MySQL for authorization, and shown in CURRENT_USER() and USER() */ char authenticated_as[MYSQL_USERNAME_LENGTH+1]; /** The unique user name that was used by the plugin to authenticate. Plugins should put null-terminated UTF-8 here. Available through the @@EXTERNAL_USER variable. */ char external_user; /** This only affects the "Authentication failed. Password used: %s" error message. Has the following values : 0 : %s will be NO. 1 : %s will be YES. 2 : there will be no %s. Set it as appropriate or ignore at will. */ int password_used; /** Set to the name of the connected client if it can be resolved, or to the address otherwise */ const char *host_or_ip; /** Length of host_or_ip */ unsigned int host_or_ip_length; ===Client-side plugins=== Obviously, for a client to be able to utilize an arbitrary third-party authentication scheme that was loaded at run-time in the server, the client needs to have some plugin capabilities too. Note that a server plugin is different from the client plugin, they even have different names. Every server auth plugin tells the server what auth plugin it should use. Implementing client plugins is not part of this task. Here we'll only do one 2 - hard-coded - client auth "plugins" which will do traditional mysql password or mysql old password conversation. The API for generic client plugins is defined by initializing the following structure : int type; unsigned int interface_version; const char *name; const char *author; const char *desc; unsigned int version; int (*init)(char *, size_t, int, va_list); int (*deinit)(); The API for authentication client plugins is defined as (in addition to the generic client plugin API entries) : int (*authenticate_user)(MYSQL_PLUGIN_VIO *vio, struct st_mysql *mysql); MYSQL_PLUGIN_VIO has the following members: /* Plugin provides a pointer reference and this function sets it to the contents of any incoming packet. Returns the packet length, or -1 if the plugin should terminate. */ int (*read_packet)(struct st_plugin_vio *vio, unsigned char **buf); /* Plugin provides a buffer with data and the length and this function sends it as a packet. Returns 0 on success, 1 on failure. */ int (*write_packet)(struct st_plugin_vio *vio, const unsigned char *packet, int packet_len); /* Fills in a st_plugin_vio_info structure, providing the information about the connection. */ void (*info)(struct st_plugin_vio *vio, struct st_plugin_vio_info *info); ===Vulnerabilities and Defects=== Since we use plugins, IDENTIFIED WITH authentication won't work with statically linked binaries, on some operating systems. (On other operating systems it is now possible to state plugins when statically linking because WL#3201 "Configure support for server plugins" is complete.) So we cannot load a plugin from any version, at any time. Come to think of it, maybe that's a feature. Anybody with DELETE privilege on mysql.plugin can cancel authentication. And we have to allow mysql.plugin privileges to people installing or removing full-text functions. But the only effect will be that users cannot connect, because when the server can't find a plugin, it regards that as authentication rejection. If someone has access to the library directory, or to my.cnf, the authentication library can be replaced. Users created with 'CREATE USER ... IDENTIFIED BY ...' or 'GRANT ...' will not be affected by the plugin. If you want anonymous users to be affected, you'll have to change the mysql.user row for user=''. If you start the server with --skip-grant-tables, then there is no authentication, either the old kind or the new kind. Anybody who can recode MySQL's server might find a password, unencrypted, that might apply for non-MySQL parts of the network. ===An example plugin=== The details of this section are not compulsory. We will provide an example plugin, and installation instructions, for this situation: * CREATE USER ... IDENTIFIED WITH PAM ...; So we need an .so (Linux) and a .dll (Windows) that supports Pluggable Authentication Modules (PAM). Why PAM? No great reason, but (1) it's mentioned in WL#2761 "MySQL plugin interface", (2) the title of this worklog entry is "Pluggable authentication support", (3) it may be useful as a gateway for other authentication techniques, (4) Peter has seen a commercial saying that "PAM is now the standard authentication framework of many Linux distributions, including RedHat and Debian". The plugin must interpret the inputs (user, AuthString, Password), check format, create an appropriate string based on the inputs, pass to PAM, interpret the result, and return "reject" or "okay" to the caller (that is, to the server). ===Other DBMSs=== Oracle10g and SQL Server 2005 have all the functionality we will have, and they don't require plugins -- they have in-the-box support for things like Windows Authentication, OpenLDAP, Kerberos, Radius, Identix, etc. ===Other activities=== I've emphasized enough, I hope, that this task is named "Pluggable authentication support" and not authorization support. With authorization, we'd find out not only who the user is, but what the user is allowed to do. Since we maintain authorizations with SQL GRANT statements, and since we'll have Roles soon (WL#988), this is much less necessary than authentication. However, let's acknowledge that it's possible for the plugin to return "these are the privileges that Joe has today, please adjust your GRANT tables accordingly". Supporting this functionality is not part of this task. There have been suggestions for "connect EVENTs" and "connect TRIGGERs", but they're not part of this task. ===Issues to consider=== Trudy Pelzer wrote ("Update Seattle summary: 5.2 general tasks") what we need to consider, according to her notes of a Seattle meeting. I (Peter) include tentative replies. I doubt they'll all be acceptable. "Everything needs to be pluggable, not a mix." * No. We leave the existing password-checking code in, so there's a mix: the old way (default), the new way (if you use new CREATE USER ... IDENTIFIED WITH). "We'd like to be able to use the family of "mod_auth_*" modules (see http://httpd.apache.org/docs/2.0/mod/#A);" * No. I guessed that was intended as an example only. I guessed that the PostgreSQL PAM example was closer to what we want as inspiration. "LDAP is an example, as is a module that allows using Windows auth." * No. I thought a PAM example would be easier, pam_ldap is okay. "What happens with backup/restore?" * Nothing. The mysqldump utility dumps database information, but users and roles are not in databases. "For repair, do you lock everyone out or not?" * Don't know. I don't understand the concern. "How do you deal with authentication when you don't know how to handle the hash (e.g. as in WinNT)?" * Not our department. We leave that to the plugin maker. "We need a cryptography scheme, so that passwords are plain to the server but not otherwise (SSL?). We want to do SSL through the whole authentication handshake, but not afterwards." * No. We want to do that. But I did not consider it as part of this task. Let's try to limit it so that "use SSL when passing client authentication packet from client to server" is outside WL#1054. "Check Oracle's LDAP option to see how they do it." * No. We do try to follow Oracle, in a general way, as always. "We need to be able to debug authentication support (i.e. we should at least provide a warning: why authentication failed)." * No. That would require support for WL#2940 "MySQL plugin interface: error reporting" and WL#2940 is not for the next version. (See also WL#3118 Pluggable Authorization Support.) ===References=== 1. http://forge.mysql.com/wiki/MySQL_Internals_ClientServer_Protocol 2. Oracle11g CREATE USER statement: http://download- uk.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_8003.htm#i2065278 3. Oracle Database Security Guide: http://download.oracle.com/docs/cd/B28359_01/network.111/b28531/toc.htm 4. "SQL Server 2005 Part 1 - Security (Authentication)" http://www.databasejournal.com/features/mssql/article.php/3461471 5. "Authenticating with LDAP using Openldap and PAM" http://www.imaginator.com/~simon/ldap/ 6. "Understanding PAM" http://www.aplawrence.com/Basics/understandingpam.html 7. "SQL Server 2005 Authentication and Regulatory Compliance" http://www.dbazine.com/sql/sql-articles/cook16 8. "SQL Server 2005 and Oracle 10g Security Comparison" http://www.microsoft.com/sql/prodinfo/compare/oracle/ ss2005oracle10gsecuritycompare.mspx 9. "Kerberos and PAM" http://books.google.com/books? id=NxMeJaLZAHwC&pg=PA212&lpg=PA212&dq=kerberos+pam&source=web&ots=ySrOoLsylE&sig =2to9rC1pZQ6j4fLS2koFP6qt_Ek#PPA213,M1 10. PADL Open Source Software http://www.padl.com/Contents/OpenSourceSoftware.html 11. BUG#4703: Permit authentification outside of mysql 12. BUG#6733: Kerberos user authentication support 13. BUG#9411: Support for Unix socket authentication (SO_PASSCRED / SCM_CREDENTIALS) 14. BUG#39397: LDAP, Integrated - Authentification for Mysql - Server 15. BUG#51323: pluggable authentication 16. BUG#49940: Authenticate by Linux system account
== Extensions to the C API == Added two new mysql_options options : - MYSQL_PLUGIN_DIR : allows a C application to specify a directory where the client can find plugins. MySQL C client searches for plugins in MYSQL_PLUGIN_DIR if specified. If not it uses the compiled-in plugin dir defaulting to lib/plugin instead. - MYSQL_DEFAULT_AUTH : the name of the plugin (without the extension) to use instead of the built-in native authentication client-side plugin == Extensions to the mysql client program === There's 2 additional options : --plugin_dir and --default_auth that, when specified set MYSQL_PLUGIN_DIR and MYSQL_DEFAULT_AUTH respectively. == Extensions to the mysqltest client program == - There's a new option : --plugin_dir to set the MYSQL_PLUGIN_DIR C API option. - There's a new optional argument on the 9th place to the connect command. The string specified there (if any) is passed to the MYSQL_DEFAULT_AUTH command. == COM_CHANGE_USER additions === COM_CHANGE_USER is defined as follows : This packet is effectively a re-login without closing/opening the connection. Important side effect: this packet destroys the session context (temporary tables, session variables, etc.) in the MySQL server. Some connection pool implementations use this to clean up the session context. Bytes Name ----- ---- n user name (Null-terminated string) n password 3.23 scramble - Null-terminated string (9 bytes) 4.1 scramble - Length (1 byte) coded string (21 byte) n database name (Null-terminated string) 2 character set number (since 5.1.23?) Client plugin name is appended to this as follows : n client plugin name. Null terminated string. (since 5.5.7)