MDEV-24557: mysqldump --system=user to version grantsbb-10.2-danielblack-MDEV-24557-mysqldump-understand-all-grants

The grants MariaDB has are in 'SHOW PRIVILEGES'.
This is extended to include a version number.

As mariadb-dump could be used against a MySQL-8.0
server, we assume any grants we don't recognised are
mysql-8.0 and apply that as a version comment.

This facilitiates the migration from MySQL-8.0 servers.

3 files changed, 149 insertions, 49 deletions

diff --git a/client/mysqldump.c b/client/mysqldump.c
index ecca380777f..10565000b0a 100644
--- a/client/mysqldump.c
+++ b/client/mysqldump.c
@@ -49,6 +49,7 @@
 #include <m_ctype.h>
 #include <hash.h>
 #include <stdarg.h>
+#include <my_static_privs.h>
 
 #include "client_priv.h"
 #include "mysql.h"
@@ -4274,6 +4275,68 @@ static char *getTableName(int reset)
 
 
 /*
+  dump grant with executable comments
+  ARGS
+  grant: the string of the grant
+*/
+
+static void dump_grant(char *grant)
+{
+  char *tptr, *tok;
+  my_bool versioned;
+  struct show_privileges_st *privilege;
+
+  tok= strtok_r(grant, ",", &tptr);
+  do
+  {
+    if (tok == NULL)
+      return;
+
+    while (isspace(*tok))
+    {
+      fputc(*tok, md_result_file);
+      tok++;
+    }
+    privilege= sys_privileges;
+    for (privilege= sys_privileges; privilege->privilege ; privilege++)
+    {
+      if (my_strcasecmp(&my_charset_latin1, privilege->privilege, tok) == 0)
+        break;	
+    }
+    versioned= TRUE;
+    if (privilege->privilege)
+    {
+      if (privilege->version == 0)
+      {
+	versioned= FALSE;
+        fprintf(md_result_file, "%s", tok);
+      }
+      else
+      {
+	unsigned version= privilege->version;
+        fprintf(md_result_file, "/*M!%02d%02d%02d %s",
+	  version / 10000, (version % 10000)/100, (version % 100),  tok);
+      }
+    }
+    else if (strncmp(tok, "ALL PRIVILEGES", sizeof("ALL PRIVILEGES") - 1) == 0)
+    {
+      versioned= FALSE;
+      fprintf(md_result_file, "%s", tok);
+    }
+    else
+    {
+      /* Assume MySQL-8.0+ privilege */
+      fprintf(md_result_file, "/*!80000 %s", tok);
+    }
+    tok= strtok_r(NULL, ",", &tptr);
+    if (tok)
+      fputc(',', md_result_file);
+    if (versioned)
+      fprintf(md_result_file, "*/");
+  } while (1);
+}
+
+/*
   dump user/role grants
   ARGS
   user_role: is either a user, or a role
@@ -4297,7 +4360,28 @@ static int dump_grants(const char *user_role)
   {
     if (strncmp(row[0], "SET DEFAULT ROLE", sizeof("SET DEFAULT ROLE") - 1) == 0)
       continue;
-    fprintf(md_result_file, "%s;\n", row[0]);
+    /* Version comment the grants based on the sys_privileges included from my_static_privs.h */
+    if (strncmp(row[0], "GRANT", sizeof("GRANT") - 1) == 0)
+    {
+      char *on= strstr(row[0] + sizeof("GRANT"), " ON ");
+      if (on)
+      {
+        fprintf(md_result_file, "GRANT ");
+        *on= '\0';
+        dump_grant(row[0] + sizeof("GRANT"));
+        *on= ' ';
+        fprintf(md_result_file, "%s;\n", on);
+      }
+      else
+      {
+        /* its a role */
+        fprintf(md_result_file, "%s;\n", row[0]);
+      }
+    }
+    else
+    {
+      fprintf(md_result_file, "%s;\n", row[0]);
+    }
   }
   mysql_free_result(tableres);
   dynstr_free(&sqlbuf);
diff --git a/include/my_static_privs.h b/include/my_static_privs.h
new file mode 100644
index 00000000000..8a8b00fd03a
--- /dev/null
+++ b/include/my_static_privs.h
@@ -0,0 +1,61 @@
+
+
+/***************************************************************************
+ List all privileges supported
+***************************************************************************/
+
+struct show_privileges_st {
+  unsigned version;
+  const char *privilege;
+  const char *context;
+  const char *comment;
+};
+
+static struct show_privileges_st sys_privileges[]=
+{
+  {0, "Alter", "Tables",  "To alter the table"},
+  {0, "Alter routine", "Functions,Procedures",  "To alter or drop stored functions/procedures"},
+  {0, "Create", "Databases,Tables,Indexes",  "To create new databases and tables"},
+  {0, "Create routine","Databases","To use CREATE FUNCTION/PROCEDURE"},
+  {0, "Create temporary tables","Databases","To use CREATE TEMPORARY TABLE"},
+  {0, "Create view", "Tables",  "To create new views"},
+  {0, "Create user", "Server Admin",  "To create new users"},
+  {0, "Delete", "Tables",  "To delete existing rows"},
+  {100304, "Delete history", "Tables", "To delete versioning table historical rows"},
+  {0, "Drop", "Databases,Tables", "To drop databases, tables, and views"},
+#ifdef HAVE_EVENT_SCHEDULER
+  {0, "Event","Server Admin","To create, alter, drop and execute events"},
+#endif
+  {0, "Execute", "Functions,Procedures", "To execute stored routines"},
+  {0, "File", "File access on server",   "To read and write files on the server"},
+  {0, "Grant option",  "Databases,Tables,Functions,Procedures", "To give to other users those privileges you possess"},
+  {0, "Index", "Tables",  "To create or drop indexes"},
+  {0, "Insert", "Tables",  "To insert data into tables"},
+  {0, "Lock tables","Databases","To use LOCK TABLES (together with SELECT privilege)"},
+  {0, "Process", "Server Admin", "To view the plain text of currently executing queries"},
+  {0, "Proxy", "Server Admin", "To make proxy user possible"},
+  {0, "References", "Databases,Tables", "To have references on tables"},
+  {0, "Reload", "Server Admin", "To reload or refresh tables, logs and privileges"},
+  {100502, "Binlog admin", "Server", "To purge binary logs"},
+  /* Replication Client replaced iwht binlog monitor in 10.5.2 */
+  {0, "Replication client","Server Admin","To ask where the slave or master servers are"},
+  {100502, "Binlog monitor", "Server", "To use SHOW BINLOG STATUS and SHOW BINARY LOG"},
+  {100502, "Replication master admin", "Server", "To monitor connected slaves"},
+  {100502, "Replication slave admin", "Server", "To start/stop slave and apply binlog events"},
+  {100508, "Slave monitor", "Server", "To use SHOW SLAVE STATUS and SHOW RELAYLOG EVENTS"},
+  {0, "Replication slave","Server Admin","To read binary log events from the master"},
+  {0, "Select", "Tables",  "To retrieve rows from table"},
+  {0, "Show databases","Server Admin","To see all databases with SHOW DATABASES"},
+  {0, "Show view","Tables","To see views with SHOW CREATE VIEW"},
+  {0, "Shutdown","Server Admin", "To shut down the server"},
+  {0, "Super","Server Admin","To use KILL thread, SET GLOBAL, CHANGE MASTER, etc."},
+  {0, "Trigger","Tables", "To use triggers"},
+  {0, "Create tablespace", "Server Admin", "To create/alter/drop tablespaces"},
+  {0, "Update", "Tables",  "To update existing rows"},
+  {100502, "Set user","Server", "To create views and stored routines with a different definer"},
+  {100502, "Federated admin", "Server", "To execute the CREATE SERVER, ALTER SERVER, DROP SERVER statements"},
+  {100502, "Connection admin", "Server", "To bypass connection limits and kill other users' connections"},
+  {100502, "Read_only admin", "Server", "To perform write operations even if @@read_only=ON"},
+  {0, "Usage","Server Admin","No privileges - allow connect only"},
+  {0, NullS, NullS, NullS}
+};
diff --git a/sql/sql_show.cc b/sql/sql_show.cc
index 3e9916816b9..bcd9eb6561d 100644
--- a/sql/sql_show.cc
+++ b/sql/sql_show.cc
@@ -544,54 +544,7 @@ bool mysqld_show_contributors(THD *thd)
   DBUG_RETURN(FALSE);
 }
 
-
-/***************************************************************************
- List all privileges supported
-***************************************************************************/
-
-struct show_privileges_st {
-  const char *privilege;
-  const char *context;
-  const char *comment;
-};
-
-static struct show_privileges_st sys_privileges[]=
-{
-  {"Alter", "Tables",  "To alter the table"},
-  {"Alter routine", "Functions,Procedures",  "To alter or drop stored functions/procedures"},
-  {"Create", "Databases,Tables,Indexes",  "To create new databases and tables"},
-  {"Create routine","Databases","To use CREATE FUNCTION/PROCEDURE"},
-  {"Create temporary tables","Databases","To use CREATE TEMPORARY TABLE"},
-  {"Create view", "Tables",  "To create new views"},
-  {"Create user", "Server Admin",  "To create new users"},
-  {"Delete", "Tables",  "To delete existing rows"},
-  {"Drop", "Databases,Tables", "To drop databases, tables, and views"},
-#ifdef HAVE_EVENT_SCHEDULER
-  {"Event","Server Admin","To create, alter, drop and execute events"},
-#endif
-  {"Execute", "Functions,Procedures", "To execute stored routines"},
-  {"File", "File access on server",   "To read and write files on the server"},
-  {"Grant option",  "Databases,Tables,Functions,Procedures", "To give to other users those privileges you possess"},
-  {"Index", "Tables",  "To create or drop indexes"},
-  {"Insert", "Tables",  "To insert data into tables"},
-  {"Lock tables","Databases","To use LOCK TABLES (together with SELECT privilege)"},
-  {"Process", "Server Admin", "To view the plain text of currently executing queries"},
-  {"Proxy", "Server Admin", "To make proxy user possible"},
-  {"References", "Databases,Tables", "To have references on tables"},
-  {"Reload", "Server Admin", "To reload or refresh tables, logs and privileges"},
-  {"Replication client","Server Admin","To ask where the slave or master servers are"},
-  {"Replication slave","Server Admin","To read binary log events from the master"},
-  {"Select", "Tables",  "To retrieve rows from table"},
-  {"Show databases","Server Admin","To see all databases with SHOW DATABASES"},
-  {"Show view","Tables","To see views with SHOW CREATE VIEW"},
-  {"Shutdown","Server Admin", "To shut down the server"},
-  {"Super","Server Admin","To use KILL thread, SET GLOBAL, CHANGE MASTER, etc."},
-  {"Trigger","Tables", "To use triggers"},
-  {"Create tablespace", "Server Admin", "To create/alter/drop tablespaces"},
-  {"Update", "Tables",  "To update existing rows"},
-  {"Usage","Server Admin","No privileges - allow connect only"},
-  {NullS, NullS, NullS}
-};
+#include <my_static_privs.h>
 
 bool mysqld_show_privileges(THD *thd)
 {
@@ -616,6 +569,8 @@ bool mysqld_show_privileges(THD *thd)
   show_privileges_st *privilege= sys_privileges;
   for (privilege= sys_privileges; privilege->privilege ; privilege++)
   {
+    if (privilege->version > MYSQL_VERSION_ID)
+      continue;
     protocol->prepare_for_resend();
     protocol->store(privilege->privilege, system_charset_info);
     protocol->store(privilege->context, system_charset_info);