| 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@localhost; |
| 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@localhost, 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@localhost; |
| 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 ndb_u1@a, ndb_u6@localhost; |
| 78 |
DROP USER local_u1@a, local_u3@a, local_u4@a; |
| 79 |
DROP USER local_u2@localhost, local_u5@localhost; |
| 80 |
DROP ROLE L3; |
| 81 |
DROP USER Monty@localhost, David@localhost; |
| 82 |
DROP USER ndb_u4@localhost, ndb_u5@localhost; |
| 83 |
DROP USER ndb_u6@localhost; |
| 84 |
DROP ROLE R3; |
| 85 |
DROP DATABASE auth_test_db; |