Debian Codegraph @

mysql-test/suite/ndb/t/distributed_grants.test

raw
1 # WL#12637 Distribute ACL changes in MySQL Cluster
2
3 --source include/have_ndb.inc
4
5 connection default;
6 --echo Connected to server1 as root
7 #
8 # Create test tables. These are not in database "test" due to the customary
9 # "GRANT ALL ON test.* to '%'@'localhost'" in effect there.
10 #
11 CREATE DATABASE auth_test_db;
12 USE auth_test_db;
13 CREATE TABLE t1 (i int primary key not null) engine = ndb;
14 CREATE TABLE t2 (i int primary key not null) engine = ndb;
15 CREATE TABLE t3 (i int primary key not null) engine = ndb;
16 INSERT INTO t1 VALUES(1);
17 INSERT INTO t2 VALUES(1),(2);
18 INSERT INTO t3 VALUES(1),(2),(3);
19
20 #
21 # Create local users
22 #
23 CREATE USER local_u1@a, local_u2@a, local_u3@a, local_u4@a, local_u5@a;
24 CREATE ROLE L1, L2, L3;
25
26 # Subsequent comments refer to specific grants by line number
27 #
28 # Create distributed users, start at line 30
29 #
30 CREATE USER ndb_u1@a;
31 CREATE USER ndb_u2@a IDENTIFIED BY 'Helsinki';
32 CREATE USER ndb_u3@a, ndb_u4@a, ndb_u5@a, ndb_u6@localhost;
33 CREATE USER ndb_u7@a IDENTIFIED BY 'pass7';
34 CREATE ROLE R1, R2, R3;
35 GRANT NDB_STORED_USER ON *.* to ndb_u1@a;
36 GRANT NDB_STORED_USER ON *.* to ndb_u2@a, ndb_u3@a, ndb_u4@a;
37 GRANT ALL ON *.* TO ndb_u5@a; # Grant NDB_STORED_USER via ALL
38 GRANT NDB_STORED_USER ON *.* to ndb_u6@localhost, ndb_u7@a;
39 GRANT NDB_STORED_USER ON *.* TO R1, R2, R3;
40
41 #
42 # GRANT statements start at line 45
43 #
44 GRANT SELECT, INSERT, DELETE ON auth_test_db.t2 to R2;
45 GRANT R3 to ndb_u3@a;
46 GRANT L3 to local_u3@a;
47 GRANT R2 TO ndb_u2@a, local_u2@a;
48 GRANT SELECT,INSERT,DELETE ON auth_test_db.t2 to R3, L3;
49 GRANT SELECT ON auth_test_db.t1 to ndb_u1@a;
50 GRANT SELECT ON auth_test_db.t1 to local_u1@a;
51 GRANT INSERT ON auth_test_db.t1 to local_u2@a;
52 GRANT SELECT,INSERT ON auth_test_db.t1 to ndb_u2@a, ndb_u3@a, local_u3@a;
53 GRANT ALL ON auth_test_db.* to ndb_u4@a;
54 GRANT UPDATE ON auth_test_db.t2 to ndb_u2@a;
55
56 #
57 # ALTER USER statements start at line 60
58 #
59 ALTER USER ndb_u5@a IDENTIFIED BY 'pass5'; # NDB ONLY
60 ALTER USER local_u5@a IDENTIFIED BY 'pass5'; # LOCAL ONLY
61 ALTER USER ndb_u4@a IDENTIFIED BY 'pass4', local_u4@a IDENTIFIED BY 'pass4';
62
63
64
65
66 #
67 # SET DEFAULT ROLE statements start at line 70
68 #
69 ALTER USER ndb_u3@a DEFAULT ROLE R3;
70 ALTER USER local_u3@a DEFAULT ROLE L3;
71 SET DEFAULT ROLE R2 TO ndb_u2@a, local_u2@a;
72
73
74
75
76 #
77 # SET PASSWORD statements start at line 80
78 #
79 SET PASSWORD FOR local_u3@a = 'Trondheim'; # LOCAL ONLY
80 SET PASSWORD FOR ndb_u3@a = 'Uppsala'; # NDB ONLY
81
82
83
84
85
86 #
87 # REVOKE statements start at line 90
88 #
89 REVOKE INSERT ON auth_test_db.t1 FROM local_u3@a;
90 REVOKE INSERT ON t1 FROM ndb_u3@a;
91 REVOKE SELECT ON auth_test_db.t1 FROM ndb_u2@a, local_u2@a;
92 REVOKE ALL, GRANT OPTION FROM ndb_u1@a, local_u1@a;
93 REVOKE NDB_STORED_USER ON *.* FROM ndb_u6@localhost;
94
95 #
96 # DROP statements start at line 100
97 #
98 DROP ROLE R1; # NDB ONLY
99 DROP ROLE L1; # LOCAL ONLY
100 DROP ROLE R2, L2; # NDB+LOCAL
101 DROP USER ndb_u1@a, ndb_u7@a;
102
103
104
105 #
106 # RENAME USER
107 #
108 RENAME USER ndb_u3@a TO bob@a, ndb_u2@a TO Monty@localhost, bob@a TO ndb_u2@a;
109 RENAME USER ndb_u2@a TO David@localhost;
110 RENAME USER ndb_u4@a TO ndb_u4@localhost;
111 RENAME USER local_u2@a TO local_u2@localhost;
112 RENAME USER ndb_u5@a TO ndb_u5@localhost, local_u5@a TO local_u5@localhost;
113
114
115 #
116 # GRANT and REVOKE to an NDB_STORED_USER on an InnoDB table (which exists
117 # only on the mysqld1, not mysqld2).
118 #
119 CREATE TABLE ib_table (pk INT PRIMARY KEY, a INT) engine = innodb;
120 INSERT INTO ib_table VALUES(0,0),(1,1),(2,2);
121 GRANT SELECT ON ib_table TO R3;
122 REVOKE SELECT ON ib_table FROM R3;
123
124
125 #
126 # GRANT and REVOKE on a table that does not exist on the local server
127 #
128 --error ER_NO_SUCH_TABLE
129 GRANT SELECT ON nonexistent_table TO R3;
130 --error ER_NONEXISTING_TABLE_GRANT
131 REVOKE SELECT ON nonexistent_table FROM R3;
132
133 #
134 # ========= Test connections from the other server in the cluster ========
135 #
136
137 # Monty (formerly ndb_u2) has password 'Helsinki', has NDB_STORED_USER,
138 # has INSERT on auth_test_db.t1 (granted at line 53), but SELECT was revoked
139 # (line 117). Monty has UPDATE on auth_test_db.t2 (granted at 79), and once
140 # had DELETE on t2 by way of R2 (line 48), but no longer has this because
141 # R2 was dropped (line 102).
142 #
143 connect (CU2,127.0.0.1,Monty,Helsinki,auth_test_db,$MASTER_MYPORT1,);
144 connection CU2;
145 --error ER_TABLEACCESS_DENIED_ERROR
146 DELETE FROM t2 where i=3;
147 --error ER_TABLEACCESS_DENIED_ERROR
148 SELECT * FROM t1;
149 INSERT INTO t1 values (2);
150
151
152 # David (formerly ndb_u3) has password 'Uppsala', has NDB_STORED_USER,
153 # has role R3 (line 46) and has SELECT,INSERT,DELETE on t2 by way of R3,
154 # and has SELECT on t1 (granted at line 53), but not INSERT (revoked at 91).
155 #
156 connect (CU3,127.0.0.1,David,Uppsala,auth_test_db,$MASTER_MYPORT1,);
157 connection CU3;
158 DELETE FROM t2 where i=3;
159 SELECT * FROM t1 ORDER BY i;
160 --error ER_TABLEACCESS_DENIED_ERROR
161 INSERT INTO t1 values (45);
162
163
164 # ndb_u4 has password 'pass4' and all table privileges on auth_test_db.*
165 connect (CU4,127.0.0.1,ndb_u4,pass4,auth_test_db,$MASTER_MYPORT1,);
166 connection CU4;
167 DROP TABLE t1;
168
169 # ndb_u6 has an empty password. Test connection only.
170 connect (CU6,127.0.0.1,ndb_u6,,test,$MASTER_MYPORT1,);
171
172 # ndb_u5 has password 'pass5' and all privileges.
173 connect (CU5,127.0.0.1,ndb_u5,pass5,auth_test_db,$MASTER_MYPORT1,);
174 connection CU5;
175 DROP TABLE t2, t3;
176 # Users that have had NDB_STORED_USER revoked must be dropped on each server,
177 # and ndb_u5 has the privilege to do this (via GRANT ALL at line 37)
178 DROP USER ndb_u1@a, ndb_u6@localhost;
179
180
181
182 # ======= CLEANUP
183 # =======
184 connection default;
185
186 DROP USER local_u1@a, local_u3@a, local_u4@a;
187 DROP USER local_u2@localhost, local_u5@localhost;
188 DROP ROLE L3;
189
190 DROP USER Monty@localhost, David@localhost;
191 DROP USER ndb_u4@localhost, ndb_u5@localhost;
192 DROP USER ndb_u6@localhost;
193 DROP ROLE R3;
194
195 DROP DATABASE auth_test_db;
196
197 # Delete the "REVOKE" statements left in ndb_sql_metadata
198 --disable_result_log
199 --exec $NDB_DELETE_ALL -d mysql ndb_sql_metadata