MDEV-7288 USER/ROLE: CREATE OR REPLACE, CREATE IF NOT EXISTS, DROP IF EXISTS

4 files changed, 186 insertions, 0 deletions

diff --git a/mysql-test/suite/rpl/r/rpl_create_drop_role.result b/mysql-test/suite/rpl/r/rpl_create_drop_role.result
new file mode 100644
index 00000000000..eee92eb0eaf
--- /dev/null
+++ b/mysql-test/suite/rpl/r/rpl_create_drop_role.result
@@ -0,0 +1,41 @@
+include/master-slave.inc
+[connection master]
+CREATE ROLE role_1;
+SELECT User FROM mysql.user WHERE is_role='Y' ORDER BY User;
+User
+role_1
+CREATE ROLE IF NOT EXISTS role_1;
+Warnings:
+Note	1974	Can't create role 'role_1'; it already exists
+CREATE ROLE IF NOT EXISTS role_2;
+SELECT User FROM mysql.user WHERE is_role='Y' ORDER BY User;
+User
+role_1
+role_2
+CREATE OR REPLACE ROLE IF NOT EXISTS role_3;
+ERROR HY000: Incorrect usage of OR REPLACE and IF NOT EXISTS
+CREATE OR REPLACE ROLE role_3;
+CREATE OR REPLACE ROLE role_2;
+SELECT User FROM mysql.user WHERE is_role='Y' ORDER BY User;
+User
+role_1
+role_2
+role_3
+CREATE ROLE role_2;
+ERROR HY000: Operation CREATE ROLE failed for 'role_2'
+SELECT User FROM mysql.user WHERE is_role='Y' ORDER BY User;
+User
+role_1
+role_2
+role_3
+DROP ROLE role_1;
+DROP ROLE IF EXISTS role_2;
+DROP ROLE IF EXISTS role_3;
+DROP ROLE IF EXISTS role_4;
+Warnings:
+Note	1975	Can't drop role 'role_4'; it doesn't exist
+DROP ROLE role_4;
+ERROR HY000: Operation DROP ROLE failed for 'role_4'
+SELECT User FROM mysql.user WHERE is_role='Y' ORDER BY User;
+User
+include/rpl_end.inc
diff --git a/mysql-test/suite/rpl/r/rpl_create_drop_user.result b/mysql-test/suite/rpl/r/rpl_create_drop_user.result
new file mode 100644
index 00000000000..e83372a97aa
--- /dev/null
+++ b/mysql-test/suite/rpl/r/rpl_create_drop_user.result
@@ -0,0 +1,46 @@
+include/master-slave.inc
+[connection master]
+CREATE USER u1@localhost IDENTIFIED BY 'abcdefghijk';
+SELECT CURRENT_USER;
+CURRENT_USER
+u1@localhost
+CREATE USER IF NOT EXISTS u2@localhost;
+SELECT CURRENT_USER;
+CURRENT_USER
+u2@localhost
+SELECT user, password FROM mysql.user WHERE user LIKE 'u%' ORDER BY user;
+user	password
+u1	*D9553C4CE316A9845CE49E30A2D7E3857AF966C4
+u2	
+SELECT user, password FROM mysql.user WHERE user LIKE 'u%' ORDER BY user;
+user	password
+u1	*D9553C4CE316A9845CE49E30A2D7E3857AF966C4
+u2	
+CREATE OR REPLACE USER u1@localhost IDENTIFIED BY 'abcdefghijk2';
+SELECT CURRENT_USER;
+CURRENT_USER
+u1@localhost
+SELECT user, password FROM mysql.user WHERE user LIKE 'u%' ORDER BY user;
+user	password
+u1	*A9A5EF53CE2EFAA6F4A746D63A917B2370971A7E
+u2	
+SELECT user, password FROM mysql.user WHERE user LIKE 'u%' ORDER BY user;
+user	password
+u1	*A9A5EF53CE2EFAA6F4A746D63A917B2370971A7E
+u2	
+CREATE USER u1@localhost;
+ERROR HY000: Operation CREATE USER failed for 'u1'@'localhost'
+DROP USER u3@localhost;
+ERROR HY000: Operation DROP USER failed for 'u3'@'localhost'
+SELECT user, password FROM mysql.user WHERE user LIKE 'u%' ORDER BY user;
+user	password
+u1	*A9A5EF53CE2EFAA6F4A746D63A917B2370971A7E
+u2	
+DROP USER IF EXISTS u1@localhost;
+DROP USER u2@localhost;
+DROP USER IF EXISTS u3@localhost;
+Warnings:
+Note	1973	Can't drop user 'u3'@'localhost'; it doesn't exist
+SELECT user, password FROM mysql.user WHERE user LIKE 'u%' ORDER BY user;
+user	password
+include/rpl_end.inc
diff --git a/mysql-test/suite/rpl/t/rpl_create_drop_role.test b/mysql-test/suite/rpl/t/rpl_create_drop_role.test
new file mode 100644
index 00000000000..7260b473530
--- /dev/null
+++ b/mysql-test/suite/rpl/t/rpl_create_drop_role.test
@@ -0,0 +1,48 @@
+--source include/master-slave.inc
+
+connection master;
+CREATE ROLE role_1;
+sync_slave_with_master;
+
+SELECT User FROM mysql.user WHERE is_role='Y' ORDER BY User;
+
+connection master;
+CREATE ROLE IF NOT EXISTS role_1;
+CREATE ROLE IF NOT EXISTS role_2;
+sync_slave_with_master;
+
+SELECT User FROM mysql.user WHERE is_role='Y' ORDER BY User;
+
+connection master;
+
+--error ER_WRONG_USAGE
+CREATE OR REPLACE ROLE IF NOT EXISTS role_3; 
+
+CREATE OR REPLACE ROLE role_3; 
+CREATE OR REPLACE ROLE role_2; 
+sync_slave_with_master;
+
+SELECT User FROM mysql.user WHERE is_role='Y' ORDER BY User;
+
+connection master;
+--error ER_CANNOT_USER
+CREATE ROLE role_2;
+
+sync_slave_with_master;
+
+SELECT User FROM mysql.user WHERE is_role='Y' ORDER BY User;
+
+connection master;
+DROP ROLE role_1;
+DROP ROLE IF EXISTS role_2;
+DROP ROLE IF EXISTS role_3;
+DROP ROLE IF EXISTS role_4;
+
+--error ER_CANNOT_USER
+DROP ROLE role_4;
+
+sync_slave_with_master;
+
+SELECT User FROM mysql.user WHERE is_role='Y' ORDER BY User;
+
+--source include/rpl_end.inc
diff --git a/mysql-test/suite/rpl/t/rpl_create_drop_user.test b/mysql-test/suite/rpl/t/rpl_create_drop_user.test
new file mode 100644
index 00000000000..5fcf0a14c36
--- /dev/null
+++ b/mysql-test/suite/rpl/t/rpl_create_drop_user.test
@@ -0,0 +1,51 @@
+--source include/master-slave.inc
+
+connection master;
+CREATE USER u1@localhost IDENTIFIED BY 'abcdefghijk';
+connect (user_a, localhost, u1,'abcdefghijk',);
+connection user_a;
+SELECT CURRENT_USER;
+disconnect user_a;
+
+connection master;
+CREATE USER IF NOT EXISTS u2@localhost;
+connect (user_a, localhost, u2,,);
+connection user_a;
+SELECT CURRENT_USER;
+disconnect user_a;
+
+connection master;
+SELECT user, password FROM mysql.user WHERE user LIKE 'u%' ORDER BY user;
+sync_slave_with_master;
+SELECT user, password FROM mysql.user WHERE user LIKE 'u%' ORDER BY user;
+
+connection master;
+CREATE OR REPLACE USER u1@localhost IDENTIFIED BY 'abcdefghijk2';
+connect (user_a, localhost, u1,'abcdefghijk2',);
+connection user_a;
+SELECT CURRENT_USER;
+disconnect user_a;
+connection master;
+SELECT user, password FROM mysql.user WHERE user LIKE 'u%' ORDER BY user;
+sync_slave_with_master;
+SELECT user, password FROM mysql.user WHERE user LIKE 'u%' ORDER BY user;
+
+connection master;
+--error ER_CANNOT_USER
+CREATE USER u1@localhost;
+
+--error ER_CANNOT_USER
+DROP USER u3@localhost;
+
+sync_slave_with_master;
+SELECT user, password FROM mysql.user WHERE user LIKE 'u%' ORDER BY user;
+
+connection master;
+DROP USER IF EXISTS u1@localhost;
+DROP USER u2@localhost;
+DROP USER IF EXISTS u3@localhost;
+sync_slave_with_master;
+
+SELECT user, password FROM mysql.user WHERE user LIKE 'u%' ORDER BY user;
+
+--source include/rpl_end.inc