WL#1054: Pluggable authentication support

Affects: Server-5.5   —   Status: Complete   —   Priority: Low

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.

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".


  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

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
  'CN=analyst, OU=division1, O=oracle, C=US';
With our syntax and recommendations this would be:
  CREATE USER global_user
  '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
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: 
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 ...".


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:

GRANT ... TO ...

This means that GRANT ... IDENTIFIED WITH ... AS ... will fail
if there already is such user defined (this is unlike 

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 ...
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';
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;

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';
(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

===How The Authentication works===

We try to preserve the backward compatibility, that is to
keep the protocol [1] 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

We will also call the plugin during reconnect, since
we don't distinguish between connect and reconnect.


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


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: SET PASSWORD has no significance for users 
  created with 'CREATE USER ... IDENTIFIED WITH ...'  

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"

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".


Peter Gulutzan thinks that "there should be no metadata, other
than the mysql database. SHOW GRANTS will not show a
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 
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[512];

    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 

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[3];                              
  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

===An example plugin===

The details of this section are not compulsory.
We will provide an example plugin, and installation
instructions, for this situation:

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
* 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.)


1. http://forge.mysql.com/wiki/MySQL_Internals_ClientServer_Protocol

2. Oracle11g CREATE USER statement:

3. Oracle Database Security Guide:

4. "SQL Server 2005 Part 1 - Security (Authentication)"

5. "Authenticating with LDAP using Openldap and PAM"

6. "Understanding PAM"

7. "SQL Server 2005 Authentication and Regulatory Compliance"

8. "SQL Server 2005 and Oracle 10g Security Comparison"

9. "Kerberos and PAM"

10. PADL Open Source Software

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 / 

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 
 - 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 
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