call mysql.mysql_cluster_move_privileges();
select mysql.mysql_cluster_privileges_are_distributed();
mysql.mysql_cluster_privileges_are_distributed()
1
CREATE TABLE t1 (pk INT PRIMARY KEY, a INT) engine = ndb;
INSERT INTO t1 VALUES(0,0),(1,1),(2,2);
CREATE USER 'user'@'localhost' IDENTIFIED by 'mypass';
GRANT UPDATE ON t1 TO 'user'@'localhost';
SET PASSWORD FOR 'user'@'localhost'= PASSWORD('newpass');
RENAME USER 'user'@'localhost' TO 'newuser'@'localhost';
GRANT SELECT ON test.* TO 'newuser'@'localhost' IDENTIFIED by 'mypass2';
CREATE USER 'user2'@'localhost' IDENTIFIED by 'mypass';
GRANT SELECT ON test.t1 TO 'user2'@'localhost';
select distinct User,Password from mysql.user order by User;
User	Password
newuser	*1E9649BB3F345563008E37641B407AFF50E5835C
root	
user2	*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4
select User,Table_priv from mysql.tables_priv ORDER BY User;
User	Table_priv
newuser	Update
user2	Select
grant ALL PRIVILEGES on test.* to user@localhost with GRANT OPTION;
select User,Table_priv from mysql.tables_priv ORDER BY User;
User	Table_priv
newuser	Update
user2	Select
GRANT SELECT ON test.t1 TO 'user'@'localhost';
select User,Table_priv from mysql.tables_priv ORDER BY User;
User	Table_priv
newuser	Update
user	Select
user2	Select
FLUSH PRIVILEGES;
select User,Table_priv from mysql.tables_priv ORDER BY User;
User	Table_priv
newuser	Update
user	Select
user2	Select
REVOKE SELECT ON test.t1 FROM 'user'@'localhost';
select User,Table_priv from mysql.tables_priv ORDER BY User;
User	Table_priv
newuser	Update
user2	Select
CREATE USER 'user3'@'localhost' IDENTIFIED by 'mypass';
select distinct User,Password from mysql.user order by User;
User	Password
newuser	*1E9649BB3F345563008E37641B407AFF50E5835C
root	
user	
user2	*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4
user3	*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4
select User,Table_priv from mysql.tables_priv ORDER BY User;
User	Table_priv
newuser	Update
user2	Select
REVOKE UPDATE ON t1 FROM 'newuser'@'localhost';
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user'@'localhost';
select User,Table_priv from mysql.tables_priv ORDER BY User;
User	Table_priv
user2	Select
GRANT SELECT (a) ON test.t1 TO 'user'@'localhost';
select User, Table_name, Column_name from mysql.columns_priv ORDER BY User;
User	Table_name	Column_name
user	t1	a
FLUSH PRIVILEGES;
select distinct User,Password from mysql.user order by User;
User	Password
newuser	*1E9649BB3F345563008E37641B407AFF50E5835C
root	
user	
user2	*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4
user3	*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4
select User,Table_priv from mysql.tables_priv ORDER BY User;
User	Table_priv
user	
user2	Select
select User, Table_name, Column_name from mysql.columns_priv ORDER BY User;
User	Table_name	Column_name
user	t1	a
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user2'@'localhost';
select User,Table_priv from mysql.tables_priv ORDER BY User;
User	Table_priv
user	
select distinct User,Password from mysql.user order by User;
User	Password
newuser	*1E9649BB3F345563008E37641B407AFF50E5835C
root	
user	
user2	*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4
user3	*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4
select User,Table_priv from mysql.tables_priv ORDER BY User;
User	Table_priv
user	
connect(127.0.0.1,magnus,,test,MASTER_PORT,MASTER_SOCKET);
ERROR 28000: Access denied for user 'magnus'@'localhost' (using password: NO)
connect(127.0.0.1,magnus,,test,MASTER_PORT1,MASTER_SOCKET);
ERROR 28000: Access denied for user 'magnus'@'localhost' (using password: NO)
CREATE USER 'billy1'@'127.0.0.1' IDENTIFIED by 'mypass';
SELECT USER();
USER()
billy1@localhost
SELECT * FROM t1 order by pk;
pk	a
0	0
1	1
2	2
connect(127.0.0.1,billy1,,test,MASTER_PORT1,MASTER_SOCKET);
ERROR 28000: Access denied for user 'billy1'@'localhost' (using password: NO)
connect(127.0.0.1,billy1,wrongpass,test,MASTER_PORT1,MASTER_SOCKET);
ERROR 28000: Access denied for user 'billy1'@'localhost' (using password: YES)
BEGIN;
UPDATE mysql.user SET Password = ''
WHERE User = 'billy1';
SET PASSWORD FOR 'billy1'@'127.0.0.1' = PASSWORD('newpass');
ERROR 42000: Can't find any matching row in the user table
SHOW WARNINGS;
Level	Code	Message
Warning	1297	Got temporary error 266 'Time-out in NDB, probably caused by deadlock' from NDB
Error	1133	Can't find any matching row in the user table
ROLLBACK;
DROP USER 'billy1'@'127.0.0.1';
GRANT ALL ON *.* TO 'billy2'@'127.0.0.1';
SELECT USER();
USER()
billy2@localhost
SELECT * FROM t1 order by pk;
pk	a
0	0
1	1
2	2
DROP USER 'billy2'@'127.0.0.1';
=== making backup of new users ===
call mysql.mysql_cluster_backup_privileges();
==== clean up ====
DROP USER 'newuser'@'localhost';
DROP USER 'user2'@'localhost';
DROP USER 'user3'@'localhost';
DROP TABLE t1;
call mysql.mysql_cluster_backup_privileges();
call mysql.mysql_cluster_backup_privileges();
call mysql.mysql_cluster_restore_local_privileges();
call mysql.mysql_cluster_restore_local_privileges();
