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.
Related feature-request bugs:
- BUG#4703 Permit authentification outside of mysql
- BUG#9411 Support for Unix socket authentication (SO_PASSCRED / SCM_CREDENTIALS)
- BUG#6733 Kerberos user authentication support
===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 VIA 'authentication plugin name'
AS 'authentication string' ]
[ REQUIRE ...]
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 VIA '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 VIA AUTHENTICATION_OPENLDAP AS
'CN=analyst, OU=division1, O=oracle, C=US';
Depending on the timeline and the progress of WL#2938,
we may want to change the above to
CREATE USER global_user
IDENTIFIED VIA 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 ASCII.
* 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 a 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.
(unless WL#2938 is done and we decide to use it)
* We store this value in a new column in mysql.user.
We don't allow "CREATE USER ... IDENTIFIED BY ...
IDENTIFIED VIA ...", although I suppose
that would be cute (it would mean that we check
passwords AND we invoke the plugin).
We don't allow "CREATE USER ... IDENTIFIED VIA ...
IDENTIFIED VIA ...", although I suppose
that would be cute too (it would mean that we can
authenticate using two different plugins).
===GRANT ... IDENTIFIED VIA===
We already have a clause GRANT ... IDENTIFIED
BY 'password'. We could also have a clause
GRANT ... IDENTIFIED VIA 'authentication plugin'.
===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
--> client sends auth packet as usual, if it uses non-default
authentication, it
* sets CLIENT_PLUGIN_AUTH capability flag
* the content of the scramble_buff is defined by the plugin
* sends plugin name as ASCIIZ string after the database name
--> server checks that
* host is allowed to connect
* user name is found in the mysql.user table
* plugin specified in the identified_via field of the
mysql.user table is loaded
** if either from 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/password
--> 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 authorizaton token in the established
session.
As usual, the originally specified user name is available
from SQL as USER(), and the authorizaton token - as
CURRENT_USER().
Reasoning: One of the most popular and requestes setups is
to keep the list of users outside of MySQL. In LDAP, for
example. In this case One can create an anonymous
"catch-all" user in MySQL, set it up to use LDAP
authentication, and the plugin will return the real
authenticated user name. Also, plugin may want to mangle the
user name somehow, e.g. Joe -> Jack, or (more realistically)
joe!office!intranet!bayarea -> joe.
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 VIA, 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 VIA. 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 VIA ...' 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 VIA '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 VIA:
* If there are no mysql.user entries, 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 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."
===Client-side plugins===
Obviously, for a client to be able to utilize arbitrary
third-party authentication scheme that was loaded 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 - hard-coded - client auth "plugin" which
will do plain-text conversation, printing the received data
to the user and reading the reply.
===Vulnerabilities and Defects===
Since we use plugins, IDENTIFIED BY authentication
won't work with statically linked binaries.
It is now possible to state plugins when statically
linking on some operating systems (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 fulltext 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===
We will provide an example plugin, and installation
instructions, for this situation:
* CREATE USER ... IDENTIFIED VIA 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"
(5) PostgreSQL does it. Those who seek
inspiration can download PostgreSQL's source code
from postgresql.org and look at src/backend/libpq/auth.c.
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 is 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 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 VIA).
"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.
Brian Aker wrote:
"
For this project to be successful you must think about the need for three
different features:
* Authentication:
** Can we verify that this user is the user we think they are?
* Authorization:
** What privileges does this user have? Can they drop a table? Alter a table?
Load another plugin?
* Access:
** What access does this user have to a particular object, whether this be a
stored procedure, a table, a column (aka attribute), etc?
Keep in mind issues of the protocol: How is it possible to make any string
being sent be passed along to a service that does the three A's mentioned
above?
Ask yourself what happens if multiple plugins are stacked. Don't assume limits,
instead make data available.
"
(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