Recently I worked on a project to implement RBAC in MySQL which I hope to slowly evolve until it can be integrated into the main MySQL branch.
Presentation: Slides
Download: rbac-mysql.tgz
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).
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 ] [-l ]
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.
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
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.
We added several commands to manage roles such as
CREATE ROLE
DROP ROLE
ADD TO ROLE USER @
DROP FROM ROLE USER @
GRANT … TO ROLE
So, when a user uses the mysql client to login they pass a list of roles. This list gets passed to the server which tries to ensure the user, from that host, is allowed to activate the roles. If any role fails, the login fails. We then add the list of roles to the security_context field of the THD class. I would be interested to know if there is a better way of doing this, since it gave us some trouble.
Next, we tried to find all points where authorizations are checked. I don’t have a list of all the places we place RBAC function calls, but I can easily make one. So, for example, the acl_get has been split into two function calls: acl_get_dac and acl_get_rbac. acl_get_dac is the acl_get that currently exists. acl_get_rbac returns the global permissions for the activated roles. We then return the disjunction of all of the permissions. ie
ulong acl_get( … )
{
return acl_get_dac( … ) || acl_get_rbac( … );
}
We could easily have chosen to do this a different way, but it was the interpretation we chose. I’m not sure how Oracle handles roles.
We placed similar parallel calls in most authorization functions, and always took the disjunction. We also modified show grants to display a list of the activated roles, and the permissions assigned to each activated role.
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::.
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.
There are also some features that we don’t think work correctly at all, such as stored procedures and views. If the user creating them required their active roles’ permissions, I’m not sure what behavior our code has.
Finally, I should mention that everything wasn’t tested enough for it to be used in any form of production system.
