PluggableAuthenticationSupport
This page describes the MySQL plan for Pluggable Authentication Support, which is currently under
consideration. Internally it's known as WL#1054 (WL stands for "Work Log"; conventionally worklog
entries are written like this). The worklog is updated periodically, and at irregular intervals we'll update this page.
The format of this page has been derived manually from the original entry, and may contain errors and outdated information. Please quote from this page when sending feedback on the specification. Thank you.
[edit] High-level description
We need to add, to the MySQL Server and client, support for easy extension of authentication to use external authentication methods (like we have OPENSSL now) as well external user (password base) - OLDAP, Kerberos, Unix Passwords, Windows Users.
Later we can add some particular auth modes based on this model.
The support shall allow enabling extension by loading .so (dll) module so our binary can be used. Interface for compiling in module shall present as well.
We would also need an option for control of allowed auth types for server and client as we have it.
It is possibly better to develop some foundation ground for MySQL extension libraries first as many of their functions are similar, for any extension type - such as possibly added status variables, modules etc.
Related feature-request bugs:
- Bug#4703 Permit authentification outside of mysql
- Bug#9411 Support for Unix socket authentication (SO_PASSCRED / SCM_CREDENTIALS)
[edit] High-level specification
[edit] Cautionary Note added by Brian, do not delete
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.
-- End of note added by Brian
(See also WL#3118 Pluggable Authorization Support.)
[edit] 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 plugin that applies for "AUTHENTICATION", 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".
[edit] 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) are 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';
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'.
- 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 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.
- We store this value in a new column in mysql.user.
(See also WL#3125 "New structure of privilege tables".)
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).
[edit] The REQUIRE clause
In full, the syntax of the "[REQUIRE ...]" clause is:
[REQUIRE NONE | [{SSL| X509}] [CIPHER 'cipher' [AND]] [ISSUER 'issuer' [AND]] [SUBJECT 'subject']]
This is not strictly an issue for the new authentication, and is not strictly necessary: this clause exists in GRANT. I only believe that "CREATE USER ... REQUIRE ..." looks good.
[edit] ALTER USER
We have not needed an ALTER USER statement till now, but we'll need one now that there are several clauses.
The ALTER USER statement looks like this:
ALTER USER user_name [ IDENTIFIED BY 'password' ] | [ IDENTIFIED VIA 'authentication plugin name' AS 'authentication string' ] [ REQUIRE ...]
... and the clauses mean the same things that they mean with CREATE USER.
Required privileges: CREATE USER or UPDATE privilege on the mysql database.
There is no convenient way to alter all users. We'll suggest using "UPDATE mysql.user ...".
[edit] GRANT ... IDENTIFIED VIA
We already have a clause GRANT ... IDENTIFIED BY 'password'. We could also have a clause GRANT ... IDENTIFIED VIA 'authentication plugin'.
We will not implement this option.
[edit] How The Server passes User Information To The Plugin
The job is to check whether connecting is possible, so the checks are analogous to the checks that we do now for IDENTIFIED BY 'password' users.
The function identification should include the word AUTHENTICATION (for example MYSQL_AUTHENTICATION_PLUGIN for the type).
Check the user name. If the user exists (that is, if there is a mysql.user entry) and the user requires authentication (that is, the specification was with IDENTIFIED VIA `AUTHENTICATION_X'), find a plugin named 'AUTHENTICATION_X'. There will be only one such plugin, since `name` is a primary key. If the name is not found, that is, if we said CREATE USER joe IDENTIFIED VIA 'AUTHENTICATION_PAM' and there is no row in mysql.plugin named AUTHENTICATION_PAM, then treat that as rejection, the plugin check fails.
Plugin function parameters: there are eight inputs:
- User_Length INT
- User VARCHAR(16) CHARACTER SET UTF8 / This includes the host name
- Host_Length INT
- Host VARCHAR(60) CHARACTER SET UTF8
- AuthString_Length INT
- AuthString VARBINARY(50000)
- Password_Length INT
- Password VARCHAR(x)
SUGGESTION: Make username > 16 characters. See all the related requests for longer usernames (Google Search for longer username requests--Jpipes 18:19, 18 August 2006 (UTC)
and one input-output:
- MESS BINARY(1000) / see section "Callbacks"
The 'User' comes from "mysql ... --user=xxx" and the client sends it to the server in the clear.
The host name is separate from the user name.
The AuthString comes from the mysql.user column where we stored "Authorization String". It might (for example) be something that OpenLDAP can use to look up a user and compare a password, but we at the server end don't worry about that. The server performs no checks on AuthString. Its only job is to pass AuthString to the plugin. The maximum length of `AuthString` is arbitrary.
The `Password` comes from the user. See the later section, "Passwords".
We do not pass values of other items that might be on a utility command line, such as
- --compress, --port, --protocol, --socket.
We do not pass values that the plugin might be able to discover by itself.
Plugin function return: 1 if failure, 0 if okay. There will be no further information, so if there if there is a failure, and there is a log, the message will contain something like:
- "Authentication Plugin 'X' failed during connect"
or the old message:
- "Access denied for user 'root'@'localhost' (using password: NO)"
We will call the plugin during reconnect, since we don't distinguish between connect and reconnect.
[edit] 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.
MESS is an INOUT parameter for all server-plugin calls. For any server-plugin call, the plugin may return (instead of "pass" or "fail") SEND_TO_CLIENT_AND_WAIT or SEND_TO_CLIENT_AND_DO_NOT_WAIT, and MESS has the message which the server must send to the client. The server's job is to send it exactly as as received, with no attempt to convert, or to frame, or to add a packet header.
If the plugin returned SEND_TO_CLIENT_AND_WAIT along with MESS, then the server will wait for the client to reply to the sending of a MESS. When a reply comes, the server always accepts it as is, checks nothing, and passes on exactly what it received, as another server-plugin call. (All other parameters are the same as on the first call.) This sequence may be repeated until the plugin finally replies "pass" or "fail" or SEND_TO_CLIENT_AND_DO_NOT_WAIT.
If the plugin returned SEND_TO_CLIENT_AND_DO_NOT_WAIT along with MESS, then the server will not wait for the client to reply to the sending of a MESS. The server will proceed as if the plugin returned "pass".
( In an earlier version of this document, the suggestion was: plugins can directly communicate with clients, so "We do pass, in AuthString or in a not-yet-defined parameter, the socket / handle / shared-memory information for the client-to-server connection, in case the plugin will need to contact the client directly." Wrong. Cancel that. No direct contact. )
[edit] 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 probably will 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.
[edit] 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".
[edit] Metadata
There is 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".
[edit] Anonymous accounts
It is possible to declare that the anonymous (blank) user is IDENTIFIED VIA, either by saying ALTER USER `` IDENTIFIED VIA ... or by saying DROP USER `` and CREATE USER `` IDENTIFIED VIA ... There is no plan to change any installation script, so by default the anonymous user won't be IDENTIFIED VIA.
"SET DEFAULT ROLE role_name TO ``" will be possible too (see WL#988 Roles).
[edit] Vulnerabilities and Defects
Since we use plugins, IDENTIFIED BY authentication won't work with statically linked binaries. WL#2947 "MySQL plugin interface: statically compiling in" is not scheduled for the same version of MySQL.
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 can find a password, unencrypted, that might apply for non-MySQL parts of the network.
[edit] An example plugin
We will provide an example plugin, and installation instructions, for this situation:
- CREATE USER ... IDENTIFIED VIA 'AUTHENTICATION_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) I've 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).
[edit] 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.
[edit] 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". If that's a requirement, okay, but it's a large change.
There have been suggestions for "connect EVENTs" and "connect TRIGGERs", but they're further in the future.
[edit] 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.
[edit] References
Oracle10g CREATE USER statement:
Oracle Database Security Guide:
"SQL Server 2005 Part 1 - Security (Authentication)"
"Authenticating with LDAP using Openldap and PAM"
"Understanding PAM"
"SQL Server 2005 Authentication and Regulatory Compliance"
"SQL Server 2005 and Oracle 10g Security Comparison"
"Kerberos and PAM"
PADL Open Source Software
email re using WL#1054 and Kerberos