| GRANT USAGE ON *.* TO ‘dev1‘@‘localhost‘ | | GRANT ALL PRIVILEGES ON ‘app_db‘.* TO ‘dev1‘@‘localhost‘ | | GRANT ‘app_developer‘@‘%‘ TO ‘dev1‘@‘localhost‘ | +----------------------------------------------------------+ 

Verify each other type of user similarly: 

mysql> SHOW GRANTS FOR ’read_user1’@’localhost’ USING ’app_read’; +--------------------------------------------------------+ | 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‘ | +--------------------------------------------------------+ 

mysql> SHOW GRANTS FOR ’rw_user1’@’localhost’ USING ’app_read’, ’app_write’; +------------------------------------------------------------------------------+ | 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‘ | +------------------------------------------------------------------------------+ 

SHOW GRANTS  displays mandatory roles according to the rules described in Section 13.7.7.21, “SHOW GRANTS Statement” . 

## Activating Roles 

Roles granted to a user account can be active or inactive within account sessions. If a granted role is active within a session, its privileges apply; otherwise, they do not. To determine which roles are active within the current session, use the CURRENT_ROLE()  function. 

By default, granting a role to an account or naming it in the mandatory_roles  system variable value does not automatically cause the role to become active within account sessions. For example, because thus far in the preceding discussion no rw_user1  roles have been activated, if you connect to the server as rw_user1  and invoke the CURRENT_ROLE()  function, the result is NONE  (no active roles): 

mysql> SELECT CURRENT_ROLE(); +----------------+ | CURRENT_ROLE() | +----------------+ | NONE | +----------------+ 

To specify which roles should become active each time a user connects to the server and authenticates, use SET DEFAULT ROLE . To set the default to all assigned roles for each account created earlier, use this statement: 

SET DEFAULT ROLE ALL TO ’dev1’@’localhost’, ’read_user1’@’localhost’, ’read_user2’@’localhost’, ’rw_user1’@’localhost’; 

Now if you connect as rw_user1 , the initial value of CURRENT_ROLE()  reflects the new default role assignments: 

mysql> SELECT CURRENT_ROLE(); +--------------------------------+ | CURRENT_ROLE() | +--------------------------------+ | ‘app_read‘@‘%‘,‘app_write‘@‘%‘ | 

