Debian Codegraph @

mysql-test/suite/roles/set_role-recursive.result

raw
1 create user test_user@localhost;
2 create role test_role1;
3 grant test_role1 to test_user@localhost;
4 create role test_role2;
5 grant test_role2 to test_role1;
6 select user, host from mysql.user where user not like 'root';
7 User Host
8 mariadb.sys localhost
9 test_role1
10 test_role2
11 test_user localhost
12 select * from mysql.roles_mapping where User like 'test_user';
13 Host User Role Admin_option
14 localhost test_user test_role1 N
15 select * from mysql.roles_mapping where User like 'test_role1';
16 Host User Role Admin_option
17 test_role1 test_role2 N
18 grant select on *.* to test_role2;
19 select * from mysql.user where user like 'test_role1';
20 Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv Create_tablespace_priv Delete_history_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections plugin authentication_string password_expired is_role default_role max_statement_time
21 test_role1 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0 N Y 0.000000
22 select * from mysql.user where user like 'test_role2';
23 Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv Create_tablespace_priv Delete_history_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections plugin authentication_string password_expired is_role default_role max_statement_time
24 test_role2 Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0 N Y 0.000000
25 select * from mysql.roles_mapping;
26 ERROR 42000: SELECT command denied to user 'test_user'@'localhost' for table `mysql`.`roles_mapping`
27 show grants;
28 Grants for test_user@localhost
29 GRANT USAGE ON *.* TO `test_user`@`localhost`
30 GRANT `test_role1` TO `test_user`@`localhost`
31 select current_user(), current_role();
32 current_user() current_role()
33 test_user@localhost NULL
34 set role test_role1;
35 select current_user(), current_role();
36 current_user() current_role()
37 test_user@localhost test_role1
38 show grants;
39 Grants for test_user@localhost
40 GRANT SELECT ON *.* TO `test_role2`
41 GRANT USAGE ON *.* TO `test_role1`
42 GRANT USAGE ON *.* TO `test_user`@`localhost`
43 GRANT `test_role1` TO `test_user`@`localhost`
44 GRANT `test_role2` TO `test_role1`
45 select * from mysql.roles_mapping where Host='';
46 Host User Role Admin_option
47 test_role1 test_role2 N
48 show grants;
49 Grants for test_user@localhost
50 GRANT SELECT ON *.* TO `test_role2`
51 GRANT USAGE ON *.* TO `test_role1`
52 GRANT USAGE ON *.* TO `test_user`@`localhost`
53 GRANT `test_role1` TO `test_user`@`localhost`
54 GRANT `test_role2` TO `test_role1`
55 set role none;
56 select current_user(), current_role();
57 current_user() current_role()
58 test_user@localhost NULL
59 show grants;
60 Grants for test_user@localhost
61 GRANT USAGE ON *.* TO `test_user`@`localhost`
62 GRANT `test_role1` TO `test_user`@`localhost`
63 select * from mysql.roles_mapping;
64 ERROR 42000: SELECT command denied to user 'test_user'@'localhost' for table `mysql`.`roles_mapping`
65 show grants;
66 Grants for test_user@localhost
67 GRANT USAGE ON *.* TO `test_user`@`localhost`
68 GRANT `test_role1` TO `test_user`@`localhost`
69 set role test_role2;
70 ERROR OP000: User `test_user`@`localhost` has not been granted role `test_role2`
71 select current_user(), current_role();
72 current_user() current_role()
73 test_user@localhost NULL
74 show grants;
75 Grants for test_user@localhost
76 GRANT USAGE ON *.* TO `test_user`@`localhost`
77 GRANT `test_role1` TO `test_user`@`localhost`
78 select * from mysql.roles_mapping;
79 ERROR 42000: SELECT command denied to user 'test_user'@'localhost' for table `mysql`.`roles_mapping`
80 show grants;
81 Grants for test_user@localhost
82 GRANT USAGE ON *.* TO `test_user`@`localhost`
83 GRANT `test_role1` TO `test_user`@`localhost`
84 set role test_role1;
85 select current_user(), current_role();
86 current_user() current_role()
87 test_user@localhost test_role1
88 show grants;
89 Grants for test_user@localhost
90 GRANT SELECT ON *.* TO `test_role2`
91 GRANT USAGE ON *.* TO `test_role1`
92 GRANT USAGE ON *.* TO `test_user`@`localhost`
93 GRANT `test_role1` TO `test_user`@`localhost`
94 GRANT `test_role2` TO `test_role1`
95 select * from mysql.roles_mapping where Host='';
96 Host User Role Admin_option
97 test_role1 test_role2 N
98 show grants;
99 Grants for test_user@localhost
100 GRANT SELECT ON *.* TO `test_role2`
101 GRANT USAGE ON *.* TO `test_role1`
102 GRANT USAGE ON *.* TO `test_user`@`localhost`
103 GRANT `test_role1` TO `test_user`@`localhost`
104 GRANT `test_role2` TO `test_role1`
105 set role none;
106 select current_user(), current_role();
107 current_user() current_role()
108 test_user@localhost NULL
109 show grants;
110 Grants for test_user@localhost
111 GRANT USAGE ON *.* TO `test_user`@`localhost`
112 GRANT `test_role1` TO `test_user`@`localhost`
113 select * from mysql.roles_mapping;
114 ERROR 42000: SELECT command denied to user 'test_user'@'localhost' for table `mysql`.`roles_mapping`
115 delete from mysql.user where user='test_role1';
116 delete from mysql.user where user='test_role2';
117 delete from mysql.roles_mapping;
118 flush privileges;
119 drop user 'test_user'@'localhost';