Hi!
First - a summary.
We, in MySQL, are usually more interested in implementing features that
are part of the SQL standard, than non-standard extensions.
SQL standard specifies what a "role" is, how it behaves, what properties it has,
and what statements exist to manipulate roles.
That is, although the functionality is mostly the same as you've
implemented, the syntax is close but different.
See below.
On Mar 02, Ian Molloy wrote:
> We implemented core RBAC (RBAC-0) which includes just user-role
> assignment (UA), and role-permission assignment (PA). All permissions
> were stored in tables in the mysql database, in a very similar manner
> to what is currently done. For those familiar with the RBAC
> standards, we did not implement any type of sessions (which is in
> RBAC-0 but rarely implemented), role hierarchies (RBAC-1, which I'd
> like to add), or constrains (RBAC-2, which would be a far off goal).
This is ok.
> The mysql client was augmented to include a flag followed by a list
> of roles the user wishes to activate when they log in.
> $ mysql [-u <username>] [-l <role1,role2,...rolen>]
Please no, these's SET ROLE for this and default role. See below.
> We didn't implement any form of role activation or deactivation, so
> it could be argued that this is how we chose to handle sessions. This
> could be one area to change.
Yes. There's a standard statement
SET ROLE
see below.
> We added six tables to the mysql database:
> rbac_ua: User, Host, RoleName
> rbac_pa: RoleName, Privileges
> rbac_db: Db, RoleName, Privileges
> rbac_tables_priv: Db, RoleName, Table_name, Privileges
> rbac_columns_priv: Db, RoleName, Table_name, Column_name, Privileges
> rbac_procs_priv: Db, RoleName, Routine_name, Routine_Type, Privileges
But roles exist in the same namespace as usernames. Why not to put them
in existing tables user/db/tables_priv/columns_priv/procs_priv ?
> We tried to keep everything as close to the standard permission
> tables as possible, but you can see we simplified some. For example,
> the tables, columns, and procs privileges tables omit the Grantor and
> Timestamp fields. We also only user user@host to authorize a user to
> activate a role at login; for the final version, I think x509 and
> other mechanisms would be desirable.
You'll have it automatically if roles will be stored together with
users.
> We added several commands to manage roles such as
> CREATE ROLE <role_name>
ok.
> DROP ROLE <role_name>
ok
> ADD TO ROLE <role_name> USER <user_name>@<host_name_or_ip>
should be
GRANT <role_name> TO <user_name>@<host_name_or_ip>
> DROP FROM ROLE <role_name> USER <user_name>@<host_name_or_ip>
REVOKE <role_name> FROM <user_name>@<host_name_or_ip>
> GRANT ... TO ROLE <role_name>
Simply
GRANT ... TO <role_name>
(same namespace, remember ?)
> Code wise, we placed virtually all of our added code in a mysql_rbac.
> {h,cc} file and created an RBAC namespace. This made it easy for us
> to see the places we had added code by searching the standard mysql
> files for RBAC::.
Good.
> That's a quick overview of what we were able to accomplish. I'm open
> to suggestions on what should be added and what should be changed.
> There are some things that should probably change. First is likely to
> be the authentication and role activation would be nice if it
> supported the other mechanisms that normal login supports. Also, we
> probably don't want to modify the client.
Here're few excerpts from our internal TODO list - the task "Roles"
(note - it assumes that roles will be in mysql.user table):
========================
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.
DROP ROLE
---------
Syntax:
DROP ROLE [ IF EXISTS ] role_name
Required privileges: either you must have CREATE ROLE privilege, or you
must have DELETE privilege for the mysql.user table.
GRANT privilege TO role_name
----------------------------
Syntax:
As in the MySQL Reference Manual,
http://dev.mysql.com/doc/refman/5.1/en/grant.html
GRANT role_name TO user_name
----------------------------
To grant a role to a user:
GRANT role_name TO <user name>@<host_name_or_ip>
GRANT role_name TO role_name
----------------------------
To grant a role to a role:
GRANT role_name TO role_name
(optional feature)
CREATE ROLE privilege
---------------------
A privilege that allows one to create and drop roles.
GRANT CREATE ROLE ON *.* TO <user name>@<host_name_or_ip>
REVOKE
------
A REVOKE counterpart for every GRANT statement above:
REVOKE privilege FROM role_name
REVOKE role_name FROM <user_name>@<host_name_or_ip>
REVOKE role_name FROM role_name
REVOKE CREATE ROLE ON *.* FROM <user name>@<host_name_or_ip>
SET ROLE
--------
Syntax:
SET ROLE { role_name | NONE | DEFAULT }
Required privileges: a role must be granted first.
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, width,
character set, and collation are the same as for CURRENT_USER.
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 or UPDATE on mysql.user table. We're
altering a user setting, and all user changes need a CREATE USER
privilege.
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.
========================
Hope this helps.
Regards / Mit vielen Grüssen,
Sergei
--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik <serg@stripped>
/ /|_/ / // /\ \/ /_/ / /__ Senior Software Developer
/_/ /_/\_, /___/\___\_\___/ MySQL GmbH, Radlkoferstr. 2, D-81373 München
<___/ Geschäftsführer: Hans von Bell, Kaj Arnö - HRB
München 162140
