1RESET MASTER; 2CREATE USER user1@localhost IDENTIFIED BY 'secret'; 3CREATE USER user2@localhost IDENTIFIED BY 'secret'; 4SET PASSWORD FOR user1@localhost = 'secret'; 5# Test the password is encrypted in binary log 6# when granting privileges on tables. 7GRANT UPDATE ON *.* TO user1@localhost IDENTIFIED BY 'secret' WITH GRANT OPTION; 8Warnings: 9Warning 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. 10GRANT UPDATE ON *.* /*before to*/TO/*after to*/ user1@localhost IDENTIFIED BY 'secret', user2@localhost IDENTIFIED BY 'secret2' /*before with*/WITH/*after with*/MAX_QUERIES_PER_HOUR 90; 11Warnings: 12Warning 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. 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. 14GRANT UPDATE ON *.* /*before to*/TO/*after to*/ user1@localhost IDENTIFIED BY 'secret', user2@localhost IDENTIFIED BY 'secret2' /*before require*/REQUIRE SSL/*after require*/ /*before with*/WITH/*after with*/MAX_QUERIES_PER_HOUR 90; 15Warnings: 16Warning 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. 17Warning 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. 18GRANT UPDATE ON *.* TO user1@localhost IDENTIFIED BY 'secret', user2@localhost IDENTIFIED BY 'secret2'; 19Warnings: 20Warning 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. 21Warning 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. 22REVOKE UPDATE ON *.* FROM user1@localhost, user2@localhost; 23# Test the password is encrypted in binary log 24# when granting privileges on procedure. 25CREATE PROCEDURE p1() SQL SECURITY INVOKER SELECT 1; 26GRANT EXECUTE ON PROCEDURE p1 TO user1@localhost IDENTIFIED BY 'secret', user2@localhost IDENTIFIED BY 'secret2' /*before with*/ WITH MAX_QUERIES_PER_HOUR 90; 27Warnings: 28Warning 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. 29Warning 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. 30GRANT EXECUTE ON PROCEDURE p1 /*before to*/TO/*after to*/ user1@localhost IDENTIFIED BY 'secret', user2@localhost IDENTIFIED BY 'secret2' /*before require*/REQUIRE SSL/*after require*/ /*before with*/WITH/*after with*/MAX_QUERIES_PER_HOUR 90; 31Warnings: 32Warning 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. 33Warning 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. 34GRANT EXECUTE ON PROCEDURE p1 TO user1@localhost IDENTIFIED BY 'secret', user2@localhost IDENTIFIED BY 'secret2'; 35Warnings: 36Warning 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. 37Warning 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. 38REVOKE EXECUTE ON PROCEDURE p1 FROM user1@localhost, user2@localhost; 39# Test the password is encrypted in binary log 40# when granting privileges on function. 41CREATE FUNCTION f1() RETURNS INT RETURN 123; 42GRANT EXECUTE ON FUNCTION f1 /*before to*/TO/*after to*/ user1@localhost IDENTIFIED BY 'secret' /*!10000 , user2@localhost IDENTIFIED BY 'secret2' */ /*!99999 THIS_WOULD_BREAK */ /*before require*/REQUIRE SSL/*after require*/ /*before with*/WITH/*after with*/MAX_QUERIES_PER_HOUR 90; 43Warnings: 44Warning 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. 45Warning 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. 46REVOKE EXECUTE ON FUNCTION f1 FROM user1@localhost, user2@localhost; 47DROP USER user1@localhost, user2@localhost; 48DROP FUNCTION f1; 49DROP PROCEDURE p1; 50GRANT ALL on *.* TO test_user1 IDENTIFIED BY 'azundris1'; 51Warnings: 52Warning 1287 Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. 53CREATE USER test_user2 IDENTIFIED BY 'azundris2'; 54CHANGE MASTER TO MASTER_PASSWORD='azundris3'; 55Warnings: 56Note 1759 Sending passwords in plain text without SSL/TLS is extremely insecure. 57Note 1760 Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. 58CREATE USER 'test_user4'@'localhost'; 59SET PASSWORD FOR 'test_user4'@'localhost' = 'azundris4'; 60include/show_binlog_events.inc 61Log_name Pos Event_type Server_id End_log_pos Info 62master-bin.000001 # Query # # use `test`; CREATE USER 'user1'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*14E65567ABDB5135D0CFD9A70B3032C179A49EE7' 63master-bin.000001 # Query # # use `test`; CREATE USER 'user2'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*14E65567ABDB5135D0CFD9A70B3032C179A49EE7' 64master-bin.000001 # Query # # use `test`; ALTER USER 'user1'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*14E65567ABDB5135D0CFD9A70B3032C179A49EE7' 65master-bin.000001 # Query # # use `test`; GRANT UPDATE ON *.* TO 'user1'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*14E65567ABDB5135D0CFD9A70B3032C179A49EE7' WITH GRANT OPTION 66master-bin.000001 # Query # # use `test`; GRANT UPDATE ON *.* TO 'user1'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*14E65567ABDB5135D0CFD9A70B3032C179A49EE7','user2'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*305D7BCA9D65D8D5C4677F05CD2EA2EBE350158F' WITH MAX_QUERIES_PER_HOUR 90 67master-bin.000001 # Query # # use `test`; GRANT UPDATE ON *.* TO 'user1'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*14E65567ABDB5135D0CFD9A70B3032C179A49EE7','user2'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*305D7BCA9D65D8D5C4677F05CD2EA2EBE350158F' REQUIRE SSL WITH MAX_QUERIES_PER_HOUR 90 68master-bin.000001 # Query # # use `test`; GRANT UPDATE ON *.* TO 'user1'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*14E65567ABDB5135D0CFD9A70B3032C179A49EE7','user2'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*305D7BCA9D65D8D5C4677F05CD2EA2EBE350158F' 69master-bin.000001 # Query # # use `test`; REVOKE UPDATE ON *.* FROM user1@localhost, user2@localhost 70master-bin.000001 # Query # # use `test`; CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`() 71 SQL SECURITY INVOKER 72SELECT 1 73master-bin.000001 # Query # # use `test`; GRANT EXECUTE ON PROCEDURE `test`.`p1` TO 'user1'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*14E65567ABDB5135D0CFD9A70B3032C179A49EE7','user2'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*305D7BCA9D65D8D5C4677F05CD2EA2EBE350158F' WITH MAX_QUERIES_PER_HOUR 90 74master-bin.000001 # Query # # use `test`; GRANT EXECUTE ON PROCEDURE `test`.`p1` TO 'user1'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*14E65567ABDB5135D0CFD9A70B3032C179A49EE7','user2'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*305D7BCA9D65D8D5C4677F05CD2EA2EBE350158F' REQUIRE SSL WITH MAX_QUERIES_PER_HOUR 90 75master-bin.000001 # Query # # use `test`; GRANT EXECUTE ON PROCEDURE `test`.`p1` TO 'user1'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*14E65567ABDB5135D0CFD9A70B3032C179A49EE7','user2'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*305D7BCA9D65D8D5C4677F05CD2EA2EBE350158F' 76master-bin.000001 # Query # # use `test`; REVOKE EXECUTE ON PROCEDURE p1 FROM user1@localhost, user2@localhost 77master-bin.000001 # Query # # use `test`; CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int(11) 78RETURN 123 79master-bin.000001 # Query # # use `test`; GRANT EXECUTE ON FUNCTION `test`.`f1` TO 'user1'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*14E65567ABDB5135D0CFD9A70B3032C179A49EE7','user2'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*305D7BCA9D65D8D5C4677F05CD2EA2EBE350158F' REQUIRE SSL WITH MAX_QUERIES_PER_HOUR 90 80master-bin.000001 # Query # # use `test`; REVOKE EXECUTE ON FUNCTION f1 FROM user1@localhost, user2@localhost 81master-bin.000001 # Query # # use `test`; DROP USER user1@localhost, user2@localhost 82master-bin.000001 # Query # # use `test`; DROP FUNCTION f1 83master-bin.000001 # Query # # use `test`; DROP PROCEDURE p1 84master-bin.000001 # Query # # use `test`; GRANT ALL PRIVILEGES ON *.* TO 'test_user1'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*8D15E74BC850D11573A52BE84B504DD0BA40C1E3' 85master-bin.000001 # Query # # use `test`; CREATE USER 'test_user2'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*A142844DBF024D282D88FE19CB4D3FA13B1E96E0' 86master-bin.000001 # Query # # use `test`; CREATE USER 'test_user4'@'localhost' IDENTIFIED WITH 'mysql_native_password' 87master-bin.000001 # Query # # use `test`; ALTER USER 'test_user4'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*F62B91C8AD3D5D0BBF02A761A179EA3A66B060EC' 88DROP USER 'test_user4'@'localhost'; 89DROP USER test_user2; 90DROP USER test_user1; 91End of 5.6 tests! 92