maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #08021
MDEV-7288 USER/ROLE: CREATE OR REPLACE, CREATE IF NOT EXISTS, DROP IF EXISTS
Hi Sergei,
Please have a look into the patch for MDEV-7288.
It consists of the USER/ROLE related part of the big
GSoC patch from Sriram that you reviewed earlier, with
review suggestions addressed.
You earlier had the following suggestions/questions:
>> diff --git a/sql/sql_acl.cc b/sql/sql_acl.cc
>> index 5f077c2..1e156be 100644
>> --- a/sql/sql_acl.cc
>> +++ b/sql/sql_acl.cc
>> ...
>> @@ -9215,10 +9252,26 @@ bool mysql_create_user(THD *thd, List
<LEX_USER> &lis
>> */
>> if (handle_grant_data(tables, 0, user_name, NULL))
>> {
>> - append_user(thd, &wrong_users, user_name);
>> -
>> - result= TRUE;
>> - continue;
>> + if (thd->lex->is_create_or_replace())
>> + {
>> + if (handle_grant_data(tables, 1, user_name, NULL) <= 0)
>> + {
>> + append_user(thd, &wrong_users, user_name);
>
> I don't understand that at all. If user creation failed, you try to drop
> it, but don't create a user again, after the old one was dropped. How
> could it work?
>
Yeah, it looks confusing, because handle_grant_data() can do different
things depending on the arguments.
The call for handle_grant_data(tables, 0, user_name, NULL) does
not create any users. It only checks if the user already exists.
The call for handle_grant_data(tables, 1, user_name, NULL) drops the user.
And the call for replace_user_table() after this block is the one
that actually creates the user.
I have added some comments in the new code, to make it clearer.
@@ -9268,7 +9321,7 @@ bool mysql_create_user(THD *thd, List <LEX_USER> &list,
(handle_as_role) ? "CREATE ROLE" : "CREATE USER",
wrong_users.c_ptr_safe());
- if (some_users_created)
+ if (some_users_created || !result)
What is that for? To binlog CREATE IF NOT EXISTS?
Yes, that was the reason.
I renamed "some_users_created" to "binlog"
and changed the logic slightly to set "binlog"
to true under the block checking lex->create_info.if_not_exists(),
instead of this additional test for "result".
So now it should be easier to read:
if (binlog)
result |= write_bin_log(thd, FALSE, thd->query(), thd->query_length());
Thanks.
diff --git a/mysql-test/r/create_drop_binlog.result b/mysql-test/r/create_drop_binlog.result
index 32638c2..4b1db84 100644
--- a/mysql-test/r/create_drop_binlog.result
+++ b/mysql-test/r/create_drop_binlog.result
@@ -181,3 +181,65 @@ Log_name Pos Event_type Server_id End_log_pos Info
# # Gtid 1 # GTID #-#-#
# # Query 1 # use `test`; DROP VIEW IF EXISTS v1
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;
+Warnings:
+Note 1974 Can't create role 'r1'; it already exists
+DROP ROLE IF EXISTS r1;
+DROP ROLE IF EXISTS r1;
+Warnings:
+Note 1975 Can't drop role 'r1'; it doesn't exist
+SHOW BINLOG EVENTS;
+Log_name Pos Event_type Server_id End_log_pos Info
+# # Format_desc 1 # VER
+# # Gtid_list 1 # []
+# # Binlog_checkpoint 1 # master-bin.000001
+# # Gtid 1 # GTID #-#-#
+# # Query 1 # use `test`; CREATE OR REPLACE ROLE r1
+# # Gtid 1 # GTID #-#-#
+# # Query 1 # use `test`; CREATE OR REPLACE ROLE r1
+# # Gtid 1 # GTID #-#-#
+# # Query 1 # use `test`; DROP ROLE r1
+# # Gtid 1 # GTID #-#-#
+# # Query 1 # use `test`; CREATE ROLE IF NOT EXISTS r1
+# # Gtid 1 # GTID #-#-#
+# # Query 1 # use `test`; CREATE ROLE IF NOT EXISTS r1
+# # Gtid 1 # GTID #-#-#
+# # Query 1 # use `test`; DROP ROLE IF EXISTS r1
+# # Gtid 1 # GTID #-#-#
+# # Query 1 # use `test`; DROP ROLE IF EXISTS r1
+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;
+Warnings:
+Note 1972 Can't create user 'u1'@'localhost'; it already exists
+DROP USER IF EXISTS u1@localhost;
+DROP USER IF EXISTS u1@localhost;
+Warnings:
+Note 1973 Can't drop user 'u1'@'localhost'; it doesn't exist
+SHOW BINLOG EVENTS;
+Log_name Pos Event_type Server_id End_log_pos Info
+# # Format_desc 1 # VER
+# # Gtid_list 1 # []
+# # Binlog_checkpoint 1 # master-bin.000001
+# # Gtid 1 # GTID #-#-#
+# # Query 1 # use `test`; CREATE OR REPLACE USER u1@localhost
+# # Gtid 1 # GTID #-#-#
+# # Query 1 # use `test`; CREATE OR REPLACE USER u1@localhost
+# # Gtid 1 # GTID #-#-#
+# # Query 1 # use `test`; DROP USER u1@localhost
+# # Gtid 1 # GTID #-#-#
+# # Query 1 # use `test`; CREATE USER IF NOT EXISTS u1@localhost
+# # Gtid 1 # GTID #-#-#
+# # Query 1 # use `test`; CREATE USER IF NOT EXISTS u1@localhost
+# # Gtid 1 # GTID #-#-#
+# # Query 1 # use `test`; DROP USER IF EXISTS u1@localhost
+# # Gtid 1 # GTID #-#-#
+# # Query 1 # use `test`; DROP USER IF EXISTS u1@localhost
+RESET MASTER;
diff --git a/mysql-test/r/create_drop_role.result b/mysql-test/r/create_drop_role.result
new file mode 100644
index 0000000..7fc969c
--- /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;
diff --git a/mysql-test/r/create_drop_user.result b/mysql-test/r/create_drop_user.result
new file mode 100644
index 0000000..3c38854
--- /dev/null
+++ b/mysql-test/r/create_drop_user.result
@@ -0,0 +1,43 @@
+CREATE USER IF NOT EXISTS u1@localhost IDENTIFIED BY 'pw1';
+SELECT password FROM mysql.user WHERE user='u1';
+password
+*2B602296A79E0A8784ACC5C88D92E46588CCA3C3
+CREATE USER IF NOT EXISTS u1@localhost IDENTIFIED BY 'pw2';
+Warnings:
+Note 1972 Can't create user 'u1'@'localhost'; it already exists
+SELECT password FROM mysql.user WHERE user='u1';
+password
+*2B602296A79E0A8784ACC5C88D92E46588CCA3C3
+CREATE OR REPLACE USER u1@localhost IDENTIFIED BY 'pw3';
+SELECT password FROM mysql.user WHERE user='u1';
+password
+*77B4A70CEFD76DB9415F36D291E74C110D2738E0
+CREATE OR REPLACE USER IF NOT EXISTS u1@localhost IDENTIFIED BY 'pw4';
+ERROR HY000: Incorrect usage of OR REPLACE and IF NOT EXISTS
+SELECT password FROM mysql.user WHERE user='u1';
+password
+*77B4A70CEFD76DB9415F36D291E74C110D2738E0
+DROP USER IF EXISTS u1@localhost;
+DROP USER IF EXISTS u1@localhost;
+Warnings:
+Note 1973 Can't drop user 'u1'@'localhost'; it doesn't exist
+DROP USER u1@localhost;
+ERROR HY000: Operation DROP USER failed for 'u1'@'localhost'
+CREATE OR REPLACE USER u1@localhost;
+CREATE USER u1@localhost;
+ERROR HY000: Operation CREATE USER failed for 'u1'@'localhost'
+DROP USER u1@localhost;
+CREATE USER u1;
+CREATE USER u1, u2;
+ERROR HY000: Operation CREATE USER failed for 'u1'@'%'
+CREATE USER u2;
+ERROR HY000: Operation CREATE USER failed for 'u2'@'%'
+CREATE OR REPLACE USER u1 IDENTIFIED BY PASSWORD 'password', u2;
+ERROR HY000: Password hash should be a 41-digit hexadecimal number
+CREATE OR REPLACE USER u1 IDENTIFIED BY PASSWORD 'abcdefghijklmnop', u2;
+DROP USER u1;
+DROP USER IF EXISTS u1, u2;
+Warnings:
+Note 1973 Can't drop user 'u1'@'%'; it doesn't exist
+DROP USER u2;
+ERROR HY000: Operation DROP USER failed for 'u2'@'%'
diff --git a/mysql-test/r/create_or_replace_permission.result b/mysql-test/r/create_or_replace_permission.result
index dc40d39..d9c5a6f 100644
--- a/mysql-test/r/create_or_replace_permission.result
+++ b/mysql-test/r/create_or_replace_permission.result
@@ -35,6 +35,10 @@ CREATE OR REPLACE FUNCTION lookup RETURNS STRING SONAME "UDF_EXAMPLE_LIB";
ERROR 42000: Access denied for user 'mysqltest_1'@'localhost' to database 'mysql'
CREATE OR REPLACE FUNCTION hello(str char(20)) RETURNS TEXT RETURN CONCAT('Hello, ', str, '!');
ERROR 42000: alter routine command denied to user 'mysqltest_1'@'localhost' for routine 'db1.hello'
+CREATE OR REPLACE USER u1@localhost;
+ERROR 42000: Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation
+CREATE OR REPLACE ROLE developer;
+ERROR 42000: Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation
SELECT CURRENT_USER;
CURRENT_USER
root@localhost
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 0000000..eee92eb
--- /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 0000000..e83372a
--- /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 0000000..7260b47
--- /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 0000000..5fcf0a1
--- /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
diff --git a/mysql-test/t/create_drop_binlog.test b/mysql-test/t/create_drop_binlog.test
index 655a543..5bcd783 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 0000000..be33083
--- /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 0000000..949782a
--- /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 22ba40f..f155e97 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;
diff --git a/sql/share/errmsg-utf8.txt b/sql/share/errmsg-utf8.txt
index 9c8595e..a5d3ba1 100644
--- a/sql/share/errmsg-utf8.txt
+++ b/sql/share/errmsg-utf8.txt
@@ -7117,3 +7117,11 @@ ER_SET_STATEMENT_NOT_SUPPORTED 42000
eng "The system variable %.200s cannot be set in SET STATEMENT."
ER_INVALID_SLAVE_PARALLEL_MODE
eng "Invalid use of '%s' option for slave_parallel_mode"
+ER_USER_CREATE_EXISTS
+ eng "Can't create user '%-.64s'@'%-.64s'; it already exists"
+ER_USER_DROP_EXISTS
+ eng "Can't drop user '%-.64s'@'%-.64s'; it doesn't exist"
+ER_ROLE_CREATE_EXISTS
+ eng "Can't create role '%-.64s'; it already exists"
+ER_ROLE_DROP_EXISTS
+ eng "Can't drop role '%-.64s'; it doesn't exist"
diff --git a/sql/sql_acl.cc b/sql/sql_acl.cc
index bbae17e..c871442 100644
--- a/sql/sql_acl.cc
+++ b/sql/sql_acl.cc
@@ -9346,7 +9346,7 @@ bool mysql_create_user(THD *thd, List <LEX_USER> &list, bool handle_as_role)
LEX_USER *user_name;
List_iterator <LEX_USER> user_list(list);
TABLE_LIST tables[TABLES_MAX];
- bool some_users_created= FALSE;
+ bool binlog= false;
DBUG_ENTER("mysql_create_user");
DBUG_PRINT("entry", ("Handle as %s", handle_as_role ? "role" : "user"));
@@ -9402,12 +9402,41 @@ bool mysql_create_user(THD *thd, List <LEX_USER> &list, bool handle_as_role)
*/
if (handle_grant_data(tables, 0, user_name, NULL))
{
- append_user(thd, &wrong_users, user_name);
- result= TRUE;
- continue;
+ if (thd->lex->create_info.or_replace())
+ {
+ // Drop the existing user
+ if (handle_grant_data(tables, 1, user_name, NULL) <= 0)
+ {
+ // DROP failed
+ append_user(thd, &wrong_users, user_name);
+ result= true;
+ continue;
+ }
+ // Proceed with the creation
+ }
+ else if (thd->lex->create_info.if_not_exists())
+ {
+ binlog= true;
+ if (handle_as_role)
+ push_warning_printf(thd, Sql_condition::WARN_LEVEL_NOTE,
+ ER_ROLE_CREATE_EXISTS, ER(ER_ROLE_CREATE_EXISTS),
+ user_name->user.str);
+ else
+ push_warning_printf(thd, Sql_condition::WARN_LEVEL_NOTE,
+ ER_USER_CREATE_EXISTS, ER(ER_USER_CREATE_EXISTS),
+ user_name->user.str, user_name->host.str);
+ continue;
+ }
+ else
+ {
+ // "CREATE USER user1" for an existing user
+ append_user(thd, &wrong_users, user_name);
+ result= true;
+ continue;
+ }
}
- some_users_created= TRUE;
+ binlog= true;
if (replace_user_table(thd, tables[USER_TABLE].table, *user_name, 0, 0, 1, 0))
{
append_user(thd, &wrong_users, user_name);
@@ -9454,7 +9483,7 @@ bool mysql_create_user(THD *thd, List <LEX_USER> &list, bool handle_as_role)
(handle_as_role) ? "CREATE ROLE" : "CREATE USER",
wrong_users.c_ptr_safe());
- if (some_users_created)
+ if (binlog)
result |= write_bin_log(thd, FALSE, thd->query(), thd->query_length());
mysql_rwlock_unlock(&LOCK_grant);
@@ -9481,7 +9510,7 @@ bool mysql_drop_user(THD *thd, List <LEX_USER> &list, bool handle_as_role)
LEX_USER *user_name, *tmp_user_name;
List_iterator <LEX_USER> user_list(list);
TABLE_LIST tables[TABLES_MAX];
- bool some_users_deleted= FALSE;
+ bool binlog= false;
ulonglong old_sql_mode= thd->variables.sql_mode;
DBUG_ENTER("mysql_drop_user");
DBUG_PRINT("entry", ("Handle as %s", handle_as_role ? "role" : "user"));
@@ -9500,6 +9529,7 @@ bool mysql_drop_user(THD *thd, List <LEX_USER> &list, bool handle_as_role)
while ((tmp_user_name= user_list++))
{
+ int rc;
user_name= get_current_user(thd, tmp_user_name, false);
if (!user_name)
{
@@ -9516,14 +9546,30 @@ bool mysql_drop_user(THD *thd, List <LEX_USER> &list, bool handle_as_role)
continue;
}
- if (handle_grant_data(tables, 1, user_name, NULL) <= 0)
+ if ((rc= handle_grant_data(tables, 1, user_name, NULL)) > 0)
{
- append_user(thd, &wrong_users, user_name);
- result= TRUE;
+ // The user or role was successfully deleted
+ binlog= true;
continue;
}
- some_users_deleted= TRUE;
+ if (rc == 0 && thd->lex->if_exists())
+ {
+ // "DROP USER IF EXISTS user1" for a non-existing user or role
+ if (handle_as_role)
+ push_warning_printf(thd, Sql_condition::WARN_LEVEL_NOTE,
+ ER_ROLE_DROP_EXISTS, ER(ER_ROLE_DROP_EXISTS),
+ user_name->user.str);
+ else
+ push_warning_printf(thd, Sql_condition::WARN_LEVEL_NOTE,
+ ER_USER_DROP_EXISTS, ER(ER_USER_DROP_EXISTS),
+ user_name->user.str, user_name->host.str);
+ binlog= true;
+ continue;
+ }
+ // Internal error, or "DROP USER user1" for a non-existing user
+ append_user(thd, &wrong_users, user_name);
+ result= TRUE;
}
if (!handle_as_role)
@@ -9545,7 +9591,7 @@ bool mysql_drop_user(THD *thd, List <LEX_USER> &list, bool handle_as_role)
(handle_as_role) ? "DROP ROLE" : "DROP USER",
wrong_users.c_ptr_safe());
- if (some_users_deleted)
+ if (binlog)
result |= write_bin_log(thd, FALSE, thd->query(), thd->query_length());
mysql_rwlock_unlock(&LOCK_grant);
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index ec40063..46d667c 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -2851,6 +2851,11 @@ struct LEX: public Query_tables_list
set_command(command, scope, options);
return check_create_options(options);
}
+ bool set_command_with_check(enum_sql_command command, DDL_options_st options)
+ {
+ set_command(command, options);
+ return check_create_options(options);
+ }
/*
DROP shares lex->create_info to store TEMPORARY and IF EXISTS options
to save on extra initialization in lex_start().
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index 6c6c8ac..e955399 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -4498,7 +4498,9 @@ static bool do_execute_sp(THD *thd, sp_head *sp)
case SQLCOM_CREATE_USER:
case SQLCOM_CREATE_ROLE:
{
- if (check_access(thd, INSERT_ACL, "mysql", NULL, NULL, 1, 1) &&
+ if (check_access(thd, lex->create_info.or_replace() ?
+ INSERT_ACL | DELETE_ACL : INSERT_ACL,
+ "mysql", NULL, NULL, 1, 1) &&
check_global_access(thd,CREATE_USER_ACL))
break;
WSREP_TO_ISOLATION_BEGIN(WSREP_MYSQL_DB, NULL, NULL)
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 7f9ca84..8025340 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -2573,13 +2573,16 @@ create:
MYSQL_YYABORT;
}
}
- | CREATE USER clear_privileges grant_list
+ | create_or_replace USER opt_if_not_exists clear_privileges grant_list
{
- Lex->sql_command = SQLCOM_CREATE_USER;
+ if (Lex->set_command_with_check(SQLCOM_CREATE_USER, $1 | $3))
+ MYSQL_YYABORT;
}
- | CREATE ROLE_SYM clear_privileges role_list opt_with_admin
+ | create_or_replace ROLE_SYM opt_if_not_exists
+ clear_privileges role_list opt_with_admin
{
- Lex->sql_command = SQLCOM_CREATE_ROLE;
+ if (Lex->set_command_with_check(SQLCOM_CREATE_ROLE, $1 | $3))
+ MYSQL_YYABORT;
}
| CREATE LOGFILE_SYM GROUP_SYM logfile_group_info
{
@@ -11866,13 +11869,13 @@ drop:
lex->set_command(SQLCOM_DROP_PROCEDURE, $3);
lex->spname= $4;
}
- | DROP USER clear_privileges user_list
+ | DROP USER opt_if_exists clear_privileges user_list
{
- Lex->sql_command = SQLCOM_DROP_USER;
+ Lex->set_command(SQLCOM_DROP_USER, $3);
}
- | DROP ROLE_SYM clear_privileges role_list
+ | DROP ROLE_SYM opt_if_exists clear_privileges role_list
{
- Lex->sql_command = SQLCOM_DROP_ROLE;
+ Lex->set_command(SQLCOM_DROP_ROLE, $3);
}
| DROP VIEW_SYM opt_if_exists
{
Follow ups
