+--------------------------------+ 

To cause all explicitly granted and mandatory roles to be automatically activated when users connect to the server, enable the activate_all_roles_on_login  system variable. By default, automatic role activation is disabled. 

Within a session, a user can execute SET ROLE  to change the set of active roles. For example, for rw_user1 : 

mysql> SET ROLE NONE; SELECT CURRENT_ROLE(); +----------------+ | CURRENT_ROLE() | +----------------+ | NONE | +----------------+ mysql> SET ROLE ALL EXCEPT ’app_write’; SELECT CURRENT_ROLE(); +----------------+ | CURRENT_ROLE() | +----------------+ | ‘app_read‘@‘%‘ | +----------------+ mysql> SET ROLE DEFAULT; SELECT CURRENT_ROLE(); +--------------------------------+ | CURRENT_ROLE() | +--------------------------------+ | ‘app_read‘@‘%‘,‘app_write‘@‘%‘ | +--------------------------------+ 

The first SET ROLE  statement deactivates all roles. The second makes rw_user1  effectively read only. The third restores the default roles. 

The effective user for stored program and view objects is subject to the DEFINER  and SQL SECURITY attributes, which determine whether execution occurs in invoker or definer context (see Section 25.6, “Stored Object Access Control” ): 

•Stored program and view objects that execute in invoker context execute with the roles that are active within the current session. 

•Stored program and view objects that execute in definer context execute with the default roles of the user named in their DEFINER  attribute. If activate_all_roles_on_login  is enabled, such objects execute with all roles granted to the DEFINER  user, including mandatory roles. For stored programs, if execution should occur with roles different from the default, the program body should execute SET ROLE to activate the required roles. 

## Revoking Roles or Role Privileges 

Just as roles can be granted to an account, they can be revoked from an account: 

REVOKE role  FROM user ; 

Roles named in the mandatory_roles  system variable value cannot be revoked. 

REVOKE  can also be applied to a role to modify the privileges granted to it. This affects not only the role itself, but any account granted that role. Suppose that you want to temporarily make all application users read only. To do this, use REVOKE  to revoke the modification privileges from the app_write  role: 

REVOKE INSERT, UPDATE, DELETE ON app_db.* FROM ’app_write’; 

As it happens, that leaves the role with no privileges at all, as can be seen using SHOW GRANTS  (which demonstrates that this statement can be used with roles, not just users): 

