blob: c0cdbc0e3386cf7f5dc587e62fb0ff7b68d1fa2f (
plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
|
#
# Testing CREATE/GRANT role
#
# On node_1
CREATE DATABASE test1;
CREATE TABLE test1.t1 (a int, b int);
CREATE TABLE test1.t2 (a int, b int);
INSERT INTO test1.t1 values (1,2),(3,4);
INSERT INTO test1.t2 values (5,6),(7,8);
CREATE PROCEDURE test1.pr1() SELECT "pr1";
CREATE USER foo@localhost;
CREATE ROLE role1;
GRANT role1 TO foo@localhost;
GRANT RELOAD ON *.* TO role1;
GRANT SELECT ON mysql.* TO role1;
GRANT EXECUTE ON PROCEDURE test1.pr1 TO role1;
GRANT SELECT ON test1.t1 TO role1;
GRANT SELECT (a) ON test1.t2 TO role1;
# Open connections to the 2 nodes using 'foo' user.
# Connect with foo_node_1
SHOW GRANTS;
Grants for foo@localhost
GRANT role1 TO 'foo'@'localhost'
GRANT USAGE ON *.* TO 'foo'@'localhost'
FLUSH TABLES;
ERROR 42000: Access denied; you need (at least one of) the RELOAD privilege(s) for this operation
SELECT * FROM mysql.roles_mapping;
ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 'roles_mapping'
SHOW TABLES FROM test1;
ERROR 42000: Access denied for user 'foo'@'localhost' to database 'test1'
SET ROLE role1;
FLUSH TABLES;
SELECT * FROM mysql.roles_mapping;
Host User Role Admin_option
localhost foo role1 N
localhost root role1 Y
SHOW TABLES FROM test1;
Tables_in_test1
t1
t2
SELECT * FROM test1.t1;
a b
1 2
3 4
SELECT * FROM test1.t2;
ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't2'
SELECT a FROM test1.t2;
a
5
7
CALL test1.pr1();
pr1
pr1
# Connect with foo_node_2
SHOW GRANTS;
Grants for foo@localhost
GRANT role1 TO 'foo'@'localhost'
GRANT USAGE ON *.* TO 'foo'@'localhost'
FLUSH TABLES;
ERROR 42000: Access denied; you need (at least one of) the RELOAD privilege(s) for this operation
SELECT * FROM mysql.roles_mapping;
ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 'roles_mapping'
SHOW TABLES FROM test1;
ERROR 42000: Access denied for user 'foo'@'localhost' to database 'test1'
SET ROLE role1;
FLUSH TABLES;
SELECT * FROM mysql.roles_mapping;
Host User Role Admin_option
role1 Y
localhost foo role1 N
SHOW TABLES FROM test1;
Tables_in_test1
t1
t2
SELECT * FROM test1.t1;
a b
1 2
3 4
SELECT * FROM test1.t2;
ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't2'
SELECT a FROM test1.t2;
a
5
7
CALL test1.pr1();
pr1
pr1
#
# Testing REVOKE role
#
#
# Connect with node_1
REVOKE EXECUTE ON PROCEDURE test1.pr1 FROM role1;
# Connect with foo_node_1
CALL test1.pr1();
ERROR 42000: execute command denied to user 'foo'@'localhost' for routine 'test1.pr1'
# Connect with foo_node_2
CALL test1.pr1();
ERROR 42000: execute command denied to user 'foo'@'localhost' for routine 'test1.pr1'
#
# Testing DROP role
#
# Connect with node_1
DROP ROLE role1;
# Connect with foo_node_1
FLUSH TABLES;
SELECT * FROM mysql.roles_mapping;
ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 'roles_mapping'
SELECT * FROM test1.t1;
ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't1'
SELECT a FROM test1.t2;
ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't2'
SHOW GRANTS;
Grants for foo@localhost
GRANT USAGE ON *.* TO 'foo'@'localhost'
SELECT * FROM INFORMATION_SCHEMA.ENABLED_ROLES;
ROLE_NAME
NULL
SELECT * FROM INFORMATION_SCHEMA.ENABLED_ROLES;
ROLE_NAME
NULL
SELECT CURRENT_ROLE();
CURRENT_ROLE()
role1
# Connect with foo_node_2
FLUSH TABLES;
SELECT * FROM mysql.roles_mapping;
ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 'roles_mapping'
SELECT * FROM test1.t1;
ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't1'
SELECT a FROM test1.t2;
ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't2'
SHOW GRANTS;
Grants for foo@localhost
GRANT USAGE ON *.* TO 'foo'@'localhost'
SELECT * FROM INFORMATION_SCHEMA.ENABLED_ROLES;
ROLE_NAME
NULL
SELECT * FROM INFORMATION_SCHEMA.ENABLED_ROLES;
ROLE_NAME
NULL
SELECT CURRENT_ROLE();
CURRENT_ROLE()
role1
# Connect with node_1
DROP USER foo@localhost;
DROP DATABASE test1;
# End of test
|
