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.

D-1 Definition: AuthorizationID := user_identifier '@' host_identifier | user_identifier

user_identifier and host_identifiers may be quoted to distinguish them from reserved keywords.

D-2 Definition: A role edge is an unique tuple (AuthorizationID, AuthorizationID) which defines an edge in a directed graph as (from role or user, to role). The direction hence represents the search direction in the graph given a particular user (or role) and is the opposite direction to how roles are assigned (a role is assigned to a user or a role).

D-3 Definition: A granted role is a role belonging to a role edge where from-part is the granted user.

D-4 Definition: An account is a row in the mysql.user table which has login properties. In most cases an account is the same as a UserID.

0 Notes on AuthorizationID

R-0 The hostname has some special properties when the AuthorizationID is used with the equal operator which comes from the fact that we assume implicit hostname '%' if none is specified. Example: 'a'='a'@'%' => true

Note: In case the AuthorizationID is used as a User id with login privileges, the hostname will be used to restrict the origin of the client during the authentication phase (according to established practice).

1 Creating or dropping a role.

R-1.1 Both roles and users are AuthorizationIDs. Users are AuthorizationIDs with an additional ability to login to the database using an authentication property. Roles can be users.

Example: `SELECT`@`UPDATE`, `NONE`, `ALL`@`NONE`

R-1.2 The Host field for a role has little meaning unless the account allows for login. In this case it's just as any Host field. Therefore these are legal:

 CREATE ROLE 'x'@'%';    /* same as: create role x; */
 CREATE ROLE a@b;        /* role a is in host 'b' */
 SHOW GRANTS FOR rolename; /* shows 'rolename'@'%' grants */

R-1.3 CREATE / DROP ROLES must be instrumented by P_S

R-1.4 When the statement CREATE ROLE is executed a new row is inserted in mysql.user, with these column values:

  Host: role_host (default '%')
  User: role_name
  Account_locked: 'Y'
  Plugin: default_plugin
  All others fields: same as for CREATE USER.

R-1.5 The user_identifier part of an AuthorizationID (the user name) is case sensitive. The host_identifier part of an AuthorizationID (the host name) is case insensitive.

R-1.6 Maximum length of a user_identifier (the role name) is 32 UTF8 as of MySQL 5.7.8

R-1.8 Attempts to create a new role with a User + Host field combination which already exists must fail with error message ER_ROLE_EXISTS unless the IF NOT EXISTS clause is used. See I-1

R-1.9 To not introduce new grammar conflicts, the following keyword tokens are not allowed as role names: EVENT, EXECUTE, FILE, PROCESS, PROXY, RELOAD, REPLICATION, SHUTDOWN, SUPER, NONE. Except this, all restrictions that apply for user names applies to role names also.

R-1.10 An automatic "REVOKE role_name FROM user_name" must occur for every user who has role_name enabled at the time of the DROP and the privileges will be guaranteed to be modified for the next statement that the user executes.

R-1.11 There is a new privilege: CREATE ROLE. To execute the CREATE ROLE statement, the CREATE ROLE privilege OR the CREATE USER privilege must be present.

R-1.12 There is a new privilege: DROP ROLE. To execute the DROP ROLE statement, the DROP ROLE privilege OR the CREATE USER privilege must be present.

2 Granting / revoking privileges to a role or roles to a user.

R-2.1 Granting privileges to a role does exactly the same as granting privileges to a privileged user account.

R-2.2 Granting a role to a user account creates a role edge which will allow for that user to obtain additional privileges by executing SET ROLE to the granted role name.

R-2.3 Granting the WITH ADMIN OPTION privilege enables a role user to transfer (or revoke) its role privileges to (from) another user or role including the WITH ADMIN OPTION.

R-2.4 When a user logins he will be assigned a default roles as the active role. Any of the default roles that fail to be activated during authentication will generate a warning in the server error log but otherwise be ignored.

R-2.4.1 SET ROLE DEFAULT will throw an error if any of the default roles fail to be activated.

Example:

 mysql> set ROLE DEFAULT;
 ERROR 3527 (HY000): `r2`@`%` is not a granted role

R-2.5 A role may be connected in a graph with directed edges through which privileges are aggregated. The final aggregation is called the effective privileges.

R-2.6 Edges connecting an authenticating user to a roles can be either active or non-active. Only active edges are used for aggregating the effective privilege.

R-2.7 All connected roles are traversed using a breadth-first search algorithm which avoids visiting the same node twice implying that a role dependency graph is an acyclic directed subgraph of the graph of all roles.

R-2.8 REVOKE ALL PRIVILEGES, GRANT OPTION FROM <authid> must not revoke any roles.

3 Activating a role

R-3.1 Executing SET ROLES will immediately modify the current user's effective privileges in accordance with the in-memory roles graph through active granted roles. Note: This is an in-memory operation and no privilege data will be loaded from the privilege tables.

R-3.3 User must have been granted the authorizationID it attempts to set as an active role.

R-3.4 SET ROLE NONE sets the current role to NONE. At the beginning of a session, the current role is NONE.

R-3.5 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.

Example: If user joe has default role X, then when joe connects there is an implicit "SET ROLE X" execution.

R-3.6 Privileges required for ALTER USER .. DEFAULT ROLE are CREATE_USER or WRITE_ACLs on mysql.default_roles.

R-3.7 ALTER USER .. DEFAULT ROLE NONE is the initial state of a new user. That is, after you do CREATE USER Sally; user Sally has no default roles.

R-3.8 You do not need privileges on role_name to say "ALTER USER alter_user 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 or error at connect time.

4 The Initial State

The following statement happens whenever a user authenticates:

SET ROLE DEFAULT;      
     

If the user has no default role, then the initial value of CURRENT_ROLE is NONE.

5 Show grants and roles

R-5.1 SHOW GRANTS FOR <authid> will display only privileges for authid which are directly granted to authid, including which roles that are granted.

R-5.2 SHOW GRANTS FOR <authid> USING <roles> will display the effective privileges for authid using roles as if they were active roles.

R-5.3 SUPER_ACL is required when the USING clause is used.

6 Plan For Replication

R-6.1 Roles and role edges must be replicated properly

7 Plan for Backup

R-7.1 It must be possible to back up roles and role edges

8 Stored Procedures and Definer Privileges

R-8.1 Definer privileges are evaluated using the default roles of the definer authorization ID.

9 Logging In With A Role Name

R-9.1 A role which you log into is an authenticated and privileged user and behaves as such.

D-1 Definition: AuthorizationID := user_identifier '@' host_identifier | user_identifier

user_identifier and host_identifiers may be quoted to distinguish them from reserved keywords.

Both roles and users are authorizationIDs. Users also are authenticationIDs. Roles can be users.

Example: `SELECT`@`UPDATE`, `NONE`, `ALL`@`NONE`

Note: The host_identifier must in lack of specification be expanded to '%'

I-1 CREATE ROLE

Syntax:

CREATE ROLE [ IF NOT EXISTS ] authorizationID
authorizationID := user_name ['@' host ]

Examples:

CREATE ROLE role1;      
CREATE ROLE `Top Engineer`;
CREATE ROLE wp_application_1@localhost;

I-2 DROP ROLE

Syntax:

DROP ROLE [ IF EXISTS ] authorizationID [, authorizationID ...]

Example:

DROP ROLE role_name_1;    
DROP ROLE wp_application_1@localhost, wp_applications, wp_administrators;

I-3 Granting privileges to authorizationIDs

Syntax:

GRANT privilegeID [,privilegeID ...] ON object_ID_range TO authorizationID [,authorizationID ...] 
[WITH GRANT OPTION]

Both roles and users are authorizationIDs. As in the MySQL Reference Manual (http://dev.mysql.com/doc/refman/5.5/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.

I-4 Creating role descriptors

Syntax:

GRANT authorizationID [, authorizationID ...] TO authorizationID [, authorizationID ...]
 [WITH ADMIN OPTION]
 

Examples:

GRANT sys_role TO peter@clickhost.net;
GRANT engineering TO joan, sally;
GRANT engineering, consultants TO joan, sally;
GRANT engineering, qa TO consultants;
GRANT `engineering`@`US` TO `engineering`@`INDIA`;
GRANT wp_administrators TO joe@localhost WITH ADMIN OPTION;

The following is NOT allowed:

GRANT engineering ON *.*, SUPER ON *.* TO joan, sally;
GRANT SUPER, engineering ON *.* TO joan, sally;
GRANT SELECT, engineering ON *.* TO joan; 

Note: 'WITH ADMIN OPTION' means that an authorizationID is granted the right to GRANT a role (and all its privileges) to another authorizationID. This is both convenient for creating a hierarchy of administrators and for passing the 'WITH GRANT OPTION' privileges. Before this option is implemented it may be requried to normalize the privilege tables in order to be able to strictly associate 'WITH GRANT OPTION' to a single privilege object.

I-5 REVOKE ROLE

Syntax:

REVOKE [authorizationID [, authorizationID ...] | ALL ROLES ] FROM authorizationID [, authorizationID ...]

Examples:

REVOKE engineering_role FROM foo@localhost
REVOKE engineering FROM managers
REVOKE ALL ROLES FROM thek@10.0.0.1

You can still revoke ordinary privileges as usual:

REVOKE CREATE ROLE ON *.* FROM foo@localhost, engineering
REVOKE DROP ROLE ON *.* FROM managers, foo@10.0.0.1
REVOKE SELECT ON *.* FROM engineering

I-6 SET ROLE

Syntax:

SET ROLE NONE | DEFAULT | ALL [EXCEPT  authorizationID [,authorizationID ...] ] | authorizationID [,authorizationID ...]

Sets the current role for the current user. The effective access privilege of the current user will be the union of the privileges of the role and the current user privileges. Switching role from one role to another might decrease privileges as well as increase privileges.

SET ROLE DEFAULT is the same as SET ROLE authorizationID where authorizationID is a default authorizationID assigned to the current user by a SET DEFAULT ROLE statement. (see F-3.5, I-9)

The active roles are authentication properties and only have a meaning when the current user is authenticated. When calculated effective privileges from the active roles, /all/ granted role descriptors count; not only active role descriptors.

Example

Let G be defined by the set of graphs {R6(R4(R1), R5(R2,R3)), R7, R8(R9) ,... }

From G is true that:

• R6 is granted the roles R4, R5.
• R6 can activate either R4, R5 or (R4,R5)
• Iff R6 has activated R4 then the effective privilege will be aggregated from

R6(R4(R1).

• Iff R6 has activated R5 then the effective privilege will be aggregated from

R6(R5(R2,R1)).

• Iff R6 has activated (R4.R5) then the effective privilege will be aggregated from R6(R4(R1), R5(R2,R3)).

I-7 CURRENT_ROLE()

This is a function which returns the current active roles, or 'NONE' if there is no active roles. Roles will be infixed by ','.

This is analogous to the CURRENT_USER function. The data type, length, character set, and collation are the same as for CURRENT_USER.

Example:

mysql> SELECT CURRENT_ROLE()
 ------------------------- 
| CURRENT_ROLE()          |
 ------------------------- 
| administrators,wp_users |
 ------------------------

I-8 SHOW PRIVILEGES

The output from SHOW PRIVILEGES will contain these new rows:

      
Privilege    | Context      | Comment      
-------------+--------------+--------      
Create role  | Server Admin | To create new roles      
Drop role    | Server Admin | To drop roles     

I-9 ALTER USER ... DEFAULT ROLE

Syntax:

 ALTER USER authorizationID DEFAULT ROLE authorizationID[,authorizationID ...]
 

Example:

 ALTER USER joe@10.0.0.1 DEFAULT ROLE administrators, wp_users@localhost;
     

This statement defines what role to use when the statement "SET ROLE DEFAULT" is executed.

After successful user authentication, the server always executes "SET ROLE DEFAULT" implicitly as part of the login process.

For new users the DEFAULT ROLE is NONE.

I-9b Alternative syntax: SET DEFAULT ROLE

Syntax:

SET DEFAULT ROLE authorizationID [,authorizationID ...] | NONE
 TO authorizationID [,authorizationID ...]

I-10 SHOW GRANTS FOR authorizationID USING authorizationID[,authorizationID ...]

This extension allows for inspection of the effective privileges for a user given a specified active role.

I-11 Error message

<TODO>

I-12 MYSQL.ROLE_EDGES

FROM_HOST CHAR(60) BINARY DEFAULT '' NOT NULL,
FROM_USER CHAR(32) BINARY DEFAULT '' NOT NULL,
TO_HOST CHAR(60) BINARY DEFAULT '' NOT NULL,
TO_USER CHAR(32) BINARY DEFAULT '' NOT NULL,
WITH_ADMIN_OPTION ENUM('N', 'Y')  COLLATE UTF8_GENERAL_CI DEFAULT 'N' NOT NULL,
PRIMARY KEY (FROM_HOST,FROM_USER,TO_HOST,TO_USER)
engine=InnoDB
CHARACTER SET utf8 COLLATE utf8_bin
comment='Edges for role subgraphs';

I-13 MYSQL.DEFAULT_ROLES

HOST CHAR(60) BINARY DEFAULT '' NOT NULL,
USER CHAR(32) BINARY DEFAULT '' NOT NULL,
DEFAULT_ROLE_HOST CHAR(60) BINARY DEFAULT '%' NOT NULL,
DEFAULT_ROLE_USER CHAR(32) BINARY DEFAULT '' NOT NULL,
PRIMARY KEY (HOST, USER, DEFAULT_ROLE_HOST, DEFAULT_ROLE_USER)
engine=InnoDB
CHARACTER SET utf8 COLLATE utf8_bin 
comment='Default roles to be activated after user has authenticated';

I-14 SQL function: ROLES_GRAPHML()

Returns an graphml document representing all in memory role subgraphs.

S-1 Boost::graph library is used for representing the role graph.

S-2 The privileges are aggregated and flattened into effective privileges under the acl_cache mutex and the LOCK_grant mutex.

S-3 The in memory role graph is synchronized to the mysql.role_edges table when a new role is GRANTED, REVOKED or DROPPED. Nothing happens to the in memory graph when a new role (or user) is created.

S-4 As a general principle this worklog is about introducing roles and not about refactoring the existing privilege system so if possible the existing behaviour should stay the same when no active roles are in effect.

S-5 A std::unordered_multimap is used to maintain a vertex to authorizationID index. This index needs to be recalculated everytime the acl_cache is updated because the reference to a ACL_USER elements of the user are changing when the cache is sorted. As we're trying to avoid refactoring the existing privilege system with this worklog we adopt to the legacy behavior instead.

S-6 AuthorizationIDs of active roles are copied to the Security_context. Before each aggregation of effective privileges, the list of active roles are verified against the set of granted roles.

S-7 Effective privileges are deep copied to a cache (Acl_cache) which is referenced by the Security_context. SQL objects in the cache might reference objects by name which change during the life time of the assigned privilege. Broken references have no significant meaning and is allowed, ie the server could verify privileges for "db1.t1" even if table "db1.t1" was deleted by another thread.

S-8 All access check functions must be refactored to use a Security_context API to pick effective privileges over user local privileges when there are active roles. When a Security_context doesn't have any active roles, the execution will fall back on existing and unmodified privileges checks. The base privilege access functions currently identified are

• check_access
• check_grant
• check_grant_column
• check_global_access
• check_grant_routine
• check_view_precheck

The introduction of the new cache to these functions will look like a conditional statement followed by a call to a Security_context member function:

if (sctx->get_active_roles()->size() != 0)
{
  t_ref->grant.grant_table= 0; // Remember for column test
  t_ref->grant.version= grant_version;

  Grant_table_aggregate aggr=
    sctx->table_and_column_acls(thd,
     {t_ref->get_db_name(),
      strlen(t_ref->get_db_name())},
     {t_ref->get_table_name(),
      strlen(t_ref->get_table_name())}); 
  /* inspect Grant_table_aggregate for table- and column privileges */
  [..]
}
else { /* fall back on legacy code here */

S-9 The Acl_cache is using a lockless hash and atomics and its state depends on the a version counter triggered by GRANT / REVOKE statements and SET ROLE, and on login/logout of a user which cause the associated Security_context to checkout or return a cache object (Acl_map) based on a key created from { authid, global_cache_version, list_of_active_roleid }

S-10 The set of old Acl_cache objects with a reference count of 0 will be periodically garbage collected. The trigger for this collection is that any Acl_map upon release has both it's reference number equal to 0 and the Acl_map version is less than the global cache version. The number of cache items is accessible in a server status variable "Acl_cache_item_count".

S-11 Each Acl_cache element is a collection of maps, Acl_map, which contain the effective privileges for schemas, tables, columns, stored procedures, functions as well as database global privileges. An Acl_map is in principle a key-value store which maps a qualified name of an SQL object to an ACL (a ulong). The Acl_map remembers the global cache version which was used to create the object and keeps record of an atomic reference count which is increased when the object is checked out and decreased when the object is released.

S-12 The default roles of a user is stored in a global multimap protected by the acl_cache mutex. The elements are strings of qualified names of authorizationIDs. These IDs may or may not have corresponding ACL_USER objects in the acl_cache.

S-13.1 Privileges required for granting a role are: INSERT_ACLs on mysql.role_edges. Note exception in R-2.3

S-13.2 Privileges required for revoking a role are: DELETE_ACL on mysql.role_edges. Note exception in R-2.3

14 Privilege cache

S-14.1 Effective privileges are aggregated into in-memory 'acl maps' and stored into a cache for performance.

S-14.2 An acl map is leased by the security_context of a client session.

S-14.3 The life time of an acl map cache element is restricted by a global cache version number and the number of references to the acl map by active security_contexts.

S-14.4 The security_context renew its lease to an acl map at 1. login time and 2. the beginning of every new statement after the first statement.

S-14.5 When a security_context renew its lease the UserID of the security_context, the global cache version and all the UsersIDs active RoleIDs are considered. An update in either RoleID or global version will result in a new hash key when searching the acl cache and a new acl map. If no previous acl_map is located a new map will be generated by aggregation of effective privileges over the role graph.

S-14.6 Acl map check outs for existing acl map elements should not acquire any mutex lock.

S-14.7 Generation of an acl map will acquire both the acl_cache mutex and the LOCK_grant mutex.