Debian Codegraph @

mysql-test/suite/ndb/r/apply_stored_grants.result

raw
1 Connected to server1 as root
2 CREATE DATABASE auth_test_db;
3 USE auth_test_db;
4 CREATE TABLE t1 (i int primary key not null) engine = ndb;
5 CREATE TABLE t2 (i int primary key not null) engine = ndb;
6 CREATE TABLE t3 (i int primary key not null) engine = ndb;
7 INSERT INTO t1 VALUES(1);
8 INSERT INTO t2 VALUES(1),(2);
9 INSERT INTO t3 VALUES(1),(2),(3);
10 CREATE USER local_u1@a, local_u2@a, local_u3@a, local_u4@a, local_u5@a;
11 CREATE ROLE L1, L2, L3;
12 CREATE USER ndb_u1@a;
13 CREATE USER ndb_u2@a IDENTIFIED BY 'Helsinki';
14 CREATE USER ndb_u3@a, ndb_u4@a, ndb_u5@a, ndb_u6@a;
15 CREATE USER ndb_u7@a IDENTIFIED BY 'pass7';
16 CREATE ROLE R1, R2, R3;
17 GRANT NDB_STORED_USER ON *.* to ndb_u1@a;
18 GRANT NDB_STORED_USER ON *.* to ndb_u2@a, ndb_u3@a, ndb_u4@a;
19 GRANT ALL ON *.* TO ndb_u5@a;
20 GRANT NDB_STORED_USER ON *.* to ndb_u6@a, ndb_u7@a;
21 GRANT NDB_STORED_USER ON *.* TO R1, R2, R3;
22 GRANT SELECT, INSERT, DELETE ON auth_test_db.t2 to R2;
23 GRANT R3 to ndb_u3@a;
24 GRANT L3 to local_u3@a;
25 GRANT R2 TO ndb_u2@a, local_u2@a;
26 GRANT SELECT,INSERT,DELETE ON auth_test_db.t2 to R3, L3;
27 GRANT SELECT ON auth_test_db.t1 to ndb_u1@a;
28 GRANT SELECT ON auth_test_db.t1 to local_u1@a;
29 GRANT INSERT ON auth_test_db.t1 to local_u2@a;
30 GRANT SELECT,INSERT ON auth_test_db.t1 to ndb_u2@a, ndb_u3@a, local_u3@a;
31 GRANT ALL ON auth_test_db.* to ndb_u4@a;
32 GRANT UPDATE ON auth_test_db.t2 to ndb_u2@a;
33 ALTER USER ndb_u5@a IDENTIFIED BY 'pass5';
34 ALTER USER local_u5@a IDENTIFIED BY 'pass5';
35 ALTER USER ndb_u4@a IDENTIFIED BY 'pass4', local_u4@a IDENTIFIED BY 'pass4';
36 ALTER USER ndb_u3@a DEFAULT ROLE R3;
37 ALTER USER local_u3@a DEFAULT ROLE L3;
38 SET DEFAULT ROLE R2 TO ndb_u2@a, local_u2@a;
39 SET PASSWORD FOR local_u3@a = 'Trondheim';
40 SET PASSWORD FOR ndb_u3@a = 'Uppsala';
41 REVOKE INSERT ON auth_test_db.t1 FROM local_u3@a;
42 REVOKE INSERT ON t1 FROM ndb_u3@a;
43 REVOKE SELECT ON auth_test_db.t1 FROM ndb_u2@a, local_u2@a;
44 REVOKE ALL, GRANT OPTION FROM ndb_u1@a, local_u1@a;
45 REVOKE NDB_STORED_USER ON *.* FROM ndb_u6@a;
46 DROP ROLE R1;
47 DROP ROLE L1;
48 DROP ROLE R2, L2;
49 DROP USER ndb_u1@a, ndb_u7@a;
50 RENAME USER ndb_u3@a TO bob@a, ndb_u2@a TO Monty@localhost, bob@a TO ndb_u2@a;
51 RENAME USER ndb_u2@a TO David@localhost;
52 RENAME USER ndb_u4@a TO ndb_u4@localhost;
53 RENAME USER local_u2@a TO local_u2@localhost;
54 RENAME USER ndb_u5@a TO ndb_u5@localhost, local_u5@a TO local_u5@localhost;
55 CREATE TABLE ib_table (pk INT PRIMARY KEY, a INT) engine = innodb;
56 INSERT INTO ib_table VALUES(0,0),(1,1),(2,2);
57 GRANT SELECT ON ib_table TO R3;
58 REVOKE SELECT ON ib_table FROM R3;
59 GRANT SELECT ON nonexistent_table TO R3;
60 ERROR 42S02: Table 'auth_test_db.nonexistent_table' doesn't exist
61 REVOKE SELECT ON nonexistent_table FROM R3;
62 ERROR 42000: There is no such grant defined for user 'R3' on host '%' on table 'nonexistent_table'
63 DELETE FROM t2 where i=3;
64 ERROR 42000: DELETE command denied to user 'Monty'@'localhost' for table 't2'
65 SELECT * FROM t1;
66 ERROR 42000: SELECT command denied to user 'Monty'@'localhost' for table 't1'
67 INSERT INTO t1 values (2);
68 DELETE FROM t2 where i=3;
69 SELECT * FROM t1 ORDER BY i;
70 i
71 1
72 2
73 INSERT INTO t1 values (45);
74 ERROR 42000: INSERT command denied to user 'David'@'localhost' for table 't1'
75 DROP TABLE t1;
76 DROP TABLE t2, t3;
77 DROP USER local_u1@a, local_u3@a, local_u4@a;
78 DROP USER local_u2@localhost, local_u5@localhost;
79 DROP ROLE L3;
80 DROP USER Monty@localhost, David@localhost;
81 DROP USER ndb_u4@localhost, ndb_u5@localhost;
82 DROP USER ndb_u6@a;
83 DROP ROLE R3;
84 DROP DATABASE auth_test_db;