1call mysql.mysql_cluster_move_privileges(); 2select mysql.mysql_cluster_privileges_are_distributed(); 3mysql.mysql_cluster_privileges_are_distributed() 41 5CREATE TABLE t1 (pk INT PRIMARY KEY, a INT) engine = ndb; 6INSERT INTO t1 VALUES(0,0),(1,1),(2,2); 7CREATE USER 'user'@'localhost' IDENTIFIED by 'mypass'; 8GRANT UPDATE ON t1 TO 'user'@'localhost'; 9SET PASSWORD FOR 'user'@'localhost'= 'newpass'; 10RENAME USER 'user'@'localhost' TO 'newuser'@'localhost'; 11GRANT SELECT ON test.* TO 'newuser'@'localhost' IDENTIFIED by 'mypass2'; 12Warnings: 13Warning 1287 Using GRANT statement to modify existing user's properties other than privileges is deprecated and will be removed in future release. Use ALTER USER statement for this operation. 14CREATE USER 'user2'@'localhost' IDENTIFIED by 'mypass'; 15GRANT SELECT ON test.t1 TO 'user2'@'localhost'; 16select distinct User,Authentication_string from mysql.user order by User; 17User Authentication_string 18newuser *1E9649BB3F345563008E37641B407AFF50E5835C 19root 20user2 *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 21select User,Table_priv from mysql.tables_priv ORDER BY User; 22User Table_priv 23newuser Update 24user2 Select 25CREATE USER 'user'@'localhost'; 26grant ALL PRIVILEGES on test.* to user@localhost with GRANT OPTION; 27select User,Table_priv from mysql.tables_priv ORDER BY User; 28User Table_priv 29newuser Update 30user2 Select 31GRANT SELECT ON test.t1 TO 'user'@'localhost'; 32select User,Table_priv from mysql.tables_priv ORDER BY User; 33User Table_priv 34newuser Update 35user Select 36user2 Select 37FLUSH PRIVILEGES; 38select User,Table_priv from mysql.tables_priv ORDER BY User; 39User Table_priv 40newuser Update 41user Select 42user2 Select 43REVOKE SELECT ON test.t1 FROM 'user'@'localhost'; 44select User,Table_priv from mysql.tables_priv ORDER BY User; 45User Table_priv 46newuser Update 47user2 Select 48CREATE USER 'user3'@'localhost' IDENTIFIED by 'mypass'; 49select distinct User,Authentication_string from mysql.user order by User; 50User Authentication_string 51newuser *1E9649BB3F345563008E37641B407AFF50E5835C 52root 53user 54user2 *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 55user3 *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 56select User,Table_priv from mysql.tables_priv ORDER BY User; 57User Table_priv 58newuser Update 59user2 Select 60REVOKE UPDATE ON t1 FROM 'newuser'@'localhost'; 61REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user'@'localhost'; 62select User,Table_priv from mysql.tables_priv ORDER BY User; 63User Table_priv 64user2 Select 65GRANT SELECT (a) ON test.t1 TO 'user'@'localhost'; 66select User, Table_name, Column_name from mysql.columns_priv ORDER BY User; 67User Table_name Column_name 68user t1 a 69FLUSH PRIVILEGES; 70select distinct User,Authentication_string from mysql.user order by User; 71User Authentication_string 72newuser *1E9649BB3F345563008E37641B407AFF50E5835C 73root 74user 75user2 *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 76user3 *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 77select User,Table_priv from mysql.tables_priv ORDER BY User; 78User Table_priv 79user 80user2 Select 81select User, Table_name, Column_name from mysql.columns_priv ORDER BY User; 82User Table_name Column_name 83user t1 a 84REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user2'@'localhost'; 85select User,Table_priv from mysql.tables_priv ORDER BY User; 86User Table_priv 87user 88select distinct User,Authentication_string from mysql.user order by User; 89User Authentication_string 90newuser *1E9649BB3F345563008E37641B407AFF50E5835C 91root 92user 93user2 *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 94user3 *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 95select User,Table_priv from mysql.tables_priv ORDER BY User; 96User Table_priv 97user 98connect(127.0.0.1,magnus,,test,MASTER_PORT,MASTER_SOCKET); 99ERROR 28000: Access denied for user 'magnus'@'localhost' (using password: NO) 100connect(127.0.0.1,magnus,,test,MASTER_PORT1,MASTER_SOCKET); 101ERROR 28000: Access denied for user 'magnus'@'localhost' (using password: NO) 102CREATE USER 'billy1'@'127.0.0.1' IDENTIFIED by 'mypass'; 103SELECT USER(); 104USER() 105billy1@localhost 106SELECT * FROM t1 order by pk; 107pk a 1080 0 1091 1 1102 2 111connect(127.0.0.1,billy1,,test,MASTER_PORT1,MASTER_SOCKET); 112ERROR 28000: Access denied for user 'billy1'@'localhost' (using password: NO) 113connect(127.0.0.1,billy1,wrongpass,test,MASTER_PORT1,MASTER_SOCKET); 114ERROR 28000: Access denied for user 'billy1'@'localhost' (using password: YES) 115BEGIN; 116UPDATE mysql.user SET Authentication_string = '' 117WHERE User = 'billy1'; 118SET PASSWORD FOR 'billy1'@'127.0.0.1' = 'newpass'; 119ERROR HY000: Lock wait timeout exceeded; try restarting transaction 120SHOW WARNINGS; 121Level Code Message 122Warning 1297 Got temporary error 266 'Time-out in NDB, probably caused by deadlock' from NDB 123Error 1205 Lock wait timeout exceeded; try restarting transaction 124Warning 1297 Got temporary error 274 'Time-out in NDB, probably caused by deadlock' from NDB 125Warning 1297 Got temporary error 274 'Time-out in NDB, probably caused by deadlock' from NDB 126Error 1205 Lock wait timeout exceeded; try restarting transaction 127ROLLBACK; 128DROP USER 'billy1'@'127.0.0.1'; 129set @orig_sql_mode= @@sql_mode; 130set SESSION sql_mode= (select replace(@@sql_mode,'NO_AUTO_CREATE_USER','')); 131Warnings: 132Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. 133GRANT ALL ON *.* TO 'billy2'@'127.0.0.1'; 134Warnings: 135Warning 1287 Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. 136set SESSION sql_mode = @orig_sql_mode; 137Warnings: 138Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. 139SELECT USER(); 140USER() 141billy2@localhost 142SELECT * FROM t1 order by pk; 143pk a 1440 0 1451 1 1462 2 147DROP USER 'billy2'@'127.0.0.1'; 148=== making backup of new users === 149call mysql.mysql_cluster_backup_privileges(); 150==== clean up ==== 151DROP USER 'newuser'@'localhost'; 152DROP USER 'user2'@'localhost'; 153DROP USER 'user3'@'localhost'; 154DROP TABLE t1; 155=== test restart of mysqld === 156# restart 157flush privileges; 158=== test truncate === 159truncate mysql.proxies_priv; 160flush privileges; 161=== test mysql_upgrade === 162mysql_upgrade first mysqld... 163mysql_upgrade second mysqld... 164show that tables still are in NDB 165table_name 166columns_priv 167db 168procs_priv 169proxies_priv 170tables_priv 171user 172=== test mysql_upgrade of two mysqlds === 173DROP DATABASE sys; 174DROP DATABASE sys; 175mysql_upgrade first mysqld... 176mysql_upgrade second mysqld... 177call mysql.mysql_cluster_backup_privileges(); 178call mysql.mysql_cluster_backup_privileges(); 179call mysql.mysql_cluster_restore_local_privileges(); 180call mysql.mysql_cluster_restore_local_privileges(); 181