Debian Codegraph @

mysql-test/suite/roles/set_role-routine-simple.test

raw
1 source include/not_embedded.inc;
2
3 create user 'test_user'@'localhost';
4 create role test_role1;
5 create role test_role2;
6 create role test_role3;
7
8 grant test_role1 to test_user@localhost;
9 grant test_role3 to test_user@localhost;
10 grant test_role2 to test_role1;
11
12 --sorted_result
13 select user, host from mysql.user where user not like 'root';
14 --sorted_result
15 select * from mysql.roles_mapping;
16
17 create function mysql.test_func (s CHAR(20))
18 returns CHAR(50) DETERMINISTIC
19 return concat('Test string: ',s);
20
21
22 delimiter |;
23 create procedure mysql.test_proc (OUT param1 INT)
24 begin
25 select COUNT(*) into param1 from mysql.roles_mapping;
26 end|
27 delimiter ;|
28
29 grant execute on function mysql.test_func to test_role2;
30 grant execute on procedure mysql.test_proc to test_role2;
31
32 grant execute on mysql.* to test_role3;
33
34 change_user 'test_user';
35 --sorted_result
36 show grants;
37
38 --error ER_DBACCESS_DENIED_ERROR
39 use mysql;
40 select current_user(), current_role();
41 set role test_role1;
42 select current_user(), current_role();
43 use mysql;
44
45 call test_proc(@a);
46 SELECT @a;
47
48 SELECT test_func('AABBCCDD');
49
50 --sorted_result
51 show grants;
52 set role none;
53 select current_user(), current_role();
54 --sorted_result
55 show grants;
56
57 --error ER_PROCACCESS_DENIED_ERROR
58 call test_proc(@a);
59
60 --error ER_PROCACCESS_DENIED_ERROR
61 SELECT test_func('AABBCCDD');
62
63 set role test_role3;
64 select current_user(), current_role();
65 --sorted_result
66 show grants;
67 call test_proc(@a);
68 SELECT @a;
69
70 SELECT test_func('AABBCCDD');
71
72 change_user 'root';
73 drop user 'test_user'@'localhost';
74 revoke execute on function mysql.test_func from test_role2;
75 revoke execute on procedure mysql.test_proc from test_role2;
76 revoke execute on mysql.* from test_role3;
77 delete from mysql.user where user like'test_%';
78 delete from mysql.roles_mapping where Role like 'test%';
79 drop function mysql.test_func;
80 drop procedure mysql.test_proc;
81 flush privileges;