1create user test_user@localhost; 2create role test_role; 3grant select on *.* to test_role; 4grant test_role to test_user@localhost; 5connect c1, localhost, test_user,,; 6show grants; 7Grants for test_user@localhost 8GRANT `test_role` TO `test_user`@`localhost` 9GRANT USAGE ON *.* TO `test_user`@`localhost` 10select user, host, default_role from mysql.user where user = 'test_user'; 11ERROR 42000: SELECT command denied to user 'test_user'@'localhost' for table 'user' 12set default role test_role; 13select user, host, default_role from mysql.user where user = 'test_user'; 14ERROR 42000: SELECT command denied to user 'test_user'@'localhost' for table 'user' 15disconnect c1; 16connection default; 17select user, host, default_role from mysql.user where user = 'test_user'; 18user host default_role 19test_user localhost test_role 20connect c1, localhost, test_user,,; 21show grants; 22Grants for test_user@localhost 23GRANT `test_role` TO `test_user`@`localhost` 24GRANT USAGE ON *.* TO `test_user`@`localhost` 25GRANT SELECT ON *.* TO `test_role` 26SET DEFAULT ROLE `test_role` FOR `test_user`@`localhost` 27select user, host, default_role from mysql.user where user = 'test_user'; 28user host default_role 29test_user localhost test_role 30set default role NONE; 31disconnect c1; 32connection default; 33select user, host, default_role from mysql.user where user = 'test_user'; 34user host default_role 35test_user localhost 36connect c1, localhost, test_user,,; 37show grants; 38Grants for test_user@localhost 39GRANT `test_role` TO `test_user`@`localhost` 40GRANT USAGE ON *.* TO `test_user`@`localhost` 41select user, host, default_role from mysql.user where user = 'test_user'; 42ERROR 42000: SELECT command denied to user 'test_user'@'localhost' for table 'user' 43disconnect c1; 44connection default; 45select user, host, default_role from mysql.user where user = 'test_user'; 46user host default_role 47test_user localhost 48set default role test_role for test_user@localhost; 49connect c1, localhost, test_user,,; 50show grants; 51Grants for test_user@localhost 52GRANT `test_role` TO `test_user`@`localhost` 53GRANT USAGE ON *.* TO `test_user`@`localhost` 54GRANT SELECT ON *.* TO `test_role` 55SET DEFAULT ROLE `test_role` FOR `test_user`@`localhost` 56select user, host, default_role from mysql.user where user = 'test_user'; 57user host default_role 58test_user localhost test_role 59disconnect c1; 60connection default; 61drop role test_role; 62drop user test_user@localhost; 63