Debian Codegraph @

mysql-test/suite/roles/set_and_drop.result

raw
1 create database mysqltest1;
2 create table mysqltest1.t1 (a int, b int);
3 create table mysqltest1.t2 (a int, b int);
4 insert mysqltest1.t1 values (1,2),(3,4);
5 insert mysqltest1.t2 values (5,6),(7,8);
6 create procedure mysqltest1.pr1() select "pr1";
7 create user foo@localhost;
8 create role role1;
9 create role role2;
10 grant role2 to role1;
11 grant role1 to foo@localhost;
12 grant reload on *.* to role2;
13 grant select on mysql.* to role2;
14 grant execute on procedure mysqltest1.pr1 to role2;
15 grant select on mysqltest1.t1 to role2;
16 grant select (a) on mysqltest1.t2 to role2;
17 connect foo,localhost,foo;
18 flush tables;
19 ERROR 42000: Access denied; you need (at least one of) the RELOAD privilege(s) for this operation
20 select * from mysql.roles_mapping;
21 ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table `mysql`.`roles_mapping`
22 show tables from mysqltest1;
23 ERROR 42000: Access denied for user 'foo'@'localhost' to database 'mysqltest1'
24 set role role1;
25 flush tables;
26 select * from mysql.roles_mapping;
27 Host User Role Admin_option
28 role1 role2 N
29 localhost foo role1 N
30 localhost root role1 Y
31 localhost root role2 Y
32 show tables from mysqltest1;
33 Tables_in_mysqltest1
34 t1
35 t2
36 select * from mysqltest1.t1;
37 a b
38 1 2
39 3 4
40 select * from mysqltest1.t2;
41 ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table `mysqltest1`.`t2`
42 select a from mysqltest1.t2;
43 a
44 5
45 7
46 call mysqltest1.pr1();
47 pr1
48 pr1
49 connection default;
50 revoke execute on procedure mysqltest1.pr1 from role2;
51 connection foo;
52 call mysqltest1.pr1();
53 ERROR 42000: execute command denied to user 'foo'@'localhost' for routine 'mysqltest1.pr1'
54 connection default;
55 drop role role2;
56 connection foo;
57 show grants;
58 Grants for foo@localhost
59 GRANT `role1` TO `foo`@`localhost`
60 GRANT USAGE ON *.* TO `foo`@`localhost`
61 GRANT USAGE ON *.* TO `role1`
62 select * from information_schema.enabled_roles;
63 ROLE_NAME
64 role1
65 flush tables;
66 select * from mysql.roles_mapping;
67 ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table `mysql`.`roles_mapping`
68 select * from mysqltest1.t1;
69 ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table `mysqltest1`.`t1`
70 select a from mysqltest1.t2;
71 ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table `mysqltest1`.`t2`
72 set role none;
73 connection default;
74 grant reload on *.* to role1;
75 grant select on mysql.* to role1;
76 grant execute on procedure mysqltest1.pr1 to role1;
77 grant select on mysqltest1.t1 to role1;
78 grant select (a) on mysqltest1.t2 to role1;
79 connection foo;
80 set role role1;
81 flush tables;
82 select * from mysql.roles_mapping;
83 Host User Role Admin_option
84 localhost foo role1 N
85 localhost root role1 Y
86 show tables from mysqltest1;
87 Tables_in_mysqltest1
88 t1
89 t2
90 select * from mysqltest1.t1;
91 a b
92 1 2
93 3 4
94 select * from mysqltest1.t2;
95 ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table `mysqltest1`.`t2`
96 select a from mysqltest1.t2;
97 a
98 5
99 7
100 call mysqltest1.pr1();
101 pr1
102 pr1
103 connection default;
104 drop role role1;
105 connection foo;
106 flush tables;
107 select * from mysql.roles_mapping;
108 ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table `mysql`.`roles_mapping`
109 select * from mysqltest1.t1;
110 ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table `mysqltest1`.`t1`
111 select a from mysqltest1.t2;
112 ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table `mysqltest1`.`t2`
113 show grants;
114 Grants for foo@localhost
115 GRANT USAGE ON *.* TO `foo`@`localhost`
116 select * from information_schema.enabled_roles;
117 ROLE_NAME
118 NULL
119 select * from information_schema.enabled_roles;
120 ROLE_NAME
121 NULL
122 select current_role();
123 current_role()
124 role1
125 disconnect foo;
126 connection default;
127 drop user foo@localhost;
128 drop database mysqltest1;