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