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.
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".
===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.
===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 our 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.
===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.
(A more up-to-date list of "objects" is in WL#3713.)
"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.
===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"
===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.
===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.
===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.
===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.
===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.
===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.
===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."
===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.
===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.
===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.
===Other DBMSs===
None of the major DBMSs does anything comparable to this.
Pure speculation: maybe InterSystems Cach is the role model.
"
Frequently Asked
Questions About Cach
Advanced Security
"
http://www.intersystems.com/cache/downloads/documentation/cache51/PDFS/ASEC_Adv_Security_FAQ.pdf
It might be what Brian was thinking of when he said we don't
need WL#988. It might be what others were thinking of, although
Mark seems to think that we can apply external roles to
database objects with "mapping", which isn't what these guys
do. In fact they say it's problematic.
Choice quote:
"Are roles in Cach the same as roles in SQL?
No! The roles defined by Cach and those defined by the SQL standard are
not the same. They represent two different authorization models that govern
two different resources. Cach defines read, write, use, and develop
permissions and governs the use of resources defined in the security database.
SQL uses select, insert, update, and delete and applies them to its
own "resources", namely, users, databases, tables, and so on. Moreover,
there are differences between the two approaches that make mapping one into
the other problematic. These are in the way permissions are determined and
when they are revoked. They do, however, share the same underlying
authentication mechanism so that the same name is used for identifying a
user to Cach and SQL."
Later, the "resources" that a user can get "authorization" for are
listed, and they're generally for objects outside the database, or
at the database-to-outside-world boundary -- connections via
console, changing configuration, etc.
===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`.
"
===Meeting Notes===
In "Minutes of the pluggable authentication (WL#1054) discussion"
(for a meeting held in Riga in September 2008) we read:
"Decisions made:
...
* WL#3118, pluggable authorization
- not anytime soon"
https://intranet.mysql.com/secure/mailarchive/mail.php?folder=4&mail=25443
===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.
"
===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).
"
===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 <p> 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>, <b>, or <c>".
"
===Anthology of Statements from Peter Gulutzan===
"
This is a terrible idea.
"
An alternative and preferred solution for the authorization would be for MySQL to handle authorization errors coming back from the storage engine. Returning an error, rather than checking authorization for every operation and every object, would have much better performance.
The capability for MySQL to handle and report errors coming back from the storage engine is a general request. Besides authorization errors, there may be locking errors, hardware errors, or errors that are resultant of external actions on the file.