WL#988: Roles
Affects: Server-7.0 — Status: Assigned — Priority: Low
MySQL will implement roles.
A role is a named collection of privileges. One can grant roles, grant
to roles, create roles, drop roles, and decide what roles are applicable
("enabled") during a session. Other DBMSs support roles, and there is an
ANSI/ISO specification.
Note: See WL#3574 "Online backup: SHOW commands for various metadata"
and WL#3582 "Online Backup: Add SHOW commands for needed metadata".
When implementing this statement, remember to also implement
SHOW CREATE ROLE.
Compatibility
-------------
Other DBMSs supporting roles are:
-- SQL Server 2005
The syntax is non-standard, everything must be done via stored procedures,
e.g. sp_addrole.
-- DB2 version 8
The word "role" does not appear in DB2's documentation, but there are two types
of grantee: "U" (user) and "G" (group). One can grant to groups and users can
associate with groups. But again the syntax is non-standard. A forthcoming
version of DB2 9 will have more standard syntax.
DB2 9.1 for z/OS has a CREATE ROLE statement:
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db29.doc.sqlref/db2z_sql_statementsintro.htm
-- Oracle10g
Close to standard syntax. Oracle supports CREATE ROLE, DROP ROLE, GRANT <role>,
etc. So this can be one practical model.
-- MaxDB
Close to standard syntax. MaxDB supports CREATE ROLE, DROP ROLE, GRANT <role>,
etc.
NB: MaxDB, like its relative Adabas, has "CREATE USERGROUP ..." and other
statements for usergroups. Usergroups are not roles but there is a bit of
overlap, because one could put a user in a usergroup, and then the user would
have the usergroup's capabilities. But usergroup support is not part of
this "roles" specification.
I will describe what I think are the minimal requirements for compatibility
with SQL:2003 non-core features T331 "Basic roles" and T332 "Extended roles".
Where Oracle (which usually in practice means "both Oracle and MaxDB") has a
major extension, I will so note, but Oracle compatibility is an optional
extra.
Our main object is compatibility with other DBMSs, but meeting current user
demands is a side effect that we can hope for. As PeterZ puts it:
"I've heard plenty of questions about simplifying user management in MySQL.
Currently the answer is to use a script if you need to change permissions for a
group of users, which is not very robust."
Features which we will not implement
------------------------------------
For CREATE ROLE:
-- [standard optional extension]
The full ANSI/ISO syntax is:
CREATE ROLE <role name> [WITH ADMIN <grantor>]
The idea of the WITH ADMIN clause is: the <grantor> can be either CURRENT_ROLE
or CURRENT_USER. The clause decides which of the two has the administrative
privileges on the role. We will not implement this.
-- [Oracle optional extension]
Oracle also has a statement, ALTER ROLE, which is useful for changing the name
of a role or the password. We will not implement this.
-- [Oracle / MaxDB optional extension]
There can be one more clause, for passwords:
CREATE ROLE <role name> IDENTIFIED BY <password>
where <password> is the authorization that a user must enter to get the role.
The password should be a character string because that is what MySQL uses for
creating users. We will not implement this.
For SET ROLE:
-- [Oracle / MaxDB optional extension]
Suppose we associated a role with a password when we said CREATE ROLE. In that
case, the necessary SET ROLE syntax is:
SET ROLE <role name> IDENTIFIED BY <password>
Although it might seem reasonable that peter could say "SET ROLE X IDENTIFIED
BY password" even if nobody has granted role X to peter, that isn't so: even if
you use passwords, you still need grants. We will not implement this.
-- [Oracle / MaxDB optional extension]
Instead of just one role name, it is possible to set many, up to a settable
limit. The syntax is any of:
1. SET ROLE ... ALL [EXCEPT <role name>]
2. SET ROLE NONE
3. SET ROLE <role name> [,<role name>...]
We will not implement this now. We will want to implement it in the future.
When we do implement it, we will have to decide how CURRENT_ROLE should look
(that is, is it an array, a concatenated string, or something else).
For SET DEFAULT ROLE:
-- In MaxDB one can accomplish a similar effect (to SET DEFAULT ROLE) with
ALTER USER (not CREATE USER) with this syntax:
ALTER USER <user name>
...
DEFAULT ROLE ALL [EXCEPT <role name>]
DEFAULT ROLE NONE
DEFAULT ROLE <role name> [IDENTIFIED BY <password>]
We will not implement this.
For Predefined Roles:
---------------------
Some DBMSs have built-in roles with fixed names, for example a 'database
administrator' role or a 'guest' role. If we wanted to emulate a particular
DBMS, we would have to have the same built-in roles. But we will not do so.
We have examined the question of PUBLIC. Technically PUBLIC is not a role, but
it has similar characteristics: it is a named package of privileges. We decided
not to implement PUBLIC as a predefined role. It remains a separate worklog
task.
PeterZ had this idea: possibly if we want PUBLIC we can just create appropriate
rules the normal way, that is, in a migration support script. That won't be the
same as a predefined PUBLIC role (a true built-in role can't be DROPped) but
it's an idea for a temporary substitute.
Related to dev-private discussion "Identifier equivalence and events":
"Identifiers are equivalent if their upper-case forms are
equal according to a binary collation with space padding."
Contents
--------
CREATE ROLE
Notes about role names
DROP ROLE
GRANT privilege TO role_name
GRANT role_name TO user_name
Who has GRANT role_name privileges?
GRANT role_name TO role_name
GRANT CREATE / ALTER / DROP ROLE
REVOKE
SET ROLE
Big Example
CURRENT_ROLE
ALTER ROLE
RENAME ROLE
SHOW PRIVILEGES
SET DEFAULT ROLE
The Initial State
SHOW
INFORMATION_SCHEMA
Command line options
Plan For Replication
Stored Procedures and Definer Privileges
Logging in with a Role Name
DML statements and role privileges
WL#1054 Pluggable Authentication Support
Glossary
Some implementation comments
The Decisions about Options
References
CREATE ROLE
-----------
Syntax:
CREATE ROLE [ IF NOT EXISTS ] role_name
Required privilege: either you must have CREATE ROLE
privilege, or you must have INSERT privilege for the
mysql.user table.
Effect: A new role is created. A new row is inserted
in mysql.user, with these column values:
(Host) '%', (User) role name, (all others) same as
for CREATE USER. There will also be a way to distinguish
between roles and users, that's up to the implementor.
Oracle allows "CREATE ROLE ... IDENTIFIED BY password etc.".
We won't implement something like that, because:
- Monty doesn't want it (see section "The Decisions About Options").
Examples:
CREATE ROLE role1;
CREATE ROLE `Top Engineer`;
The optional "[IF NOT EXISTS]" clause isn't allowed
for CREATE USER. Sergei Golubchik and Trudy Pelzer
like CREATE ROLE [IF NOT EXISTS], so it's in.
But other votes are welcome.
Notes About Role names
----------------------
Roles are objects, so they should follow the same naming rules that we use
for tables etc. -- someday. Not now.
Role names are not "per host" so there is no need for "role_name@click.net".
There is only one "role_name", which is for all hosts.
Role names are not "within schema", so there is no need for "database1.role1".
There is only one "role_name", which is for all catalogs/schemas/databases.
Role names and User names share the same namespace, so:
- If there is a user named 'peter@click.net', then there
cannot be a role named peter -- ignore user's host name
when checking for this.
- Role name cannot be qualified
- Maximum length is 16 UTF8 characters, which is much less
than the maximum length of a table identifier (64)
(The mysql.user.User column is CHAR(16) CHARACTER SET UTF8
COLLATE UTF8_BIN although WL#3125 proposes changing it.)
- Users will cause confusion if they create roles named
`%` or `` or `NONE` or `PUBLIC` or `EVENT_SCHEDULER`,
but we won't disallow them.
- If there is already a role or a user with the same
name, return an error with the message:
Operation CREATE ROLE failed for 'role_name'
However, the User-like name x@y is illegal for
roles.
DROP ROLE
---------
Syntax:
DROP ROLE [ IF EXISTS ] role_name
Required privileges: either you must have DROP ROLE
privilege, or you must have DELETE privilege for the
mysql.user table.
If the role_name does not exist, or if you don't
have appropriate privileges to drop, return error
ERROR xxxx (42000): Operation DROP ROLE failed for 'role_name'
On DROP ROLE, the privileges will be modified for the next
command that the user executes.
Don't allow RESTRICT or CASCADE.
An automatic "REVOKE role_name FROM user_name" must occur
for every user who has role_name enabled at the time of the DROP.
Example:
DROP ROLE role_name_1;
The optional "[IF EXISTS]" clause isn't allowed
for DROP USER. The limitation to a single
role_name isn't as in DROP USER, which allows
"user_name [, user_name ...]". Sergei Golubchik
and Trudy Pelzer like DROP ROLE [IF NOT EXISTS]
role_name, so it's in. But other votes are welcome.
GRANT privilege TO role_name
----------------------------
Syntax:
As in the MySQL Reference Manual,
http://dev.mysql.com/doc/refman/5.1/en/grant.html
but changing the word "user" to "role". That is,
- If you can grant a privilege to a user, then you
can grant a privilege to a role.
- The privilege change takes effect for the next
statement that any affected user executes.
(A user is affected if he/she has an enabled role
that is affected by the privilege change.)
(A role is enabled if the user has "enabled"
with a SET ROLE statement, described later.)
Some GRANT/REVOKE syntax doesn't apply for roles,
for example MAX_USER_CONNECTIONS. The rule is:
if a GRANT or REVOKE clause affects the way that
a connection occurs, then it is irrelevant for a
role, because you can't connect with a role_name.
So either ignore the clause or accept it and change
the mysql.user table, with no error message.
With Oracle, you can't grant REFERENCES privilege
to a role. We don't care about REFERENCES at this
moment, but if it ever becomes a "real" privilege (WL#4099)
we'll have to decide if we want the same restriction.
Examples:
GRANT ALL PRIVILEGES ON Table1 TO sys_role;
GRANT UPDATE (col1) ON Table1 TO sys_role2 WITH GRANT OPTION;
GRANT role_name TO user_name
----------------------------
To grant a role to a user:
GRANT role_name ON *.* TO <user name>
In MySQL terms, a role_name is a "Global Level" privilege.
Therefore one says "GRANT/REVOKE role_name ON *.* ...".
In standard SQL there would be no "ON *.*" clause.
GRANT ALL [PRIVILEGES] does not include any role_name privileges.
It is okay to grant multiple role_names, or a
combination of role_names and other global-level privileges,
to multiple users.
Effect: the user's privileges are modified for the next
SET ROLE statement that the user executes.
It is unacceptable to treat roles inside GRANT statements as macros. For
example: if sys_role has "UPDATE ON t2" privilege, then it may be tempting
to transform:
GRANT sys_role ON *.* TO peter@click.net;
into:
GRANT UPDATE ON t2 TO peter@click.net;
This will not be acceptable, because later, if we say "DROP ROLEsys_role",
we do not know whether peter@click.net should lose his update privilege.
Besides, "GRANT sys_role" doesn't immediately cause peter@click.net
to get "UPDATE on t2" privileges, unless sys_role is an "enabled"
role (see SET ROLE statement and Glossary).
In standard SQL, "GRANT role_name ..." statements may have a
WITH ADMIN OPTION clause that means the same thing as a WITH
GRANT OPTION clause. We will not implement this.
Examples:
GRANT sys_role ON *.* TO peter@clickhost.net;
GRANT engineering, SUPER ON *.* TO joan, sally;
Who has "GRANT role_name" privileges?
-------------------------------------
Peter can say "GRANT role_name" if somebody (ultimately, if root)
said "GRANT role_name ON *.* TO Peter WITH GRANT OPTION".
There will be disagreement about this.
Some People will say that, if Peter said CREATE ROLE Role1,
Peter should automatically have GRANT Role1 privilege.
The analogy is with the way that we grant for routines.
This is what would happen with Oracle.
Other People will say that, no, the only way for Peter
to get GRANT Role1 privileges if somebody grants to Peter.
The analogy is with the way that we grant for tables.
If the decision is to change the specification and support
Some People, we would have to answer these questions:
Suppose you have CREATE ROLE privilege as a result of the
fact that your enabled role has a CREATE ROLE privilege.
In that case, why should CURRENT_USER get GRANT Role1
privilege? Why shouldn't CURRENT_ROLE get GRANT Role1 privilege?
Or, maybe some other enabled role should get it?
If the holder of the CREATE ROLE privilege gets the privilege
of everything that is ever granted to any role he/she creates,
then he/she can grant those (role) privileges to CURRENT_USER,
which in effect means granting new privileges to oneself.
Should this be prevented?
GRANT role_name TO role_name
----------------------------
To grant a role to a role:
GRANT role_name ON *.* TO role_name
Effect: the user's privileges are modified for the next
statement that the user executes, if the user has
"enabled" the role (enabling is described later).
This GRANT variant allows us to make roles within roles,
or in more common terminology "contained roles" /
"applicable roles".
If role C has been granted role A, then role C has all the privileges of A.
And if A gets a new privilege, then C gets that privilege too. But it's worth
emphasizing that C only has the privilege indirectly.
There must be a guard against cycles. That is, if you say
"GRANT role1 ON *.* TO role2" then you shouldn't be able to say
"GRANT role2 ON *>* TO role1". Return an error message:
"Cyclical GRANT".
Example:
GRANT sys_role2 ON *.* TO sys_role1;
The "GRANT role_name ON *.* TO role_name" statement is likely to cause
lots of trouble. The implementor may decide to declare a fixed
maximum depth for the number of levels of roles within roles
within roles. The implementor has the option of cancelling
the "GRANT role_name ON *.* TO role_name" feature at any stage of the
project.
GRANT CREATE / ALTER / DROP ROLE
--------------------------------
Syntax:
GRANT CREATE ROLE ...
GRANT DROP ROLE ...
The CREATE ROLE privilege works for CREATE ROLE.
The DROP ROLE privilege works for DROP ROLE.
The ALTER ROLE privilege is unnecessary, we don't allow ALTER ROLE.
There will be some disagreement about this.
Some People will say that there should only be one privilege,
CREATE ROLE, for both CREATE ROLE and DROP ROLE. The closest
analogy (I suppose) is the CREATE USER privilege.
The CREATE USER privilege works for CREATE USER, DROP USER, RENAME
USER, and REVOKE ALL PRIVILEGES.
Other People will say that "Monty probably would want
multiple fine-grained privileges" and will point to the task
WL#2227 "New privileges for new objects"
which got stuck in raw idea bin.
When a user upgrades to MySQL 6.x, somebody must have CREATE ROLE
privilege. So change mysql_fix_privilege_tables, ensuring that
anybody who has the CREATE USER privilege will have the CREATE ROLE
privilege, initially. Notice that mysql_privilege_tables.sql
already gives CREATE USER privilege to anyone who has GRANT
privilege, therefore anybody who had GRANT privilege in an
earlier version will have CREATE ROLE privilege in MySQL 6.x.
As well as changing mysql_fix_privilege_tables, change the
initial privilege list in Windows too.
Example:
GRANT CREATE ROLE ON *.* TO Peter;
REVOKE
------
Whatever GRANT syntax is allowed, there must be a corresponding REVOKE syntax,
thus:
REVOKE <privilege> ON *.* FROM role_name
and
REVOKE role_name ON *.* FROM <user>
and
REVOKE role_name ON *.* FROM role_name
and
REVOKE CREATE ROLE ON *.* FROM user_name | role_name
and
REVOKE DROP ROLE ON *.* FROM user_name | role_name
The current user's privileges will be modified for the next statement
that the user executes, if the role is "enabled" ("enabled" is
defined later).
The statement
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user_name | role_name
does not revoke roles.
SET ROLE
--------
Syntax:
SET ROLE { role_name | NONE | DEFAULT }
It is permissible to use a string literal or variable for role_name:
SET ROLE 'engineering'
SET ROLE @engineering
Privileges required: role_name. That is, if peter says
SET ROLE role1;
there will be an error return unless somebody said earlier:
GRANT role1 ON *.* TO peter;
ANSI/ISO says return error "invalid role specification"
(sqlstate = '0P000') if you say "SET ROLE role_name"
and role_name is not valid, or you say "SET ROLE role_name"
and no such role has been granted to current user.
Oracle says return error "ORA-06565: cannot execute SET
ROLE from within stored procedure" if you try SET ROLE
within a stored procedure (for explanation see later
section "Stored Procedures and Definer Privileges").
Effect: SET ROLE role_name causes role_name to be the
"current role". If role_name "contains" other roles,
that is, if there was an earlier "GRANT other_role
ON *.* TO role_name", then the contained roles become
"enabled roles".
Effect: SET ROLE NONE disables the current role, if any.
At the beginning of a session, the current role is NULL.
After SET ROLE NONE, there are no enabled roles for the
current user. NONE is a new reserved word.
Effect: SET ROLE DEFAULT is the same as SET ROLE role_name
where role_name is a default role_name assigned to the
current user by a SET DEFAULT ROLE statement,described
later. DEFAULT is a new reserved word.
Effect: the current user gets a UNION ALL of privileges
of all enabled roles. That is:
all the privileges granted directly to the current user,
plus all the privileges granted to role_name,
plus all the privileges granted to roles which were
granted to role_name ("contained roles"),
plus all the privileges granted to roles which were
granted to roles which were granted to role_name,
and so on.
Effect: if the current user had privileges via previous
enabled roles, those privileges are revoked.
Oracle allows:
SET ROLE role1 [, role2 ...]
That is, with Oracle you can have multiple roles
We will not implement this because:
- It's not required by the standard and not implemented
by the other DBMS (Informix) which supports SET ROLE in a
standard-compatible way
- It would make CURRENT_ROLE meaningless
- Monty doesn't want it (see section "The Decisions About Options").
Big Example
-----------
User root says:
CREATE ROLE Role1;
CREATE ROLE Role2;
CREATE USER Peter;
GRANT SELECT ON t TO Role1;
GRANT INSERT ON t TO Role2;
GRANT UPDATE ON t TO Peter;
GRANT ROLE Role1 ON *.* TO Peter;
GRANT ROLE Role2 ON *.* TO Role1;
Peter starts and says:
SELECT * FROM t;
This fails! Saying "GRANT ROLE role1 ON *.* TO Peter" is not enough.
The only privilege that Peter has is UPDATE.
Peter says:
SET ROLE Role1;
This succeeds. There was a "GRANT ROLE Role1 ON *.* TO Peter".
Now Peter has three privileges: INSERT, UPDATE, SELECT.
Peter says:
SELECT * FROM t;
This succeeds. Peter can select because his
enabled role allows select.
Peter says:
SET ROLE Role2;
This fails! Role2 is "contained in" Role1, so Peter can
hold all the privileges of Role2, but only indirectly.
Peter can only say SET ROLE Role2 if there's a direct
grant. (Oracle allows SET ROLE for indirect grants; we don't.)
User root says:
GRANT ROLE Role2 ON *.* TO Peter;
Peter says:
SET ROLE Role2;
This succeeds. There was a "GRANT ROLE Role2 ON *.* TO Peter".
At this point, Peter loses his SELECT privilege because
he held SELECT privilege via Role1, and Role1 is no longer
enabled. But Peter still has INSERT and UPDATE privileges.
User root says:
GRANT ROLE Role1 ON *.* TO Role2;
This fails. It would be a cycle -- Role1 contains Role2,
and Role2 contains Role1.
User root says:
REVOKE INSERT ON t FROM peter;
This fails. Peter holds the privilege, but the only way
to take it away from him is to say "REVOKE INSERT ON t
FROM Role2".
User root says:
DROP ROLE Role2;
Well, okay, there's another way. Now that Role2 is gone,
Peter loses the INSERT privilege. Now he only has SELECT.
And Peter's "current role" is once again NULL.
Peter says:
SET ROLE NONE;
This does nothing, since there is no current role. There
is no warning if you SET ROLE to a setting that's already
in effect.
CURRENT_ROLE
------------
Syntax:
CURRENT_ROLE
This is a function which returns the current
role, or NULL if there is no current role.
This is analogous to the CURRENT_USER function.
The data type, length, character set, and collation
are the same as for CURRENT_USER.
CURRENT_ROLE is a new reserved word.
Example:
SELECT CURRENT_ROLE;
ALTER ROLE
----------
Syntax:
ALTER ROLE role_name ...
Oracle has an ALTER ROLE statement for changing
the IDENTIFIED BY clause.
We will not implement this.
RENAME ROLE
-----------
Syntax:
RENAME ROLE role_name TO role_name;
RENAME is not required by ANSI/ISO and not in Oracle.
But we have a RENAME USER statement, so we had to
consider RENAME ROLE.
If users are renamed, any storage of "role name to
user name" references will be adjusted.
We will not implement this.
SHOW PRIVILEGES
---------------
The output from SHOW PRIVILEGES will contain these columns:
Privilege | Context | Comment
-------------+--------------+--------
Create role | Server Admin | To create new roles
Drop role | Server Admin | To drop roles
SET DEFAULT ROLE
----------------
Syntax:
SET DEFAULT ROLE role_name TO user_name [,user_name...]
or
SET DEFAULT ROLE NONE TO user_name [,user_name...]
The idea of a "default role" is: if user joe has default role X,
then when joe connects there is, in effect, an implicit
"SET ROLE X" execution.
Privileges required: CREATE USER. We're altering a
user setting, and all user changes need a CREATE
USER privilege. (Perhaps also the current user
should have the right to change his/her own defaults.)
SET DEFAULT ROLE NONE is unnecessary for new users,
because it's the initial state. That is, after you say
CREATE USER Sally;
user Sally has no default roles. We do not plan to
implement a DEFAULT ROLE clause for the CREATE USER
statement.
You do not need privileges on role_name to say
"SET DEFAULT ROLE role_name", you don't even need
to know whether role_name exists. However, when the
grantee user connects again and the implicit
"SET ROLE X" occurs, the required privileges for SET ROLE
are checked. If the implicit SET ROLE fails, there is no
warning at connect time.
Monty wants this (see section "The Decisions About Options").
We will not implement the Oracle syntax:
ALTER USER user_name DEFAULT ROLE role_name;
Also, Oracle's privilege checking is different: you would
need an ALTER USER privilege, and the checking for
"whether the user can have the role" is checked at
ALTER USER time, not at connect time.
We will not implement the Oracle side effect:
(quoting from Oracle documentation)
"When you grant a role to a user, the role is granted
as a default role for that user and is therefore enabled
immediately upon logon."
The Initial State
-----------------
The following statement happens, in effect, whenever
a user connects, and for mysql_change_user():
SET ROLE DEFAULT;
So, if the user has no default role, then the initial value
of CURRENT_ROLE is NULL and there are no enabled roles.
SHOW
----
SHOW GRANTS FOR role_name must work the same way that SHOW GRANTS FOR
user_name works.
Example:
GRANT SELECT ON t TO role_x;
GRANT role_x ON *.* TO peter;
SHOW GRANTS FOR 'peter';
The display will include the directly-granted role_x privilege:
GRANT SELECT ON `mysql`.`t` TO 'x'@'%'
The display will not include the indirectly-granted SELECT privilege.
We will not extend SHOW so that a user can see what roles have been
granted to him/her, or what roles are enabled, or what role is current,
or what users' default roles are.
Note: See WL#3574 "Online backup: SHOW commands for various metadata"
and WL#3582 "Online Backup: Add SHOW commands for needed metadata".
When implementing this statement, remember to also implement
SHOW CREATE ROLE.
INFORMATION_SCHEMA
------------------
We should have an entry for roles in information_schema eventually. The
views that may be necessary are APPLICABLE_ROLES, ENABLED_ROLES,
APPLICABLE_ROLES table:
The roles that have been GRANTed to a user.
If role1 is contained in role2, then both roles appear.
GRANTEE
ROLE_NAME
IS_GRANTABLE
ENABLED_ROLES table:
The current role, and all roles applicable to it, at any level.
If the user never said SET ROLE, then this table will be empty.
If role1 is contained in role2, then both roles appear.
GRANTEE
ROLE_NAME
IS_GRANTABLE
If you have a role, and the role has "SELECT on t", then a search
of information_schema privilege tables will show that you have
"SELECT on t". But it can't be granted or revoked. (Actually it might be
better if we didn't show the SELECT privilege, and I think we
can leave this detail up to the implementor.)
Although metadata is available, roles are not in
databases and therefore mysqldump won't show them.
Command line options
--------------------
Several programs, including mysqld, have a
command line option "u user_name" or
"--user=user_name". There's no way to start these
programs with an indication of the starting role,
that is, there's no "r role_name" or
"--role=role_name". Keep it thus.
Adding a way to specify roles on program
startup will only cause people to demand
something similar for other connections,
e.g. DBI->connect($dsn, $user, $password, $role);.
Or they'd want a new ROLE environment variable
along with the USER environment variable.
Instead, depend on default roles. If it's necessary
for user Peter to to have role X for mysqladmin
purposes, then "SET DEFAULT ROLE X TO Peter" before
he runs mysqladmin.
Plan For Replication
--------------------
The plan is to ignore replication till the last minute.
We know that SET ROLE must be logged, and we suspect that
in addition to "--master-user=username" we might need
"--master-role=role_name". Checking with the replication
people, after implementation commences, is part of this
task.
Plan for Backup
---------------
Roles are mentioned in the list of objects in
WL#3713 Online backup: Selective rpl/backup of metadata.
Stored Procedures and Definer Privileges
----------------------------------------
When a user enters a trigger or routine defined with
SQL SECURITY DEFINER, disable the current role
(there is an implicit SET ROLE NULL here) and
disallow SET ROLE. So within the routine the value of
CURRENT_ROLE is always NULL and all of the current user's
enabled ROLE privileges are disabled. On exit from
the routine, CURRENT_ROLE is restored and roles are
enabled again.
This is non-ANSI. Let's justify it as Oracle compatible.
Oracle's manual says:
"All roles are disabled in any named PL/SQL block (stored
procedure, function, or trigger) that executes with
definer's rights. Roles are not used for privilege checking
and you cannot set roles within a definer's rights procedure."
"If a named PL/SQL block that executes with definer's rights
queries SESSION_ROLES, the query does not return any rows."
(Oracle's SESSION_ROLES is equivalent to our CURRENT_ROLE.
They have a table because they allow one user to
have multiple current roles.)
Example:
The root user says:
CREATE USER Joe;
CREATE ROLE Role1;
CREATE TABLE t (s1 INT);
GRANT INSERT ON t TO Role1;
GRANT CREATE ROUTINE ON *.* TO Joe;
GRANT EXECUTE ON *.* TO Joe;
GRANT ROLE Role1 ON *.* TO Joe;
Joe says:
SET ROLE Role1;
This succeeds. At this point, if Joe says "INSERT
INTO t VALUES (0)", he'll have no problem, he has the right.
CREATE PROCEDURE pj () SQL SECURITY DEFINER INSERT INTO t VALUES(0);
CALL pj();
This fails! Joe has no right to INSERT into t.
Logging In With A Role Name
---------------------------
In standard SQL, it's okay for a client program
to connect using a role name instead of a user
identifier. In that case, CURRENT_USER is NULL
and CURRENT_ROLE is role_name.
We will not implement this.
We also will not allow the standard-SQL syntax
GRANT ... [ GRANTED BY CURRENT_ROLE ]
or
CREATE ROLE ... [ WITH ADMIN CURRENT_ROLE]
We also will not allow MySQL's "DEFINER="
clause to refer to a role name -- it must
refer to a user name.
The net effect is that roles cannot be the
current "authorization identifiers", and
can't be the definers of any objects.
I hope that makes the task simpler.
It doesn't hurt Oracle compatibility.
DML statements and Role Privileges
----------------------------------
Oracle has a restriction: if you have a DML
privilege via a role, you can't use the
privilege during CREATE/ALTER. For example:
if there's a SELECT privilege to check during
CREATE VIEW, that privilege must be granted
to the current user, not just via a role.
This means you'd have to check if a privilege
came via a role or not.
We will not have this restriction.
WL#1054 Pluggable authentication support
----------------------------------------
Changes to WL#1054 may affect this task (WL#988),
but I believe we should implement Roles now, and
add a new task "Pluggable authentication support
and Roles" in a few years.
That's unlikely, though. Some People think that
"Pluggable authentication support" comes logically
before "Roles". See also WL#3118 "Pluggable
authorization support".
Glossary
--------
Applicable Role. If user1 says "GRANT role1 ON *.* TO user1", then role1
is applicable to user1. That is, a user's applicable roles include
the roles that the user can use with a SET ROLE statement.
Contained Role. If you say "GRANT role1 ON *.* TO role2" then role2 "contains"
role1. If you then say "GRANT role1 ON *.* TO Peter", then Oracle would say
that Peter holds role1 "indirectly" or "through other roles", but let's
not use those terms. If role1 is "contained" in role2, then it is
"applicable" to role2.
Current Role. If you say "SET ROLE role1" then role1 is the "current role"
and "SELECT CURRENT_ROLE" will return "role1". Initially the current role
is DEFAULT and initially the DEFAULT is NONE, i.e. SET CURRENT_ROLE=NULL.
The current role can change to NULL when you enter a routine.
Enabled Role. The current role, plus any role which is "contained" in
the current role. ANSI/ISO has a view INFORMATION_SCHEMA.ENABLED_ROLES.
Role. The ANSI/ISO definition (from the technical corrigendum) is
"A role is a potential grantee and grantor of privileges and other roles.
A role can also own schemas and other objects ... A role is not a schema
object, nor is it a collection of role authorizations." In MySQL's case,
a role cannot own any objects.
Role. The explanation by Jim Melton (from his SQL:1999 book) is
"In essence, a role is an identifier to which you can grant privileges
but that never exists by itself. Instead, you grant roles to specific
authIDs [authorization identifiers], and those authIDs are then able to
operate as though the role's privileges had been granted to them."
Role. My explanation, which is for MySQL but not for ANSI, is
"A Role is like a user (because you can GRANT/REVOKE to/from roles);
a role is like a user (because role names and user names are in the
same namespace); a role is unlike a user (because you can't log in
with a role name or use it in DEFINER=); a role is unlike a user
(because you can GRANT a role so that the grantee gets the privileges
of the role)."
Some implementation comments
----------------------------
This is just a suggestion-box area for people who want to edit the
description to say how the implementation should occur.
Sample comment:
"I think the hard part is to store a "role name" inside a list of grants.
It could probably be done as a pointer or as an ordinal number (the # of
the role row in the user table); however,it would still be the case that
we'd have to allow a lot of space -- unless we had a limit on the number
of roles a user can have." (Actually our proposal now is that a user
can only have one current role, but multiple applicable or enabled roles.)
Roles should be stored together with users, in mysql.user.
Roles are an "administrative" privilege and therefore are always
in mysql.user, not mysql.db or mysql.host
"We are to add 3 additional columns to mysql.user:
Default_role char(16) binary;
User_type char(1) not null default 'U' /* or 'P' for role */
Comment varchar(50)"
Doubtless, if it's a user, we'll need one more column for "what is the
default role".
Monty (paraphrased) says:
To handle the many-to-many relationships between users and roles, we could
have a separate table: mysql.roles. This table would have three columns:
hostname hostname of the user
user name of the user
user_role name of the role that the user has
The Decisions About Options
---------------------------
Here are Monty's opinions on four questions that Peter Gulutzan asked:
(a) do we want passwords?
NO
(b) do we want defaults?
YES
(c) do we want multiple current roles?
NO (but "syntax we use should allow for this too")
(d) should PUBLIC be a role?
NO
References
----------
Only Oracle and Informix and DB2 for z/OS have "roles" in the sense that we
use in the Glossary, above. Others have the idea that a role is
like a usergroup, so you can become a member of a role or you
can change your privileges to a role's privileges, but you can't
assign roles to users or roles, and setting a role doesn't OR
your privileges with the role's privileges. In other words:
don't use the the other DBMSs as models.
Oracle:
http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_6012.htm#i2066772
SQL Server 2005 CREATE APPLICATION ROLE statement:
http://msdn2.microsoft.com/en-us/library/ms181491(en-US,SQL.90).aspx
SQL Server 2008 CREATE ROLE statement:
http://technet.microsoft.com/en-us/library/ms187936(SQL.100).aspx
Informix CREATE ROLE statement:
http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.sqls.doc/sqls252.htm
"New features in DB2 Viper 2 to help your business grow"
(skip to Section 3. Database roles)
http://www.ibm.com/developerworks/db2/library/techarticle/dm-0706see/
PostgreSQL:
http://www.postgresql.org/docs/8.3/interactive/sql-createrole.html
Firebird:
http://www.ibphoenix.com/main.nfs?a=ibphoenix&page=ibp_60_sqlref#RSf19239
"Bordeaux BOF: privilege tables"
https://intranet.mysql.com/secure/mailarchive/mail.php?folder=5&mail=19600
"CREATE USER and sql-mode: log of IRC chat"
https://intranet.mysql.com/secure/mailarchive/mail.php?folder=4&mail=10461
"Re: Create if not exists, Drop if exists, Drop many"
https://intranet.mysql.com/secure/mailarchive/mail.php?folder=4&mail=12908
"create role statement" (feature request)
http://bugs.mysql.com/bug.php?id=3573
You must be logged in to tag this worklog
|
Votes
Rated 4.00 out of 5 with 1 votes cast.
You must be logged in to vote.
Watches
2 members are watching this worklog
You must be logged in to track this worklog.
Provide Feedback
|
I will implement this in mine Google Summer of Code project.