Description
-----------

A role bundles a number of privileges together. It assists larger
organizations where, typically, a number of users would have the same
privileges, and, previously, the only way to change the privileges for a group
of users was by changing each user's privileges individually.

Alternatively, multiple external users could have been assigned the same user,
and there would have been no way to see which actual user was responsible for
which action.

With roles, managing this is easy. For example, there could be a number of
users assigned to a journalist role, with identical privileges. Changing the
privileges for all the journalists is a matter of simply changing the role's
privileges, while the individual user is still linked with any changes that
take place.

Roles are created with the CREATE ROLE statement, and dropped with the DROP
ROLE statement. Roles are then assigned to a user with an extension to the
GRANT statement, while privileges are assigned to a role in the regular way
with GRANT. Similarly, the REVOKE statement can be used to both revoke a role
from a user, or revoke a privilege from a role.

Once a user has connected, he can obtain all privileges associated with a role
by setting a role with the SET ROLE statement. The CURRENT_ROLE function
returns the currently set role for the session, if any.

Only roles granted directly to a user can be set, roles granted to other roles
cannot. Instead the privileges granted to a role, which is, in turn, granted
to another role (grantee), will be immediately available to any user who sets
this second grantee role.

The SET DEFAULT ROLE statement allows one to set a default role for a user. A
default role is automatically enabled when a user connects (an implicit SET
ROLE statement is executed immediately after a connection is established).

Roles were implemented as a GSoC 2013 project by Vicentiu Ciorbaru.

System Tables
-------------

Information about roles and who they've been granted to can be found in the
Information Schema APPLICABLE_ROLES table as well as the mysql.ROLES_MAPPING
table.

The Information Schema ENABLED_ROLES table shows the enabled roles for the
current session.

Examples
--------

Creating a role and granting a privilege:

CREATE ROLE journalist;

GRANT SHOW DATABASES ON *.* TO journalist;

GRANT journalist to hulda;

Note, that hulda has no SHOW DATABASES privilege, even though she was granted
the journalist role. She needs to set the role first:

SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+

SELECT CURRENT_ROLE;
+--------------+
| CURRENT_ROLE |
+--------------+
| NULL         |
+--------------+

SET ROLE journalist;

SELECT CURRENT_ROLE;
+--------------+
| CURRENT_ROLE |
+--------------+
| journalist   |
+--------------+

SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| ...                |
| information_schema |
| mysql              |
| performance_schema |
| test               |
| ...                |
+--------------------+

SET ROLE NONE;

Roles can be granted to roles:

CREATE ROLE writer;

GRANT SELECT ON data.* TO writer;

GRANT writer TO journalist;

But one does not need to set a role granted to a role. For example, hulda will
automatically get all writer privileges when she sets the journalist role:

SELECT CURRENT_ROLE;
+--------------+
| CURRENT_ROLE |
+--------------+
| NULL         |
+--------------+

SHOW TABLES FROM data;
Empty set (0.01 sec)

SET ROLE journalist;

SELECT CURRENT_ROLE;
+--------------+
| CURRENT_ROLE |
+--------------+
| journalist   |
+--------------+

SHOW TABLES FROM data;
+------------------------------+
| Tables_in_data               |
+------------------------------+
| set1                         |
| ...                          |
+------------------------------+

Roles and Views (and Stored Routines)
-------------------------------------

When a user sets a role, he, in a sense, has two identities with two
associated sets of privileges. But a view (or a stored routine) can have only
one definer. So, when a view (or a stored routine) is created with the SQL
SECURITY DEFINER, one can specify whether the definer should be CURRENT_USER
(and the view will have none of the privileges of the user's role) or
CURRENT_ROLE (in this case, the view will use role's privileges, but none of
the user's privileges). As a result, sometimes one can create a view that is
impossible to use.

CREATE ROLE r1;

GRANT ALL ON db1.* TO r1;

GRANT r1 TO foo@localhost;

GRANT ALL ON db.* TO foo@localhost;

SELECT CURRENT_USER
+---------------+
| current_user  |
+---------------+
| foo@localhost |
+---------------+

SET ROLE r1;

CREATE TABLE db1.t1 (i int);

CREATE VIEW db.v1 AS SELECT * FROM db1.t1;

SHOW CREATE VIEW db.v1;
+------+-----------------------------------------------------------------------
------------------------------------------------------------------+------------
---------+----------------------+
| View | Create View                                                          
                                 |
character_set_client | collation_connection |
+------+-----------------------------------------------------------------------
------------------------------------------------------------------+------------
---------+----------------------+
| v1   | CREATE ALGORITHM=UNDEFINED DEFINER=`foo`@`localhost` SQL SECURITY
DEFINER VIEW `db`.`v1` AS SELECT `db1`.`t1`.`i` AS `i` from `db1`.`t1` | utf8 
       | utf8_general_ci      |
+------+-----------------------------------------------------------------------
------------------------------------------------------------------+------------
---------+----------------------+

CREATE DEFINER=CURRENT_ROLE VIEW db.v2 AS SELECT * FROM db1.t1;

SHOW CREATE VIEW db.b2;
+------+-----------------------------------------------------------------------
-----------------------------------------------------+----------------------+--
-------------------+
| View | Create View                                                          
                           | character_set_client |
collation_connection |
+------+-----------------------------------------------------------------------
-----------------------------------------------------+----------------------+--
-------------------+
| v2   | CREATE ALGORITHM=UNDEFINED DEFINER=`r1` SQL SECURITY DEFINER VIEW
`db`.`v2` AS select `db1`.`t1`.`a` AS `a` from `db1`.`t1` | utf8              
 | utf8_general_ci      |
+------+-----------------------------------------------------------------------
-----------------------------------------------------+----------------------+--
-------------------+

Other Resources
---------------

* Roles Review by Peter Gulutzan

URL: https://mariadb.com/kb/en/roles_overview/