MDEV-14443: Show grants denies for routines

3 files changed, 135 insertions, 6 deletions

diff --git a/mysql-test/suite/deny/show_grants.result b/mysql-test/suite/deny/show_grants.result
index 015a0d783e4..8bbd4d34a49 100644
--- a/mysql-test/suite/deny/show_grants.result
+++ b/mysql-test/suite/deny/show_grants.result
@@ -41,6 +41,13 @@ deny insert (c3, c4) on one_db.t1 to bar;
 deny all privileges on one_db.t2 to bar;
 deny select (c_b, c_a) on one_db.t2 to bar;
 deny usage on one_db.t2 to bar with grant option;
+deny execute on procedure some_other_db.p1 to bar;
+deny execute on function some_other_db.p1 to bar;
+set @old_sql_mode=@@sql_mode;
+set sql_mode=ORACLE;
+deny execute on package some_other_db.p1 to bar;
+deny all on package body some_other_db.p1 to bar;
+set sql_mode=@old_sql_mode;
 grant bar to foo;
 connect  con1, localhost, foo,,;
 set role bar;
@@ -57,6 +64,10 @@ DENY SELECT ON *.* TO `bar`
 DENY INSERT ON `one_db`.* TO `bar`
 DENY INSERT(c1,c2,c3,c4), UPDATE, UPDATE(c1) ON `one_db`.`t1` TO `bar`
 DENY ALL PRIVILEGES, SELECT(c_a,c_b) ON `one_db`.`t2` TO `bar` WITH GRANT OPTION
+DENY EXECUTE ON PROCEDURE `some_other_db`.`p1` TO `bar`
+DENY EXECUTE ON FUNCTION `some_other_db`.`p1` TO `bar`
+DENY EXECUTE ON PACKAGE `some_other_db`.`p1` TO `bar`
+DENY EXECUTE, ALTER ROUTINE ON PACKAGE BODY `some_other_db`.`p1` TO `bar`
 disconnect con1;
 connection default;
 drop database some_db;
diff --git a/mysql-test/suite/deny/show_grants.test b/mysql-test/suite/deny/show_grants.test
index 4968b86e7d1..7469d96d6c0 100644
--- a/mysql-test/suite/deny/show_grants.test
+++ b/mysql-test/suite/deny/show_grants.test
@@ -48,6 +48,17 @@ deny all privileges on one_db.t2 to bar;
 deny select (c_b, c_a) on one_db.t2 to bar;
 deny usage on one_db.t2 to bar with grant option;
 
+deny execute on procedure some_other_db.p1 to bar;
+deny execute on function some_other_db.p1 to bar;
+
+set @old_sql_mode=@@sql_mode;
+set sql_mode=ORACLE;
+
+deny execute on package some_other_db.p1 to bar;
+deny all on package body some_other_db.p1 to bar;
+
+set sql_mode=@old_sql_mode;
+
 grant bar to foo;
 --connect (con1, localhost, foo,,)
 set role bar;
diff --git a/sql/sql_acl.cc b/sql/sql_acl.cc
index 9f34ddf4902..cb73ccf28da 100644
--- a/sql/sql_acl.cc
+++ b/sql/sql_acl.cc
@@ -1227,6 +1227,8 @@ static bool show_table_and_column_denies(THD *, const Deny_spec &,
                                          const char *, const char *);
 static int show_routine_grants(THD *, const char *, const char *,
                                const Sp_handler *sph, char *, int);
+static int show_routine_denies(THD *, const Deny_spec &, const char *,
+                               const char *, const Sp_handler *, char *, int );
 
 static const ACL_internal_schema_access *
 get_cached_schema_access(GRANT_INTERNAL_INFO *grant_internal_info,
@@ -11713,26 +11715,46 @@ static bool print_grants_for_role(THD *thd, ACL_ROLE * role)
   if (show_table_and_column_privileges(thd, role->user.str, "", buff, sizeof(buff)))
     return TRUE;
 
-  if (role->denies)
-    if (show_table_and_column_denies(thd, *role->denies, role->user.str, ""))
+  if (role->initial_denies)
+    if (show_table_and_column_denies(thd, *role->initial_denies, role->user.str, ""))
       return TRUE;
 
   if (show_routine_grants(thd, role->user.str, "", &sp_handler_procedure,
                           buff, sizeof(buff)))
     return TRUE;
 
+  if (role->initial_denies &&
+      show_routine_denies(thd, *role->initial_denies, role->user.str, "",
+                          &sp_handler_procedure, buff, sizeof(buff)))
+    return TRUE;
+
   if (show_routine_grants(thd, role->user.str, "", &sp_handler_function,
                           buff, sizeof(buff)))
     return TRUE;
 
+  if (role->initial_denies &&
+      show_routine_denies(thd, *role->initial_denies, role->user.str, "",
+                          &sp_handler_function, buff, sizeof(buff)))
+    return TRUE;
+
   if (show_routine_grants(thd, role->user.str, "", &sp_handler_package_spec,
                           buff, sizeof(buff)))
     return TRUE;
 
+  if (role->initial_denies &&
+      show_routine_denies(thd, *role->initial_denies, role->user.str, "",
+                          &sp_handler_package_spec, buff, sizeof(buff)))
+    return TRUE;
+
   if (show_routine_grants(thd, role->user.str, "", &sp_handler_package_body,
                           buff, sizeof(buff)))
     return TRUE;
 
+  if (role->initial_denies &&
+      show_routine_denies(thd, *role->initial_denies, role->user.str, "",
+                          &sp_handler_package_body, buff, sizeof(buff)))
+    return TRUE;
+
   return FALSE;
 
 }
@@ -12007,17 +12029,37 @@ bool mysql_show_grants(THD *thd, LEX_USER *lex_user)
                             buff, sizeof(buff)))
       goto end;
 
+    if (acl_user->denies &&
+        show_routine_denies(thd, *acl_user->denies, username, hostname,
+                            &sp_handler_procedure, buff, sizeof(buff)))
+      goto end;
+
     if (show_routine_grants(thd, username, hostname, &sp_handler_function,
                             buff, sizeof(buff)))
       goto end;
 
+    if (acl_user->denies &&
+        show_routine_denies(thd, *acl_user->denies, username, hostname,
+                            &sp_handler_function, buff, sizeof(buff)))
+      goto end;
+
     if (show_routine_grants(thd, username, hostname, &sp_handler_package_spec,
                             buff, sizeof(buff)))
       goto end;
 
+
+    if (acl_user->denies &&
+        show_routine_denies(thd, *acl_user->denies, username, hostname,
+                            &sp_handler_package_spec, buff, sizeof(buff)))
+      goto end;
+
     if (show_routine_grants(thd, username, hostname, &sp_handler_package_body,
                             buff, sizeof(buff)))
       goto end;
+    if (acl_user->denies &&
+        show_routine_denies(thd, *acl_user->denies, username, hostname,
+                            &sp_handler_package_body, buff, sizeof(buff)))
+      goto end;
 
     if (show_proxy_grants(thd, username, hostname, buff, sizeof(buff)))
       goto end;
@@ -12364,6 +12406,7 @@ static int print_col_list_matching_priv(
   return err ? -1 : paren_printed;
 }
 
+
 static bool col_list_has_priv(
     const Dynamic_array<std::pair<LEX_CSTRING, privilege_t>>& arr,
     privilege_t priv)
@@ -12707,13 +12750,17 @@ static bool print_routine_grants(THD *thd, String *result,
                                  privilege_t routine_priv,
                                  const LEX_CSTRING &routine_type,
                                  const char *username, const char *hostname,
-                                 const char *db, const char *routine_name)
+                                 const char *db, const char *routine_name,
+                                 bool deny)
 {
   //TODO(cvicentiu) Error checking for this function.
   privilege_t test_access(routine_priv & ~GRANT_ACL);
 
   result->length(0);
-  result->append(STRING_WITH_LEN("GRANT "));
+  if (deny)
+    result->append(STRING_WITH_LEN("DENY "));
+  else
+    result->append(STRING_WITH_LEN("GRANT "));
 
   if (!test_access)
     result->append(STRING_WITH_LEN("USAGE"));
@@ -12746,6 +12793,66 @@ static bool print_routine_grants(THD *thd, String *result,
 }
 
 
+static int show_routine_denies(THD *thd,
+                               const Deny_spec &denies,
+                               const char *username, const char *hostname,
+                               const Sp_handler *sph,
+                               char *buff, int buffsize)
+{
+  bool error= 0;
+  Protocol *protocol= thd->protocol;
+  String result(buff, buffsize, system_charset_info);
+
+  PRIV_TYPE priv_type= NO_PRIV;
+  enum_sp_type type= sph->type();
+
+  switch (type)
+  {
+    case SP_TYPE_FUNCTION:
+      priv_type= FUNCTION_PRIV;
+      break;
+    case SP_TYPE_PROCEDURE:
+      priv_type= PROCEDURE_PRIV;
+      break;
+    case SP_TYPE_PACKAGE:
+      priv_type= PACKAGE_SPEC_PRIV;
+      break;
+    case SP_TYPE_PACKAGE_BODY:
+      priv_type= PACKAGE_BODY_PRIV;
+      break;
+    /* No privileges attached to triggers or events. */
+    case SP_TYPE_TRIGGER:
+    case SP_TYPE_EVENT:
+      DBUG_ASSERT(0);
+      return 0;
+  }
+  for (size_t i= 0; i < denies.get_hash_size(priv_type); i++)
+  {
+    auto *entry= denies.get_hash_entry(priv_type, i);
+
+    LEX_CSTRING db, routine_name;
+    Deny_spec::deconstruct_identifier(entry->first, &db, &routine_name);
+
+    error= print_routine_grants(thd, &result,
+                                entry->second,
+                                sph->type_lex_cstring(),
+                                username, hostname,
+                                db.str, routine_name.str,
+                                true);
+
+    protocol->prepare_for_resend();
+    protocol->store(&result);
+    if (protocol->write())
+    {
+      error= true;
+      break;
+    }
+  }
+
+  return error;
+}
+
+
 static int show_routine_grants(THD* thd,
                                const char *username, const char *hostname,
                                const Sp_handler *sph,
@@ -12785,9 +12892,9 @@ static int show_routine_grants(THD* thd,
       String global(buff, buffsize, system_charset_info);
       print_routine_grants(thd, &global, proc_access, sph->type_lex_cstring(),
                            username, hostname, grant_proc->db,
-                           grant_proc->tname);
+                           grant_proc->tname, false);
       protocol->prepare_for_resend();
-      protocol->store(global.ptr(),global.length(),global.charset());
+      protocol->store(global);
       if (protocol->write())
       {
         error= -1;