WL#1054: Pluggable authentication support
Affects: Server-5.5
—
Status: Complete
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 TO ...
must represent a valid locally authenticated user
at login time or the login will fail.
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 FROM
The same applies to syntax extensions for REVOKE.
The PROXY privilege can be granted by :
- for itself : the value of USER() must
exactly match CURRENT_USER() and ,
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 [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
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[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
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[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
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)
Copyright (c) 2000, 2024, Oracle Corporation and/or its affiliates. All rights reserved.