[Report 1] Informal requirements and to-do list for my project

Below are informal requirements and to-do list for my project

All ‘!!’ marks in this projects identify priority suggestions.

My project can be divided into two logical parts:

Part 1) Developing the way roles’ privileges will be stored in DB including
changes in MySQL server code to load those new privileges and dispatch
them correctly

Part 2) Implementing necessary SQL statements at least closely (at most, completely)
to SQL:2003 standard.

Now details.

Part 1) As one can see from MySQL worklog#988 (http://forge.mysql.com/worklog/task.php?id=988),
roles’ privileges must be stored in the same way as users’ does. So, the following tables must be used to
store roles’ privileges:

mysql.columns_priv;
mysql.proc_priv;
mysql.tables_priv;
mysql.user;

Also at least one additional table is needed to store user-role relationships (and role inheritance, if I’d implement it).

So, necessary changes are:

1.a) Add 5 new columns to mysql.user table:
Default_role char(16) binary;
User_type char(1) not null default ‘U’ /* or ‘P’ for role */
Comment varchar(50)”
/* Next two columns were omitted in worklog#988, but I think they must be here. */
Create_role_priv enum(‘N’,’Y’) character set utf8 NOT NULL default ‘N’
Drop_role_priv enum(‘N’,’Y’) character set utf8 NOT NULL default ‘N’

!! This must be done.

1.b) Add new table mysql.roles as follows
CREATE TABLE mysql.roles(
`Host` char(60) collate utf8_bin NOT NULL default ”,
`User` char(16) collate utf8_bin NOT NULL default ”,
`Role` char(16) collate utf8_bin NOT NULL default ”,
PRIMARY KEY (`Host`,`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT=’Users and roles relationships’;

!! This must be done.

When creating statements operating with roles-to-roles relationship (inheritance/inclusion) a guard against cyclic role grants must be implemented.

!! I will implement role inheritance only if I have enough time for this.

1.c) Modify existing MySQL Server code to correctly load default roles, etc. Propose new memory structures for storing roles’ privileges, if necessary. Also check all routines which have to deal with mysql.user table to make sure the new format of this table won’t break anything.

!! This must be done.

1.d) Add two new tables to INFORMATION_SCHEMA db: APPLICABLE_ROLES and ENABLED_ROLES.
First must contain all roles that were GRANTed to current user, while second must contain active role(s). Both tables could be empty.
Approximate structure of those tables (same for both tables):

CREATE TEMPORARY TABLE `APPLICABLE_ROLES` (
`GRANTEE` varchar(81) NOT NULL default ”,
`ROLE_NAME` varchar(81) NOT NULL default ”,
`IS_GRANTABLE` varchar(3) NOT NULL default ”
) ENGINE=MEMORY DEFAULT CHARSET=utf8;

CREATE TEMPORARY TABLE `ENABLED_ROLES` (
`GRANTEE` varchar(81) NOT NULL default ”,
`ROLE_NAME` varchar(81) NOT NULL default ”,
`IS_GRANTABLE` varchar(3) NOT NULL default ”
) ENGINE=MEMORY DEFAULT CHARSET=utf8;

!! This is an important feature, but I’ll implement it only after more prioritized tasks done.

So, moving to part 2.

Part 2) I will implement the following SQL statements:

2.a) CREATE ROLE [ IF NOT EXISTS ] role_name;
Creates role with specified name. Fails with corresponding error if the role (or user) with such name already exist. Error isn’t produced if IF NOT EXISTS specified.

!! This must be done.

2.b) DROP ROLE [ IF EXISTS ] role_name;
Drops existing role, implicitly invoking “REVOKE role_name FROM user_name” statement for every user having role role_name on the moment of DROP. A question to discuss: what to do with child roles?

!! This must be done.

2.c) GRANT privilege TO role_name;
Grants a privilege to role_name, similar to existing “GRANT privilege TO user_name” statement. Not sure, whether I will need to change anything in this statement at all, except filtering out statements like “GRANT … TO … IDENTIFIED BY …” and so on.

!! This must be done.

2.d) GRANT role_name TO user_name;
Grant a role named role_name to user named user_name respectively.

!! This must be done.

2.e) GRANT role_name TO role_name;
Role inheritance.

!! I will implement role inheritance only if I have enough time for this.

2.f) GRANT CREATE / ALTER / DROP ROLE TO …;
New GRANT statements for new privileges.

!! This must be done.

2.g) REVOKE

Whatever GRANT syntax is allowed, there must be a corresponding REVOKE syntax,
thus:

REVOKE ON *.* FROM role_name

and

REVOKE role_name ON *.* FROM

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

!! This must be done, except for role inheritance; I will implement role inheritance only if I have enough time for this.

2.h) SET ROLE { role_name | NONE | DEFAULT }
Sets active role for current user.

!! This must be done.

2.i) CURRENT_ROLE function
Returns the name of currently active role or NULL if no role is active.