Debian Codegraph @

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

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