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

5 files changed, 85 insertions, 21 deletions

diff --git a/sql/share/errmsg-utf8.txt b/sql/share/errmsg-utf8.txt
index 9c8595e949f..a5d3ba19b2f 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 bbae17eb472..c8714423933 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 ec400630c9e..46d667cda8c 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 6c6c8ac7c9f..e9553993794 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -4498,7 +4498,9 @@ end_with_restore_list:
   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 7f9ca84bdf0..8025340938b 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
           {