MENU


Roles

A MySQL role is a named collection of privileges. Like user accounts, roles can have privileges granted to and revoked from them. Roles add a layer of indirection between accounts and privileges: instead of granting the same set of privileges to every account that needs them, the privileges are granted once to a role, and the role is granted to as many accounts as needed. Changing the role's privileges immediately changes what every account holding that role can do, without touching the accounts themselves.

A typical workflow creates the accounts, creates one role per job function, attaches privileges to each role, and finally grants roles to accounts:
CREATE USER 'dev1'@'localhost' IDENTIFIED BY 'dev1pass';
CREATE USER 'read_user1'@'localhost' IDENTIFIED BY 'read_user1pass';
CREATE USER 'read_user2'@'localhost' IDENTIFIED BY 'read_user2pass';
CREATE USER 'rw_user1'@'localhost' IDENTIFIED BY 'rw_user1pass';

CREATE ROLE 'app_developer', 'app_read', 'app_write';

GRANT ALL ON app_db.* TO 'app_developer';
GRANT SELECT ON app_db.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON app_db.* TO 'app_write';

GRANT 'app_developer' TO 'dev1'@'localhost';
GRANT 'app_read' TO 'read_user1'@'localhost', 'read_user2'@'localhost';
GRANT 'app_read', 'app_write' TO 'rw_user1'@'localhost';

Four accounts are created first (see Users), then three roles are created and each is given the privileges appropriate to a job function, and finally each account is granted the role(s) matching its job function. rw_user1 ends up with both app_read and app_write.

Granting a role to an account does not, by itself, make that role's privileges active in a session — see Default Roles and SET ROLE below. SHOW GRANTS accepts a USING clause to preview what an account's effective privileges would be with specific roles active, whether or not they are currently active:
SHOW GRANTS FOR user [USING role [, role] ...];

With no USING clause, lists the roles and privileges granted directly to the account. With USING, lists what the account's privileges would be if the named roles were active, without actually activating them.

SHOW GRANTS FOR 'dev1'@'localhost';
SHOW GRANTS FOR 'dev1'@'localhost' USING 'app_developer';
SHOW GRANTS FOR 'read_user1'@'localhost' USING 'app_read';
SHOW GRANTS FOR 'rw_user1'@'localhost' USING 'app_read', 'app_write';

The first form shows what is directly granted to dev1, including the fact that it holds the app_developer role. The other three resolve the privileges each role set would grant.


SHOW GRANTS FOR 'dev1'@'localhost';
SHOW GRANTS FOR 'dev1'@'localhost' USING 'app_developer';
SHOW GRANTS FOR 'read_user1'@'localhost' USING 'app_read';
SHOW GRANTS FOR 'rw_user1'@'localhost' USING 'app_read', 'app_write';

Grants for dev1@localhost
GRANT USAGE ON *.* TO `dev1`@`localhost`
GRANT `app_developer`@`%` TO `dev1`@`localhost`
Grants for dev1@localhost
GRANT USAGE ON *.* TO `dev1`@`localhost`
GRANT ALL PRIVILEGES ON `app_db`.* TO `dev1`@`localhost`
GRANT `app_developer`@`%` TO `dev1`@`localhost`
Grants for read_user1@localhost
GRANT USAGE ON *.* TO `read_user1`@`localhost`
GRANT SELECT ON `app_db`.* TO `read_user1`@`localhost`
GRANT `app_read`@`%` TO `read_user1`@`localhost`
Grants for rw_user1@localhost
GRANT USAGE ON *.* TO `rw_user1`@`localhost`
GRANT SELECT, INSERT, UPDATE, DELETE ON `app_db`.* TO `rw_user1`@`localhost`
GRANT `app_read`@`%`,`app_write`@`%` TO `rw_user1`@`localhost`

5.3.1. CREATE ROLE

Creates one or more new roles, each initially with no privileges.
CREATE ROLE [IF NOT EXISTS] role [, role] ...;

A role name follows the same 'name'@'host' format as a user account; if the host part is omitted, it defaults to the wildcard host '%', exactly as for CREATE USER. This lets a role be scoped to a particular host if needed, though most roles omit the host and rely on the default.

CREATE ROLE 'admin', 'developer';
CREATE ROLE 'webapp'@'localhost';

Creates two roles that default to host '%', and a third role explicitly scoped to localhost.

A newly created role has no privileges and is not granted to any account. Privileges are attached with GRANT, exactly as for a user account. DROP ROLE [IF EXISTS] role [, role] ...; removes a role, revoking it from every account and role it had been granted to.

5.3.2. Granting and Revoking Roles

A role is attached to (or detached from) an account, or to another role, using the same GRANT and REVOKE statements used for privileges; see Privileges for the full statement syntax.
GRANT role [, role] ... TO user_or_role [, user_or_role] ... [WITH ADMIN OPTION];
REVOKE [IF EXISTS] role [, role] ... FROM user_or_role [, user_or_role] ... [IGNORE UNKNOWN USER];

Grants or revokes one or more existing roles to/from one or more accounts or other roles. Because the target can itself be a role, roles can be nested: granting role A to role B means every account holding B also inherits everything A grants. WITH ADMIN OPTION lets the grantee, in turn, grant or revoke that role to/from others; doing so without holding the ROLE_ADMIN dynamic privilege (or the deprecated SUPER) otherwise fails.

REVOKE 'app_write' FROM 'rw_user1'@'localhost';

Detaches the app_write role from rw_user1; rw_user1 keeps whatever app_read still grants it, plus any privileges granted to it directly.

5.3.3. Default Roles

A role granted to an account is not automatically active every time that account connects — unless it is also marked as one of the account's default roles. Default roles activate themselves at connect time with no explicit action needed from the client. They are set with the DEFAULT ROLE clause of CREATE USER or ALTER USER, or with the equivalent standalone statement:
SET DEFAULT ROLE {NONE | ALL | role [, role] ...} TO user [, user] ...;

Sets the default role(s) for one or more existing accounts; equivalent to the DEFAULT ROLE clause of ALTER USER. NONE clears all default roles for the named accounts. ALL makes every role currently granted to each account a default role. Naming specific roles requires that each one already be granted to the account.

CREATE USER 'joe'@'10.0.0.1' DEFAULT ROLE 'administrator', 'developer';
SET DEFAULT ROLE ALL TO 'rw_user1'@'localhost';
ALTER USER 'dev1'@'localhost' DEFAULT ROLE NONE;

joe's administrator and developer roles (which must already be granted to it) activate automatically on every connection. rw_user1's default roles are set to every role it currently holds. dev1's default roles are cleared, so app_developer no longer activates automatically for it even though it is still granted — it now requires SET ROLE.

5.3.4. SET ROLE

Within a single connected session, SET ROLE changes which of the current user's granted roles are active, which in turn changes the account's effective privileges for that session. Granted roles include those granted explicitly to the account and any named by the mandatory_roles system variable, which applies implicitly to every account on the server.
SET ROLE {DEFAULT | NONE | ALL | ALL EXCEPT role [, role] ... | role [, role] ...};

DEFAULT activates the account's default roles (see above). NONE deactivates every active role for the session. ALL activates every role granted to the account. ALL EXCEPT role, ... activates every granted role except the ones listed. Naming specific roles activates exactly those, and each one must already be granted to the account or the statement fails.

SET ROLE NONE;
SET ROLE DEFAULT;
SET ROLE 'app_read', 'app_write';
SET ROLE ALL;
SET ROLE ALL EXCEPT 'app_write';

Deactivates all roles, then re-activates the default roles, then activates only two named roles, then everything granted, then everything except app_write.

The CURRENT_ROLE() function returns the roles currently active in the session, and SELECT CURRENT_USER(), CURRENT_ROLE(); is a quick way to check both the connected identity and its active privilege set. Because SET ROLE only changes which already-granted roles are switched on for the session, it cannot be used to gain privileges that were never granted in the first place — use GRANT for that. 

-- Create roles and attach privileges to each
CREATE ROLE 'app_developer', 'app_read', 'app_write';
GRANT ALL ON app_db.* TO 'app_developer';
GRANT SELECT ON app_db.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON app_db.* TO 'app_write';

-- Grant roles to accounts (a single account can hold more than one)
GRANT 'app_developer' TO 'dev1'@'localhost';
GRANT 'app_read', 'app_write' TO 'rw_user1'@'localhost';

-- Preview effective privileges without activating anything
SHOW GRANTS FOR 'rw_user1'@'localhost' USING 'app_read', 'app_write';

-- Make roles activate automatically at connect time
ALTER USER 'dev1'@'localhost' DEFAULT ROLE 'app_developer';
SET DEFAULT ROLE ALL TO 'rw_user1'@'localhost';

-- Within a session, switch which granted roles are active
SET ROLE DEFAULT;
SET ROLE 'app_read';
SET ROLE ALL EXCEPT 'app_write';
SET ROLE NONE;

-- Detach a role from an account, then remove the role entirely
REVOKE 'app_write' FROM 'rw_user1'@'localhost';
DROP ROLE 'app_write';