MySQL Forge

Resources for the MySQL Community

Category: Specifications

PluggableAuthorizationSupport

This page describes the MySQL plan for Pluggable Authorization Support, which is currently under consideration. Internally it's known as WL#3118 (WL stands for "Work Log"; conventionally worklog entries are written like this). The worklog is updated periodically, and at regular intervals we'll update this page.

The format of this page has been derived manually from the original entry, and may contain errors and slightly outdated information. Please quote from this page when sending feedback on the specification. Thank you.

Contents 1 High-level description 2 High-level specification 2.1 Install 2.2 Connect 2.3 Terms 2.4 The normal pass 2.5 Definer privilege 2.6 CREATE ROLE 2.7 CREATE USER 2.8 GRANT / REVOKE 2.9 ALTER USER 2.10 SET ROLE 2.11 Local Access 2.12 Metadata 2.13 Replication 2.14 Logging and monitoring 2.15 The old contents of WL#3118 2.16 Anthology of statements from Brian Aker 2.17 Anthology of statements from Mark Matthews 2.18 Anthology of statements from Jim Winstead

[edit] High-level description

To go with "Pluggable Authentication" (WL#1054), we want "Pluggable Authorization". This will occur via features which are unique to MySQL.

The plugin will decide what operations are possible. The server will do what the plugin decides, instead of using the tables in the mysql database.

[edit] High-level specification

Authorizations happen when the MySQL server is about to perform any act that requires access to any database object or global variable, including all acts performed in order to prepare or execute SQL statements. "Access" means read, write, use, get information about, create, or drop. Before the act is attempted (but possibly after initial processing such as parsing), the server tells the plugin "this is what I wish to do" and the plugin replies "okay" or "not okay".

Authorization plugins are for authorizations that are to be handled outside the DBMS. Let's refer to the SQL grant/revoke/create/use statements as the "current [authorization] system" and authorization via plugins as the "new [plugin] [authorization] system".

[edit] Install

Since we must install before we can use, installing plugins must depend on the current authorization system. As with any plugin: any user with INSERT privilege on mysql.plugin can install an authorization plugin.

[edit] Connect

There must be an authentication plugin. The authentication plugin chooses what the name of the authorization plugin will be. There is no way to choose via SQL statements or API calls.

Therefore, when an authentication plugin succeeds (that is, the user is okay for connecting), it returns authorization_plugin_name varchar() character set utf8. The server must then check whether the plugin identified by authorization_plugin_name is available. If not, connection fails with an error:

• "Authorization plugin unloadable"

Doubtless the authorization plugin may depend on some data that was discovered by the authentication plugin. That's not out problem. It's up to the authentication plugin to ensure that such information is persistent and available.

Probably the authentication and authorization plugins will be in the same library.

[edit] Terms

In what follows, there will be frequent mention of these terms:

"object type"

• The possible types of objects are:
  • TABLE, VIEW, EVENT, TRIGGER, DATABASE, INDEX, GLOBAL VARIABLE, SESSION VARIABLE, USER, ROLE, TEMPORARY TABLE, SAVEPOINT. PASSWORD, FILE, SLAVE.
• Eventually there will be more object types.
• An engine is not an object.
• A UDF is not an object.

"object name"

• The character string(s) necessary to identify an object, possibly including the database identifier and the table identifier.

(It is possible that there are multiple different databases all named "database1" if we deal with a central server which many MySQL server instances connect to. That is not our problem. The plugin must guarantee that names are unique.)

"operation"

• The possible operations are:
  • anything that reaches sql_parse.cc's dispatch_command() e.g. COM_REGISTER_SLAVE, COM_STMT_FETCH
  • anything that is implied by an SQL statement, such as a subquery, function, trigger, or reference.
• The categories are not mutually exclusive.

Privilege

• A description of a combination of an operation and zero or more objects, which may be stored in a central directory, and may be discovered by a plugin, and may be required by a server.

Pass/Fail

• This is what a plugin returns. The plugin only needs to say whether the server may proceed. The plugin does not have to suggest an error message, or (if the server passed many object names) the plugin does not have to say which particular object had a problem.

[edit] The normal pass

For the great majority of operations, including SQL statements, the server passes:

• operation type {type of object, object name, operation,...}

(The plugin should already know the user name, and "object name, operation" means "privilege" as defined.) The number of objects may be zero (for example "KILL 9" has no objects), or one, or more than one (for example "SELECT ... FROM a, b WHERE x=(SELECT ...)" has multiple objects).

The plugin returns Pass/Fail.

The plugin doesn't get very much information this way. It can't tell what kind of table the user is creating, for instance. But the only known alternative is to pass the whole SQL statement and expect the plugin to parse it, and that makes the plugin writer's job harder.

For example, suppose we perform these statements:

USE db1;

• The server passes:
  • USAGE, DATABASE, "db1"

CREATE TABLE t1 (s1 INT, PRIMARY KEY (s1)) ENGINE=INNODB;

• The server passes:
  • CREATE, TABLE, "db1.t1"

CREATE TABLE t2 (s1 INT) ENGINE=INNODB;

• The server passes:
  • CREATE, TABLE, "db1.t2"

CREATE TABLE t3 (s1 INT, FOREIGN KEY (s1) REFERENCES t1 (s1)) ENGINE=INNODB;

• The server passes:
  • CREATE, TABLE, "db1.t3"
  • SELECT, TABLE, "db1.t1"

CREATE TRIGGER t_bi BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES (new.s1);

• The server passes:
  • SELECT, COLUMN, "db1.t1.s1"
  • INSERT, TABLE, "db1.t2"

INSERT INTO t3 SELECT * FROM t2;

• The server must pass:
  • INSERT, TABLE, "db1.t3"
  • SELECT, TABLE, "db1.t2"

[edit] Definer privilege

Upon entry into a routine defined with SQL SECURITY DEFINER, the server passes:

• DEFINER-PRIVILEGE-IN, "user name"

The plugin returns Pass/Fail.

This involves a temporary switch to a different user, but the plugin does not perform a new authentication. (Authentication may require user interaction while definer may not be physically available.) It does not matter if the different user was not made with AUTHORIZATION VIA.

Upon exit from a routine defined with SQL SECURITY DEFINER, the server passes:

• DEFINER-PRIVILEGE-OUT, "user name"

The plugin returns Pass/Fail, although failure is unlikely.

[edit] CREATE ROLE

It will be possible to store roles, and all information about roles, "externally" -- that is, the role won't be in the mysql database or in any database available to the server. Presumably the role storage will be handled by some central authority chosen by the plugin.

Syntax:

CREATE ROLE ... IDENTIFIED VIA ...

The new IDENTIFIED VIA clause is similar to the one we use for CREATE USER. If it is present, the server does:

• 1. Check if the role exists. We cannot allow two roles with the same name, one created with IDENTIFIED VIA, the other created without IDENTIFIED VIA. Therefore this check is done by the server alone, before it calls the plugin.

• 2. Pass, as usual, the type of object and object name and operation, for example: CREATE, ROLE, "X"

It is possible that the plugin knows that there are other ways to create roles. If so, fine -- the plugin always has the right to return "Fail" instead of "Pass".

Similar considerations apply for DROP ROLE.

[edit] CREATE USER

We must continue to use the mysql.user table, because the authentication depends on it (see WL#1054). And we cannot start the authorization until the authentication is done, according to this task description. We have no idea whether a user is subject to the current or the new privilege system, until connection occurs.

Therefore we still need a mysql.user table. There have been objections, but so far, no complete alternatives.

The CREATE USER statement does not change, and its effects do not change. Of course, the plugin will receive the information that the user is being created, in the usual way.

[edit] GRANT / REVOKE

If a GRANT or REVOKE occurs, the server must determine whether the granting is happening for an IDENTIFIED VIA user or role. If not, then there's no problem: the operation is performed the current way (and the plugin is informed the usual way). If so, then there's a problem: it's meaningless or misleading to store the grant information in mysql.tables_priv etc. That's because, even if the plugin returns "Pass", the plugin doesn't really have to grant or revoke anything.

Therefore, after the plugin returns Pass for a GRANT or REVOKE operation, the server simply returns to the caller without making any changes to local tables.

[edit] ALTER USER

A large problem exists for the statement

ALTER USER User_1 IDENTIFIED VIA ...

because this is in effect a "migration" from the current system to the new system. The server will have to pass the grant information for all privileges that User_1 holds. The plugin will then be able to decide whether to store that information, for example by passing it to a central repository server.

If one alters the other way, with the statement ALTER USER user_1 [no IDENTIFIED VIA ...] then the user becomes subject once more to the current privilege system. There is no need for the plugin to send any information about the user's grant information.

[edit] SET ROLE

It will be desirable for SET ROLE and SET DEFAULT ROLE to have the same effect as in the WL#988 task description. However, it is impossible for the server to ensure this.

[edit] Local Access

The mysql-database tables (columns_priv, procs_priv, user, host, plugin, event, func, db, proc, tables_priv) continue to exist. We might want to let the plugin access them. But there is no mechanism for "granting authorization to a plugin". That is, plugin X cannot decide whether plugin X should be allowed to read from mysql.user.

Therefore we will, in effect, grant SELECT on all mysql-database tables to all plugins. The plugin will read these tables via callbacks: the plugin returns "please execute this select on a mysql table" and the server passes to the plugin "here is the result set".

The plugin itself should not access any other table in any database. But Sergei Golubchik points out: There's no way to control it - plugin is executed in the server's address space, it can access everything the server can. We don't have a sandbox for plugins."

[edit] Metadata

Users must know whether they are operating with the current security system, or with plugin security. They can discover this by looking at a new session variable, a new column in an information_schema table, or a new niladic function. The choice has not been made.

[edit] Replication

The slave will have to get authentication and authorization just like the master does. Therefore the slave must have the same plugins, and whatever services (e.g. LDAP) are available to the master are also available to the slave.

[edit] Logging and monitoring

The plugin has no responsibility to explain how it will decide what privileges it will grant. Even if it did have such responsibility, there is no way the server could know if the explanation was correct.

Therefore, any query of the privilege metadata (for example SHOW GRANTS, SELECT FROM INFORMATION_SCHEMA.USER_PRIVILEGES) cannot return complete and reliable information. The server doessn't know it.

And, any log of activity (for example the general query log) cannot contain complete and reliable information. The plugin doesn't record statements about which implicit grants it makes, or which mysql-database tables it accesses.

Thus, with the usual meaning of AAA (authentication authorization auditing), MySQL will supply only AA-. There is no guaranteeable way for MySQL to do auditing.

[edit] The old contents of WL#3118

( This section is temporary. It will be removed after confirmation that we will not follow the original description, at all. )

The original High Level Description of this task was:

" After we have "Pluggable Authentication" (WL#1054), we want "Pluggable Authorization". The additional steps are:

Assume there is an "authorizer", probably tightly connected to the "authenticator". This authorizer knows not only that there is a user Joey, but also that Joey is authorized (to connect to a database, to drop a trigger, etc.). Until now we have used SQL privileges for authorization. But the authorizer will override the SQL privileges.

The authorizer may be invoked at connect time (immediately after the authenticator). There may be a separate authorizer plugin.

The authorizer returns descriptions of the privileges to the plugin. The plugin converts these descriptions to SQL statements. The statements can be anything at all, but ordinarily will be things like GRANT, REVOKE, ALTER USER.

The privileges may include "role" privileges (see WL#988).

Possibly the server will change the user name too, depending on plugin instructions.

Example:

Joe gets authenticated.

The authorizer is also aware that Joe has Role `Office Help`.

The authorizer communicates this fact to the plugin.

The plugin creates two statements:

• REVOKE ALL ON *.* FROM Joe;
• GRANT `Office Help` To Joe;

This assumes the existence of a role named `Office Help`. "

[edit] Anthology of statements from Brian Aker

" CREATE USER generates a condition that is then pushed down into the auth layer. Engines can either read the data and pass it along, or capture the data and not pass it on. "

[edit] Anthology of statements from Mark Matthews

" Use case #3: This one's a little bit more pie-in-the-sky. Basically MySQL delegates _all_ authentication and authorization to the plugin. This means that no MySQL system tables are used for users identified by this table, and _all_ authentication and authorization (user/role relationships, permissions contained in role and authorization to access database objects all handled by plugins). This might even be as "out there" as the plugin handles "Does user have SELECT access to table 'foo'", and roles have nothing to do with it. This use case is more in Brian's domain, so maybe he can expand on it. Let's call this use case "PLUGIN-HANDLES-ALL". "

" I thought he [Brian] sees a plugin being able to accept/reject any access to a database object _directly_. "

" >> I'd like to extract some simple true/false sound bites from what you say above, with apologies if I omit or misinterpret something: - don't use mysql.user at all

I think authorization plugins should have the ability to use, or not use any existing MySQL authorization functionality. Some use cases will require none, some will require some.

>> - do no SQL privilege checking

There might be a class of plugins that don't use built-in MySQL privilege checking, which is demonstrated by your next statement.

>> - call a plugin when starting any statement that accesses SQL objects

Some plugins might want to do this. _I_ don't have any use cases where I see users wanting to do this, but Brian must since he asked for the ability for a plugin to do all authentication and authorization.

>> - don't use roles

Some plugins that don't use SQL-based _authorization_ might not. Others may. I think we have to include both possibilities.

>> - halt WL#1054 "Pluggable authentication support"

Too drastic. "

" Let me be more clear, so that I relate to the language that is in WL#988. In my opinion, it seems that one would use ROLEs to simplify management of security by naming a collection of permissions, which is what the SQL standard says (from what I can tell, in a nutshell). In the same vein, I see that users who are trying to simplify security management by plugging MySQL into an external authentication source will not want to maintain a mapping of "external" users to MySQL ROLES through maintaining data in a MySQL installation itself, but _might_ want to maintain an "implicit GRANT" (for lack of a better term) that is done through some mapping.

For example, assuming a user appears in an LDAP entry with an OU of "Database Production Team", a user _might_ want to have any user in that organizational unit be granted a role of DBA in a given MySQL database, and would not want to explicitly issue a "GRANT DBA ON foo.* TO user", but instead want the plugin to do whatever effectively causes that to happen.

The problem I have with WL#3118 is that it assumes that a plugin actually has to issue the grant statement to make this happen. I was under the impression that because everything is pluggable that we can't make that assumption (and that the "GRANT .../REVOKE ..." is an implementation detail that may or may not be how the functionality is actually accomplished). "

[edit] Anthology of statements from Jim Winstead

" with this, it is possible for users to exist entirely outside the mysql.* tables, and only pick up privileges based on roles and wildcard entries.

"

" (and i believe this is a matter for authentication plugins, not authorization. an authorization plugin would handle the later step of authorizing access to resources to the user, taking into account their current role.) "

" an authentication plugin does not set privileges. neither does an authorization plugin. an authentication plugin authenticates identity. an authorization plugin authorizes access. "

" i am saying that when a user connects, an authentication plugin says "this is the user <x>, her default role is <y>" (perhaps because during the connection, the user has specified the right username and password, or supplied the right token, or just because it is monday). when a "set role <z>" is done, the server asks the authentication plugin "can <x> set role <z>" and the plugin either says yes or no.*

the "authentication plugin" may simply be one that looks at the mysql.* tables to determine that. that is, our "built-in" authentication scheme would be no different from a plugin.

when the user does something that requires an authorization check, the server then asks an authorization plugin "can <x> (who has current role

<r>) do

to <q>" and the plugin either says yes or no. the "authorization plugin" may simply be one that looks at the mysql.* tables to determine that. that is, our "built-in" authorization scheme would be no different from a plugin. at no point does a plugin ever issue any sort of sql command. one thing to note is that an authorization plugin may simply not know about a given user <x>. that is, our theoretical ldap authentication plugin may have said "this is jimw@mysql.com, his default role is 'dba'" and the authorization plugin may say "i don't know this jim guy from adam, but because his role is 'dba', he may certainly perform a tonsilectomy on that table!"

• i have made an implementation assumption here. another way to do it would be for the authentication plugin to say, during connection, "this is user <x>, with default role <y>, and they can also set role to <a>, , or <c>".

"