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

1 files changed, 75 insertions, 0 deletions

diff --git a/mysql-test/r/create_drop_role.result b/mysql-test/r/create_drop_role.result
new file mode 100644
index 00000000000..7fc969cbe5a
--- /dev/null
+++ b/mysql-test/r/create_drop_role.result
@@ -0,0 +1,75 @@
+CREATE ROLE IF NOT EXISTS role1 WITH ADMIN user1;
+Warnings:
+Note	1449	The user specified as a definer ('user1'@'%') does not exist
+SELECT * FROM mysql.roles_mapping;
+Host	User	Role	Admin_option
+%	user1	role1	Y
+DROP ROLE role1;
+CREATE OR REPLACE ROLE role1 WITH ADMIN user2;
+Warnings:
+Note	1449	The user specified as a definer ('user2'@'%') does not exist
+SELECT * FROM mysql.roles_mapping WHERE Role='role1';
+Host	User	Role	Admin_option
+%	user2	role1	Y
+CREATE OR REPLACE ROLE role1 WITH ADMIN user3;
+Warnings:
+Note	1449	The user specified as a definer ('user3'@'%') does not exist
+SELECT * FROM mysql.roles_mapping WHERE Role='role1';
+Host	User	Role	Admin_option
+%	user3	role1	Y
+CREATE ROLE IF NOT EXISTS role1 WITH ADMIN user4;
+Warnings:
+Note	1449	The user specified as a definer ('user4'@'%') does not exist
+Note	1974	Can't create role 'role1'; it already exists
+SELECT * FROM mysql.roles_mapping WHERE Role='role1';
+Host	User	Role	Admin_option
+%	user3	role1	Y
+DROP ROLE IF EXISTS role1;
+SELECT * FROM mysql.roles_mapping WHERE Role='role1';
+Host	User	Role	Admin_option
+DROP ROLE IF EXISTS role1;
+Warnings:
+Note	1975	Can't drop role 'role1'; it doesn't exist
+CREATE ROLE role_1;
+CREATE ROLE IF NOT EXISTS role_1;
+Warnings:
+Note	1974	Can't create role 'role_1'; it already exists
+CREATE OR REPLACE ROLE role_1;
+CREATE OR REPLACE ROLE IF NOT EXISTS role_1;
+ERROR HY000: Incorrect usage of OR REPLACE and IF NOT EXISTS
+CREATE ROLE role_1;
+ERROR HY000: Operation CREATE ROLE failed for '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;
+SELECT CURRENT_USER;
+CURRENT_USER
+u1@localhost
+SHOW DATABASES;
+Database
+information_schema
+test
+SELECT CURRENT_ROLE;
+CURRENT_ROLE
+NULL
+SET ROLE role_1;
+SELECT CURRENT_ROLE;
+CURRENT_ROLE
+role_1
+SHOW DATABASES;
+Database
+information_schema
+mtr
+mysql
+performance_schema
+test
+SET ROLE NONE;
+# Clearing up
+DROP ROLE role_1;
+DROP ROLE IF EXISTS role_1;
+Warnings:
+Note	1975	Can't drop role 'role_1'; it doesn't exist
+DROP ROLE role_1;
+ERROR HY000: Operation DROP ROLE failed for 'role_1'
+DROP USER u1@localhost;