1create user test_user@localhost; 2create role test_role1; 3create role test_role2; 4grant test_role1 to test_user@localhost; 5grant test_role2 to test_user@localhost; 6grant test_role2 to test_role1; 7select user, host from mysql.user where user not like 'root'; 8user host 9test_role1 10test_role2 11test_user localhost 12select * from mysql.roles_mapping; 13Host User Role Admin_option 14 test_role1 test_role2 N 15localhost root test_role1 Y 16localhost root test_role2 Y 17localhost test_user test_role1 N 18localhost test_user test_role2 N 19select user, host from mysql.db; 20user host 21 % 22 % 23grant select on mysql.* to test_role2; 24flush privileges; 25select * from information_schema.applicable_roles; 26GRANTEE ROLE_NAME IS_GRANTABLE IS_DEFAULT 27root@localhost test_role1 YES NO 28root@localhost test_role2 YES NO 29test_role1 test_role2 NO NULL 30select * from information_schema.applicable_roles; 31GRANTEE ROLE_NAME IS_GRANTABLE IS_DEFAULT 32test_role1 test_role2 NO NULL 33test_user@localhost test_role1 NO NO 34test_user@localhost test_role2 NO NO 35show grants; 36Grants for test_user@localhost 37GRANT USAGE ON *.* TO `test_user`@`localhost` 38GRANT `test_role1` TO `test_user`@`localhost` 39GRANT `test_role2` TO `test_user`@`localhost` 40select current_user(), current_role(); 41current_user() current_role() 42test_user@localhost NULL 43set role test_role1; 44select * from information_schema.enabled_roles; 45ROLE_NAME 46test_role1 47test_role2 48select current_user(), current_role(); 49current_user() current_role() 50test_user@localhost test_role1 51show grants; 52Grants for test_user@localhost 53GRANT SELECT ON `mysql`.* TO `test_role2` 54GRANT USAGE ON *.* TO `test_role1` 55GRANT USAGE ON *.* TO `test_role2` 56GRANT USAGE ON *.* TO `test_user`@`localhost` 57GRANT `test_role1` TO `test_user`@`localhost` 58GRANT `test_role2` TO `test_role1` 59GRANT `test_role2` TO `test_user`@`localhost` 60set role none; 61select * from information_schema.enabled_roles; 62ROLE_NAME 63NULL 64select current_user(), current_role(); 65current_user() current_role() 66test_user@localhost NULL 67show grants; 68Grants for test_user@localhost 69GRANT USAGE ON *.* TO `test_user`@`localhost` 70GRANT `test_role1` TO `test_user`@`localhost` 71GRANT `test_role2` TO `test_user`@`localhost` 72show grants for test_user@localhost; 73Grants for test_user@localhost 74GRANT USAGE ON *.* TO `test_user`@`localhost` 75GRANT `test_role1` TO `test_user`@`localhost` 76GRANT `test_role2` TO `test_user`@`localhost` 77show grants for test_role1; 78ERROR 42000: Access denied for user 'test_user'@'localhost' to database 'mysql' 79show grants for test_role2; 80ERROR 42000: Access denied for user 'test_user'@'localhost' to database 'mysql' 81show grants for CURRENT_USER; 82Grants for test_user@localhost 83GRANT USAGE ON *.* TO `test_user`@`localhost` 84GRANT `test_role1` TO `test_user`@`localhost` 85GRANT `test_role2` TO `test_user`@`localhost` 86show grants for CURRENT_USER(); 87Grants for test_user@localhost 88GRANT USAGE ON *.* TO `test_user`@`localhost` 89GRANT `test_role1` TO `test_user`@`localhost` 90GRANT `test_role2` TO `test_user`@`localhost` 91show grants for CURRENT_ROLE; 92ERROR 42000: There is no such grant defined for user 'test_user' on host 'localhost' 93show grants for CURRENT_ROLE(); 94ERROR 42000: There is no such grant defined for user 'test_user' on host 'localhost' 95set role test_role2; 96select * from information_schema.enabled_roles; 97ROLE_NAME 98test_role2 99select current_user(), current_role(); 100current_user() current_role() 101test_user@localhost test_role2 102show grants; 103Grants for test_user@localhost 104GRANT SELECT ON `mysql`.* TO `test_role2` 105GRANT USAGE ON *.* TO `test_role2` 106GRANT USAGE ON *.* TO `test_user`@`localhost` 107GRANT `test_role1` TO `test_user`@`localhost` 108GRANT `test_role2` TO `test_user`@`localhost` 109show grants for test_user@localhost; 110Grants for test_user@localhost 111GRANT USAGE ON *.* TO `test_user`@`localhost` 112GRANT `test_role1` TO `test_user`@`localhost` 113GRANT `test_role2` TO `test_user`@`localhost` 114show grants for test_role1; 115Grants for test_role1 116GRANT SELECT ON `mysql`.* TO `test_role2` 117GRANT USAGE ON *.* TO `test_role1` 118GRANT USAGE ON *.* TO `test_role2` 119GRANT `test_role2` TO `test_role1` 120show grants for test_role2; 121Grants for test_role2 122GRANT SELECT ON `mysql`.* TO `test_role2` 123GRANT USAGE ON *.* TO `test_role2` 124show grants for CURRENT_USER; 125Grants for test_user@localhost 126GRANT USAGE ON *.* TO `test_user`@`localhost` 127GRANT `test_role1` TO `test_user`@`localhost` 128GRANT `test_role2` TO `test_user`@`localhost` 129show grants for CURRENT_USER(); 130Grants for test_user@localhost 131GRANT USAGE ON *.* TO `test_user`@`localhost` 132GRANT `test_role1` TO `test_user`@`localhost` 133GRANT `test_role2` TO `test_user`@`localhost` 134show grants for CURRENT_ROLE; 135Grants for test_role2 136GRANT SELECT ON `mysql`.* TO `test_role2` 137GRANT USAGE ON *.* TO `test_role2` 138show grants for CURRENT_ROLE(); 139Grants for test_role2 140GRANT SELECT ON `mysql`.* TO `test_role2` 141GRANT USAGE ON *.* TO `test_role2` 142drop user 'test_user'@'localhost'; 143revoke select on mysql.* from test_role2; 144drop role test_role1; 145drop role test_role2; 146delete from mysql.roles_mapping where Role='test_role1'; 147delete from mysql.roles_mapping where Role='test_role2'; 148flush privileges; 149# 150# MDEV-24289: show grants missing with grant option 151# 152create role anel; 153GRANT SELECT, UPDATE, DELETE, ALTER ON *.* TO 'anel'; 154SHOW GRANTS for 'anel'; 155Grants for anel 156GRANT SELECT, UPDATE, DELETE, ALTER ON *.* TO `anel` 157create role MariaDB_admin; 158GRANT SELECT, UPDATE, DELETE, ALTER ON *.* TO 'MariaDB_admin' WITH GRANT OPTION; 159SHOW GRANTS for 'MariaDB_admin'; 160Grants for MariaDB_admin 161GRANT SELECT, UPDATE, DELETE, ALTER ON *.* TO `MariaDB_admin` WITH GRANT OPTION 162drop role MariaDB_admin; 163drop role anel; 164