include/master-slave.inc
[connection master]
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;
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
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
FLUSH PRIVILEGES;
REVOKE UPDATE ON t1 FROM 'newuser'@'localhost';
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user2'@'localhost';
select User,Table_priv from mysql.tables_priv ORDER BY User;
User Table_priv
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
select User, Table_name, Column_name from mysql.columns_priv ORDER BY User;
User Table_name Column_name
user t1 a
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
select User, Table_name, Column_name from mysql.columns_priv ORDER BY User;
User Table_name Column_name
user t1 a
DROP USER 'newuser'@'localhost';
== Showing binlog server1 ==
show binlog events from <binlog_start>;
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Query # # use `test`; CREATE USER 'user'@'localhost' IDENTIFIED by 'mypass'
master-bin.000001 # Query # # use `test`; GRANT UPDATE ON t1 TO 'user'@'localhost'
master-bin.000001 # Query # # use `test`; SET PASSWORD FOR 'user'@'localhost'='*D8DECEC305209EEFEC43008E1D420E1AA06B19E0'
master-bin.000001 # Query # # use `test`; RENAME USER 'user'@'localhost' TO 'newuser'@'localhost'
master-bin.000001 # Query # # use `test`; GRANT SELECT ON test.* TO 'newuser'@'localhost' IDENTIFIED by 'mypass2'
master-bin.000001 # Query # # use `test`; CREATE USER 'user2'@'localhost' IDENTIFIED by 'mypass'
master-bin.000001 # Query # # use `test`; GRANT SELECT ON test.t1 TO 'user2'@'localhost'
master-bin.000001 # Query # # use `test`; grant ALL PRIVILEGES on test.* to user@localhost with GRANT OPTION
master-bin.000001 # Query # # use `test`; GRANT SELECT ON test.t1 TO 'user'@'localhost'
master-bin.000001 # Query # # REVOKE SELECT ON test.t1 FROM 'user'@'localhost'
master-bin.000001 # Query # # CREATE USER 'user3'@'localhost' IDENTIFIED by 'mypass'
master-bin.000001 # Query # # REVOKE UPDATE ON t1 FROM 'newuser'@'localhost'
master-bin.000001 # Query # # REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user2'@'localhost'
master-bin.000001 # Query # # GRANT SELECT (a) ON test.t1 TO 'user'@'localhost'
master-bin.000001 # Query # # use `test`; FLUSH PRIVILEGES
master-bin.000001 # Query # # DROP USER 'newuser'@'localhost'
== Showing binlog server2 ==
show binlog events from <binlog_start>;
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Query # # CREATE USER 'user'@'localhost' IDENTIFIED by 'mypass'
master-bin.000001 # Query # # GRANT UPDATE ON t1 TO 'user'@'localhost'
master-bin.000001 # Query # # SET PASSWORD FOR 'user'@'localhost'='*D8DECEC305209EEFEC43008E1D420E1AA06B19E0'
master-bin.000001 # Query # # RENAME USER 'user'@'localhost' TO 'newuser'@'localhost'
master-bin.000001 # Query # # GRANT SELECT ON test.* TO 'newuser'@'localhost' IDENTIFIED by 'mypass2'
master-bin.000001 # Query # # CREATE USER 'user2'@'localhost' IDENTIFIED by 'mypass'
master-bin.000001 # Query # # GRANT SELECT ON test.t1 TO 'user2'@'localhost'
master-bin.000001 # Query # # grant ALL PRIVILEGES on test.* to user@localhost with GRANT OPTION
master-bin.000001 # Query # # GRANT SELECT ON test.t1 TO 'user'@'localhost'
master-bin.000001 # Query # # use `test`; FLUSH PRIVILEGES
master-bin.000001 # Query # # use `test`; REVOKE SELECT ON test.t1 FROM 'user'@'localhost'
master-bin.000001 # Query # # use `test`; CREATE USER 'user3'@'localhost' IDENTIFIED by 'mypass'
master-bin.000001 # Query # # use `test`; FLUSH PRIVILEGES
master-bin.000001 # Query # # use `test`; REVOKE UPDATE ON t1 FROM 'newuser'@'localhost'
master-bin.000001 # Query # # use `test`; REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user2'@'localhost'
master-bin.000001 # Query # # use `test`; GRANT SELECT (a) ON test.t1 TO 'user'@'localhost'
master-bin.000001 # Query # # use `test`; DROP USER 'newuser'@'localhost'
select distinct User,Password from mysql.user order by User;
User Password
root
user
user2 *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4
user3 *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4
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
root
user
user2 *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4
user3 *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4
select User,Table_priv from mysql.tables_priv ORDER BY User;
User Table_priv
user
BEGIN;
UPDATE mysql.user SET Password = ''
WHERE User = 'user2';
SET PASSWORD FOR 'user2'@'localhost' = 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
== Showing binlog server2 ==
show binlog events from <binlog_start>;
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Query # # CREATE USER 'user'@'localhost' IDENTIFIED by 'mypass'
master-bin.000001 # Query # # GRANT UPDATE ON t1 TO 'user'@'localhost'
master-bin.000001 # Query # # SET PASSWORD FOR 'user'@'localhost'='*D8DECEC305209EEFEC43008E1D420E1AA06B19E0'
master-bin.000001 # Query # # RENAME USER 'user'@'localhost' TO 'newuser'@'localhost'
master-bin.000001 # Query # # GRANT SELECT ON test.* TO 'newuser'@'localhost' IDENTIFIED by 'mypass2'
master-bin.000001 # Query # # CREATE USER 'user2'@'localhost' IDENTIFIED by 'mypass'
master-bin.000001 # Query # # GRANT SELECT ON test.t1 TO 'user2'@'localhost'
master-bin.000001 # Query # # grant ALL PRIVILEGES on test.* to user@localhost with GRANT OPTION
master-bin.000001 # Query # # GRANT SELECT ON test.t1 TO 'user'@'localhost'
master-bin.000001 # Query # # use `test`; FLUSH PRIVILEGES
master-bin.000001 # Query # # use `test`; REVOKE SELECT ON test.t1 FROM 'user'@'localhost'
master-bin.000001 # Query # # use `test`; CREATE USER 'user3'@'localhost' IDENTIFIED by 'mypass'
master-bin.000001 # Query # # use `test`; FLUSH PRIVILEGES
master-bin.000001 # Query # # use `test`; REVOKE UPDATE ON t1 FROM 'newuser'@'localhost'
master-bin.000001 # Query # # use `test`; REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user2'@'localhost'
master-bin.000001 # Query # # use `test`; GRANT SELECT (a) ON test.t1 TO 'user'@'localhost'
master-bin.000001 # Query # # use `test`; DROP USER 'newuser'@'localhost'
ROLLBACK;
=== making backup of new users ===
call mysql.mysql_cluster_backup_privileges();
==== clean up ====
DROP USER 'user2'@'localhost';
DROP USER 'user3'@'localhost';
DROP TABLE t1;
STOP SLAVE;
call mysql.mysql_cluster_backup_privileges();
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();
call mysql.mysql_cluster_restore_local_privileges();