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