Debian Codegraph @

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

raw
1 create user 'test_user'@'localhost';
2 create role test_role1;
3 create role test_role2;
4 create role test_role3;
5 grant test_role1 to test_user@localhost;
6 grant test_role3 to test_user@localhost;
7 grant test_role2 to test_role1;
8 select user, host from mysql.user where user not like 'root';
9 User Host
10 mariadb.sys localhost
11 test_role1
12 test_role2
13 test_role3
14 test_user localhost
15 select * from mysql.roles_mapping;
16 Host User Role Admin_option
17 test_role1 test_role2 N
18 localhost root test_role1 Y
19 localhost root test_role2 Y
20 localhost root test_role3 Y
21 localhost test_user test_role1 N
22 localhost test_user test_role3 N
23 create function mysql.test_func (s CHAR(20))
24 returns CHAR(50) DETERMINISTIC
25 return concat('Test string: ',s);
26 create procedure mysql.test_proc (OUT param1 INT)
27 begin
28 select COUNT(*) into param1 from mysql.roles_mapping;
29 end|
30 grant execute on function mysql.test_func to test_role2;
31 grant execute on procedure mysql.test_proc to test_role2;
32 grant execute on mysql.* to test_role3;
33 show grants;
34 Grants for test_user@localhost
35 GRANT USAGE ON *.* TO `test_user`@`localhost`
36 GRANT `test_role1` TO `test_user`@`localhost`
37 GRANT `test_role3` TO `test_user`@`localhost`
38 use mysql;
39 ERROR 42000: Access denied for user 'test_user'@'localhost' to database 'mysql'
40 select current_user(), current_role();
41 current_user() current_role()
42 test_user@localhost NULL
43 set role test_role1;
44 select current_user(), current_role();
45 current_user() current_role()
46 test_user@localhost test_role1
47 use mysql;
48 call test_proc(@a);
49 SELECT @a;
50 @a
51 6
52 SELECT test_func('AABBCCDD');
53 test_func('AABBCCDD')
54 Test string: AABBCCDD
55 show grants;
56 Grants for test_user@localhost
57 GRANT EXECUTE ON FUNCTION `mysql`.`test_func` TO `test_role2`
58 GRANT EXECUTE ON PROCEDURE `mysql`.`test_proc` TO `test_role2`
59 GRANT USAGE ON *.* TO `test_role1`
60 GRANT USAGE ON *.* TO `test_role2`
61 GRANT USAGE ON *.* TO `test_user`@`localhost`
62 GRANT `test_role1` TO `test_user`@`localhost`
63 GRANT `test_role2` TO `test_role1`
64 GRANT `test_role3` TO `test_user`@`localhost`
65 set role none;
66 select current_user(), current_role();
67 current_user() current_role()
68 test_user@localhost NULL
69 show grants;
70 Grants for test_user@localhost
71 GRANT USAGE ON *.* TO `test_user`@`localhost`
72 GRANT `test_role1` TO `test_user`@`localhost`
73 GRANT `test_role3` TO `test_user`@`localhost`
74 call test_proc(@a);
75 ERROR 42000: execute command denied to user 'test_user'@'localhost' for routine 'mysql.test_proc'
76 SELECT test_func('AABBCCDD');
77 ERROR 42000: execute command denied to user 'test_user'@'localhost' for routine 'mysql.test_func'
78 set role test_role3;
79 select current_user(), current_role();
80 current_user() current_role()
81 test_user@localhost test_role3
82 show grants;
83 Grants for test_user@localhost
84 GRANT EXECUTE ON `mysql`.* TO `test_role3`
85 GRANT USAGE ON *.* TO `test_role3`
86 GRANT USAGE ON *.* TO `test_user`@`localhost`
87 GRANT `test_role1` TO `test_user`@`localhost`
88 GRANT `test_role3` TO `test_user`@`localhost`
89 call test_proc(@a);
90 SELECT @a;
91 @a
92 6
93 SELECT test_func('AABBCCDD');
94 test_func('AABBCCDD')
95 Test string: AABBCCDD
96 drop user 'test_user'@'localhost';
97 revoke execute on function mysql.test_func from test_role2;
98 revoke execute on procedure mysql.test_proc from test_role2;
99 revoke execute on mysql.* from test_role3;
100 delete from mysql.user where user like'test_%';
101 delete from mysql.roles_mapping where Role like 'test%';
102 drop function mysql.test_func;
103 drop procedure mysql.test_proc;
104 flush privileges;