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

4 files changed, 131 insertions, 6 deletions

diff --git a/mysql-test/t/create_drop_binlog.test b/mysql-test/t/create_drop_binlog.test
index 655a54308d6..5bcd7832dc5 100644
--- a/mysql-test/t/create_drop_binlog.test
+++ b/mysql-test/t/create_drop_binlog.test
@@ -95,3 +95,27 @@ DROP VIEW IF EXISTS v1;
 --replace_regex /xid=[0-9]+/xid=XX/ /GTID [0-9]+-[0-9]+-[0-9]+/GTID #-#-#/ /Server.ver.*/VER/
 SHOW BINLOG EVENTS;
 RESET MASTER;
+
+CREATE OR REPLACE ROLE r1;
+CREATE OR REPLACE ROLE r1;
+DROP ROLE r1;
+CREATE ROLE IF NOT EXISTS r1;
+CREATE ROLE IF NOT EXISTS r1;
+DROP ROLE IF EXISTS r1;
+DROP ROLE IF EXISTS r1;
+--replace_column 1 # 2 # 5 #
+--replace_regex /xid=[0-9]+/xid=XX/ /GTID [0-9]+-[0-9]+-[0-9]+/GTID #-#-#/ /Server.ver.*/VER/
+SHOW BINLOG EVENTS;
+RESET MASTER;
+
+CREATE OR REPLACE USER u1@localhost;
+CREATE OR REPLACE USER u1@localhost;
+DROP USER u1@localhost;
+CREATE USER IF NOT EXISTS u1@localhost;
+CREATE USER IF NOT EXISTS u1@localhost;
+DROP USER IF EXISTS u1@localhost;
+DROP USER IF EXISTS u1@localhost;
+--replace_column 1 # 2 # 5 #
+--replace_regex /xid=[0-9]+/xid=XX/ /GTID [0-9]+-[0-9]+-[0-9]+/GTID #-#-#/ /Server.ver.*/VER/
+SHOW BINLOG EVENTS;
+RESET MASTER;
diff --git a/mysql-test/t/create_drop_role.test b/mysql-test/t/create_drop_role.test
new file mode 100644
index 00000000000..be33083e6c4
--- /dev/null
+++ b/mysql-test/t/create_drop_role.test
@@ -0,0 +1,56 @@
+--source include/not_embedded.inc
+
+CREATE ROLE IF NOT EXISTS role1 WITH ADMIN user1;
+SELECT * FROM mysql.roles_mapping;
+DROP ROLE role1;
+
+CREATE OR REPLACE ROLE role1 WITH ADMIN user2;
+SELECT * FROM mysql.roles_mapping WHERE Role='role1';
+CREATE OR REPLACE ROLE role1 WITH ADMIN user3;
+SELECT * FROM mysql.roles_mapping WHERE Role='role1';
+CREATE ROLE IF NOT EXISTS role1 WITH ADMIN user4;
+SELECT * FROM mysql.roles_mapping WHERE Role='role1';
+DROP ROLE IF EXISTS role1;
+SELECT * FROM mysql.roles_mapping WHERE Role='role1';
+DROP ROLE IF EXISTS role1;
+
+
+CREATE ROLE role_1;
+
+CREATE ROLE IF NOT EXISTS role_1;
+CREATE OR REPLACE ROLE role_1;
+
+--error ER_WRONG_USAGE
+CREATE OR REPLACE ROLE IF NOT EXISTS role_1;
+
+--error ER_CANNOT_USER
+CREATE ROLE role_1;
+
+CREATE USER u1@localhost;
+REVOKE SHOW DATABASES ON *.* FROM 'u1'@'localhost';
+GRANT SHOW DATABASES ON *.* TO role_1;
+GRANT role_1 TO u1@localhost;
+
+connect (user_a, localhost, u1,,);
+connection user_a;
+
+SELECT CURRENT_USER;
+
+SHOW DATABASES;
+SELECT CURRENT_ROLE;
+SET ROLE role_1;
+SELECT CURRENT_ROLE;
+SHOW DATABASES;
+SET ROLE NONE;
+
+connect (user_b, localhost, root,,);
+connection user_b;
+
+--echo # Clearing up
+DROP ROLE role_1;
+DROP ROLE IF EXISTS role_1;
+
+--error ER_CANNOT_USER
+DROP ROLE role_1;
+
+DROP USER u1@localhost;
diff --git a/mysql-test/t/create_drop_user.test b/mysql-test/t/create_drop_user.test
new file mode 100644
index 00000000000..949782a2daf
--- /dev/null
+++ b/mysql-test/t/create_drop_user.test
@@ -0,0 +1,46 @@
+--source include/not_embedded.inc
+
+CREATE USER IF NOT EXISTS u1@localhost IDENTIFIED BY 'pw1';
+SELECT password FROM mysql.user WHERE user='u1';
+
+CREATE USER IF NOT EXISTS u1@localhost IDENTIFIED BY 'pw2';
+SELECT password FROM mysql.user WHERE user='u1';
+
+CREATE OR REPLACE USER u1@localhost IDENTIFIED BY 'pw3';
+SELECT password FROM mysql.user WHERE user='u1';
+
+--error ER_WRONG_USAGE
+CREATE OR REPLACE USER IF NOT EXISTS u1@localhost IDENTIFIED BY 'pw4';
+SELECT password FROM mysql.user WHERE user='u1';
+
+DROP USER IF EXISTS u1@localhost;
+DROP USER IF EXISTS u1@localhost;
+
+--error ER_CANNOT_USER
+DROP USER u1@localhost;
+
+CREATE OR REPLACE USER u1@localhost;
+
+--error ER_CANNOT_USER
+CREATE USER u1@localhost;
+
+DROP USER u1@localhost;
+
+CREATE USER u1;
+
+--error ER_CANNOT_USER
+CREATE USER u1, u2;
+
+--error ER_CANNOT_USER
+CREATE USER u2;
+
+--error ER_PASSWD_LENGTH
+CREATE OR REPLACE USER u1 IDENTIFIED BY PASSWORD 'password', u2;
+
+CREATE OR REPLACE USER u1 IDENTIFIED BY PASSWORD 'abcdefghijklmnop', u2;
+
+DROP USER u1;
+DROP USER IF EXISTS u1, u2;
+
+--error ER_CANNOT_USER
+DROP USER u2;
diff --git a/mysql-test/t/create_or_replace_permission.test b/mysql-test/t/create_or_replace_permission.test
index 22ba40f43f9..f155e97198c 100644
--- a/mysql-test/t/create_or_replace_permission.test
+++ b/mysql-test/t/create_or_replace_permission.test
@@ -52,12 +52,11 @@ eval CREATE OR REPLACE FUNCTION lookup RETURNS STRING SONAME "$UDF_EXAMPLE_SO";
 --error ER_PROCACCESS_DENIED_ERROR
 CREATE OR REPLACE FUNCTION hello(str char(20)) RETURNS TEXT RETURN CONCAT('Hello, ', str, '!');
 
-# TODO: add this when "MDEV-5359 CREATE OR REPLACE..." is done
-#--error ER_SPECIFIC_ACCESS_DENIED_ERROR
-#CREATE OR REPLACE USER u1@localhost;
-#
-#--error ER_SPECIFIC_ACCESS_DENIED_ERROR
-#CREATE OR REPLACE ROLE developer;
+--error ER_SPECIFIC_ACCESS_DENIED_ERROR
+CREATE OR REPLACE USER u1@localhost;
+
+--error ER_SPECIFIC_ACCESS_DENIED_ERROR
+CREATE OR REPLACE ROLE developer;
 
 connection default;
 SELECT CURRENT_USER;