1show status like 'debug%'; 2Variable_name Value 3set @old_dbug=@@global.debug_dbug; 4set global debug_dbug="+d,role_merge_stats"; 5create user foo@localhost; 6create role role1; 7create role role2; 8create role role3; 9create role role4; 10create role role5; 11create role role6; 12create role role7; 13create role role8; 14create role role9; 15create role role10; 16grant role1 to role2; 17grant role2 to role4; 18grant role2 to role5; 19grant role3 to role5; 20grant role4 to role6; 21grant role5 to role6; 22grant role5 to role7; 23grant role6 to role8; 24grant role6 to role9; 25grant role7 to role9; 26grant role9 to role10; 27grant role10 to foo@localhost; 28grant role10 to role2; 29ERROR HY000: Cannot grant role 'role10' to: 'role2' 30connect foo, localhost, foo; 31show grants; 32Grants for foo@localhost 33GRANT USAGE ON *.* TO `foo`@`localhost` 34GRANT `role10` TO `foo`@`localhost` 35select * from information_schema.applicable_roles; 36GRANTEE ROLE_NAME IS_GRANTABLE IS_DEFAULT 37foo@localhost role10 NO NO 38role10 role9 NO NULL 39role2 role1 NO NULL 40role4 role2 NO NULL 41role5 role2 NO NULL 42role5 role3 NO NULL 43role6 role4 NO NULL 44role6 role5 NO NULL 45role7 role5 NO NULL 46role9 role6 NO NULL 47role9 role7 NO NULL 48show status like 'debug%'; 49Variable_name Value 50Debug_role_merges_global 11 51Debug_role_merges_db 0 52Debug_role_merges_table 0 53Debug_role_merges_column 0 54Debug_role_merges_routine 0 55connection default; 56grant select on *.* to role1; 57show status like 'debug%'; 58Variable_name Value 59Debug_role_merges_global 19 60Debug_role_merges_db 0 61Debug_role_merges_table 0 62Debug_role_merges_column 0 63Debug_role_merges_routine 0 64connection foo; 65select count(*) from mysql.roles_mapping; 66ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 'roles_mapping' 67set role role10; 68select count(*) from mysql.roles_mapping; 69count(*) 7022 71show grants; 72Grants for foo@localhost 73GRANT SELECT ON *.* TO `role1` 74GRANT USAGE ON *.* TO `foo`@`localhost` 75GRANT USAGE ON *.* TO `role10` 76GRANT USAGE ON *.* TO `role2` 77GRANT USAGE ON *.* TO `role3` 78GRANT USAGE ON *.* TO `role4` 79GRANT USAGE ON *.* TO `role5` 80GRANT USAGE ON *.* TO `role6` 81GRANT USAGE ON *.* TO `role7` 82GRANT USAGE ON *.* TO `role9` 83GRANT `role10` TO `foo`@`localhost` 84GRANT `role1` TO `role2` 85GRANT `role2` TO `role4` 86GRANT `role2` TO `role5` 87GRANT `role3` TO `role5` 88GRANT `role4` TO `role6` 89GRANT `role5` TO `role6` 90GRANT `role5` TO `role7` 91GRANT `role6` TO `role9` 92GRANT `role7` TO `role9` 93GRANT `role9` TO `role10` 94select * from information_schema.enabled_roles; 95ROLE_NAME 96role1 97role10 98role2 99role3 100role4 101role5 102role6 103role7 104role9 105connection default; 106revoke select on *.* from role1; 107show status like 'debug%'; 108Variable_name Value 109Debug_role_merges_global 27 110Debug_role_merges_db 0 111Debug_role_merges_table 0 112Debug_role_merges_column 0 113Debug_role_merges_routine 0 114connection foo; 115select count(*) from mysql.roles_mapping; 116count(*) 11722 118set role none; 119set role role10; 120select count(*) from mysql.roles_mapping; 121ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 'roles_mapping' 122set role none; 123connection default; 124grant select on mysql.* to role1; 125show status like 'debug%'; 126Variable_name Value 127Debug_role_merges_global 27 128Debug_role_merges_db 8 129Debug_role_merges_table 0 130Debug_role_merges_column 0 131Debug_role_merges_routine 0 132connection foo; 133select count(*) from mysql.roles_mapping; 134ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 'roles_mapping' 135set role role10; 136select count(*) from mysql.roles_mapping; 137count(*) 13822 139show grants; 140Grants for foo@localhost 141GRANT SELECT ON `mysql`.* TO `role1` 142GRANT USAGE ON *.* TO `foo`@`localhost` 143GRANT USAGE ON *.* TO `role10` 144GRANT USAGE ON *.* TO `role1` 145GRANT USAGE ON *.* TO `role2` 146GRANT USAGE ON *.* TO `role3` 147GRANT USAGE ON *.* TO `role4` 148GRANT USAGE ON *.* TO `role5` 149GRANT USAGE ON *.* TO `role6` 150GRANT USAGE ON *.* TO `role7` 151GRANT USAGE ON *.* TO `role9` 152GRANT `role10` TO `foo`@`localhost` 153GRANT `role1` TO `role2` 154GRANT `role2` TO `role4` 155GRANT `role2` TO `role5` 156GRANT `role3` TO `role5` 157GRANT `role4` TO `role6` 158GRANT `role5` TO `role6` 159GRANT `role5` TO `role7` 160GRANT `role6` TO `role9` 161GRANT `role7` TO `role9` 162GRANT `role9` TO `role10` 163connection default; 164revoke select on mysql.* from role1; 165show status like 'debug%'; 166Variable_name Value 167Debug_role_merges_global 27 168Debug_role_merges_db 16 169Debug_role_merges_table 0 170Debug_role_merges_column 0 171Debug_role_merges_routine 0 172connection foo; 173select count(*) from mysql.roles_mapping; 174ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 'roles_mapping' 175set role none; 176connection default; 177grant select on mysql.roles_mapping to role1; 178show status like 'debug%'; 179Variable_name Value 180Debug_role_merges_global 27 181Debug_role_merges_db 16 182Debug_role_merges_table 8 183Debug_role_merges_column 0 184Debug_role_merges_routine 0 185connection foo; 186select count(*) from mysql.roles_mapping; 187ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 'roles_mapping' 188set role role10; 189select count(*) from mysql.roles_mapping; 190count(*) 19122 192show grants; 193Grants for foo@localhost 194GRANT SELECT ON `mysql`.`roles_mapping` TO `role1` 195GRANT USAGE ON *.* TO `foo`@`localhost` 196GRANT USAGE ON *.* TO `role10` 197GRANT USAGE ON *.* TO `role1` 198GRANT USAGE ON *.* TO `role2` 199GRANT USAGE ON *.* TO `role3` 200GRANT USAGE ON *.* TO `role4` 201GRANT USAGE ON *.* TO `role5` 202GRANT USAGE ON *.* TO `role6` 203GRANT USAGE ON *.* TO `role7` 204GRANT USAGE ON *.* TO `role9` 205GRANT `role10` TO `foo`@`localhost` 206GRANT `role1` TO `role2` 207GRANT `role2` TO `role4` 208GRANT `role2` TO `role5` 209GRANT `role3` TO `role5` 210GRANT `role4` TO `role6` 211GRANT `role5` TO `role6` 212GRANT `role5` TO `role7` 213GRANT `role6` TO `role9` 214GRANT `role7` TO `role9` 215GRANT `role9` TO `role10` 216connection default; 217revoke select on mysql.roles_mapping from role1; 218show status like 'debug%'; 219Variable_name Value 220Debug_role_merges_global 27 221Debug_role_merges_db 16 222Debug_role_merges_table 16 223Debug_role_merges_column 0 224Debug_role_merges_routine 0 225connection foo; 226select count(*) from mysql.roles_mapping; 227ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 'roles_mapping' 228set role none; 229connection default; 230grant select(User) on mysql.roles_mapping to role1; 231show status like 'debug%'; 232Variable_name Value 233Debug_role_merges_global 27 234Debug_role_merges_db 16 235Debug_role_merges_table 24 236Debug_role_merges_column 8 237Debug_role_merges_routine 0 238connection foo; 239select count(*) from mysql.roles_mapping; 240ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 'roles_mapping' 241set role role10; 242select count(concat(User,Host,Role)) from mysql.roles_mapping; 243ERROR 42000: SELECT command denied to user 'foo'@'localhost' for column 'Host' in table 'roles_mapping' 244select count(concat(User)) from mysql.roles_mapping; 245count(concat(User)) 24622 247show grants; 248Grants for foo@localhost 249GRANT SELECT (User) ON `mysql`.`roles_mapping` TO `role1` 250GRANT USAGE ON *.* TO `foo`@`localhost` 251GRANT USAGE ON *.* TO `role10` 252GRANT USAGE ON *.* TO `role1` 253GRANT USAGE ON *.* TO `role2` 254GRANT USAGE ON *.* TO `role3` 255GRANT USAGE ON *.* TO `role4` 256GRANT USAGE ON *.* TO `role5` 257GRANT USAGE ON *.* TO `role6` 258GRANT USAGE ON *.* TO `role7` 259GRANT USAGE ON *.* TO `role9` 260GRANT `role10` TO `foo`@`localhost` 261GRANT `role1` TO `role2` 262GRANT `role2` TO `role4` 263GRANT `role2` TO `role5` 264GRANT `role3` TO `role5` 265GRANT `role4` TO `role6` 266GRANT `role5` TO `role6` 267GRANT `role5` TO `role7` 268GRANT `role6` TO `role9` 269GRANT `role7` TO `role9` 270GRANT `role9` TO `role10` 271connection default; 272grant select(Host) on mysql.roles_mapping to role3; 273show status like 'debug%'; 274Variable_name Value 275Debug_role_merges_global 27 276Debug_role_merges_db 16 277Debug_role_merges_table 30 278Debug_role_merges_column 14 279Debug_role_merges_routine 0 280connection foo; 281select count(concat(User,Host,Role)) from mysql.roles_mapping; 282ERROR 42000: SELECT command denied to user 'foo'@'localhost' for column 'Role' in table 'roles_mapping' 283select count(concat(User,Host)) from mysql.roles_mapping; 284count(concat(User,Host)) 28522 286show grants; 287Grants for foo@localhost 288GRANT SELECT (Host) ON `mysql`.`roles_mapping` TO `role3` 289GRANT SELECT (User) ON `mysql`.`roles_mapping` TO `role1` 290GRANT USAGE ON *.* TO `foo`@`localhost` 291GRANT USAGE ON *.* TO `role10` 292GRANT USAGE ON *.* TO `role1` 293GRANT USAGE ON *.* TO `role2` 294GRANT USAGE ON *.* TO `role3` 295GRANT USAGE ON *.* TO `role4` 296GRANT USAGE ON *.* TO `role5` 297GRANT USAGE ON *.* TO `role6` 298GRANT USAGE ON *.* TO `role7` 299GRANT USAGE ON *.* TO `role9` 300GRANT `role10` TO `foo`@`localhost` 301GRANT `role1` TO `role2` 302GRANT `role2` TO `role4` 303GRANT `role2` TO `role5` 304GRANT `role3` TO `role5` 305GRANT `role4` TO `role6` 306GRANT `role5` TO `role6` 307GRANT `role5` TO `role7` 308GRANT `role6` TO `role9` 309GRANT `role7` TO `role9` 310GRANT `role9` TO `role10` 311connection default; 312revoke select(User) on mysql.roles_mapping from role1; 313show status like 'debug%'; 314Variable_name Value 315Debug_role_merges_global 27 316Debug_role_merges_db 16 317Debug_role_merges_table 38 318Debug_role_merges_column 22 319Debug_role_merges_routine 0 320connection foo; 321select count(concat(User,Host)) from mysql.roles_mapping; 322ERROR 42000: SELECT command denied to user 'foo'@'localhost' for column 'User' in table 'roles_mapping' 323select count(concat(Host)) from mysql.roles_mapping; 324count(concat(Host)) 32522 326connection default; 327revoke select(Host) on mysql.roles_mapping from role3; 328show status like 'debug%'; 329Variable_name Value 330Debug_role_merges_global 27 331Debug_role_merges_db 16 332Debug_role_merges_table 44 333Debug_role_merges_column 28 334Debug_role_merges_routine 0 335connection foo; 336select count(concat(Host)) from mysql.roles_mapping; 337ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 'roles_mapping' 338set role none; 339connection default; 340create procedure pr1() select "pr1"; 341create function fn1() returns char(10) return "fn1"; 342grant execute on procedure test.pr1 to role1; 343show status like 'debug%'; 344Variable_name Value 345Debug_role_merges_global 27 346Debug_role_merges_db 16 347Debug_role_merges_table 44 348Debug_role_merges_column 28 349Debug_role_merges_routine 8 350connection foo; 351call pr1(); 352ERROR 42000: execute command denied to user 'foo'@'localhost' for routine 'test.pr1' 353set role role10; 354call pr1(); 355pr1 356pr1 357select fn1(); 358ERROR 42000: execute command denied to user 'foo'@'localhost' for routine 'test.fn1' 359connection default; 360grant execute on function test.fn1 to role5; 361show status like 'debug%'; 362Variable_name Value 363Debug_role_merges_global 27 364Debug_role_merges_db 16 365Debug_role_merges_table 44 366Debug_role_merges_column 28 367Debug_role_merges_routine 13 368connection foo; 369select fn1(); 370fn1() 371fn1 372connection default; 373revoke execute on procedure test.pr1 from role1; 374show status like 'debug%'; 375Variable_name Value 376Debug_role_merges_global 27 377Debug_role_merges_db 16 378Debug_role_merges_table 44 379Debug_role_merges_column 28 380Debug_role_merges_routine 21 381connection foo; 382call pr1(); 383ERROR 42000: execute command denied to user 'foo'@'localhost' for routine 'test.pr1' 384select fn1(); 385fn1() 386fn1 387connection default; 388revoke execute on function test.fn1 from role5; 389show status like 'debug%'; 390Variable_name Value 391Debug_role_merges_global 27 392Debug_role_merges_db 16 393Debug_role_merges_table 44 394Debug_role_merges_column 28 395Debug_role_merges_routine 26 396connection foo; 397select fn1(); 398ERROR 42000: execute command denied to user 'foo'@'localhost' for routine 'test.fn1' 399set role none; 400connection default; 401drop procedure pr1; 402drop function fn1; 403grant select on mysql.roles_mapping to role3; 404show status like 'debug%'; 405Variable_name Value 406Debug_role_merges_global 27 407Debug_role_merges_db 16 408Debug_role_merges_table 50 409Debug_role_merges_column 28 410Debug_role_merges_routine 26 411grant select on mysql.roles_mapping to role1; 412show status like 'debug%'; 413Variable_name Value 414Debug_role_merges_global 27 415Debug_role_merges_db 16 416Debug_role_merges_table 53 417Debug_role_merges_column 28 418Debug_role_merges_routine 26 419revoke select on mysql.roles_mapping from role3; 420show status like 'debug%'; 421Variable_name Value 422Debug_role_merges_global 27 423Debug_role_merges_db 16 424Debug_role_merges_table 54 425Debug_role_merges_column 28 426Debug_role_merges_routine 26 427revoke select on mysql.roles_mapping from role1; 428show status like 'debug%'; 429Variable_name Value 430Debug_role_merges_global 27 431Debug_role_merges_db 16 432Debug_role_merges_table 62 433Debug_role_merges_column 28 434Debug_role_merges_routine 26 435grant select on mysql.* to role1; 436show status like 'debug%'; 437Variable_name Value 438Debug_role_merges_global 27 439Debug_role_merges_db 24 440Debug_role_merges_table 62 441Debug_role_merges_column 28 442Debug_role_merges_routine 26 443grant select on test.* to role1; 444show status like 'debug%'; 445Variable_name Value 446Debug_role_merges_global 27 447Debug_role_merges_db 32 448Debug_role_merges_table 62 449Debug_role_merges_column 28 450Debug_role_merges_routine 26 451revoke select on mysql.* from role1; 452show status like 'debug%'; 453Variable_name Value 454Debug_role_merges_global 27 455Debug_role_merges_db 40 456Debug_role_merges_table 62 457Debug_role_merges_column 28 458Debug_role_merges_routine 26 459revoke select on test.* from role1; 460show status like 'debug%'; 461Variable_name Value 462Debug_role_merges_global 27 463Debug_role_merges_db 48 464Debug_role_merges_table 62 465Debug_role_merges_column 28 466Debug_role_merges_routine 26 467connection default; 468drop user foo@localhost; 469drop role role1; 470drop role role2; 471drop role role3; 472drop role role4; 473drop role role5; 474drop role role6; 475drop role role7; 476drop role role8; 477drop role role9; 478drop role role10; 479set global debug_dbug=@old_dbug; 480