MDEV-29509 execute granted indirectly (via roles) doesn't always work

The issue manifests due to a bug in mysql_routine_grant. This was a side
effect of e46eea8660fb which fixed the problem of not giving appropriate error
message (ER_NONEXISTING_PROC_GRANT) when a routine grant existed due to role
inheritance.

When granting a routine privilege, it is possible to have a GRANT_NAME
entry already created from an inherited role, but with it's init_privs
set to 0.

In this case we must not create a *new* grant entry, but we must edit
this grant entry to set its init_privs.

Note that this case was already covered by MDEV-29458, however due to a
forgotten "flush privileges;" the actual code path never got hit.
Remove the flush privilege command as it was never intended to be there
in the first place.

3 files changed, 10 insertions, 11 deletions

diff --git a/mysql-test/suite/roles/role_grant_propagate-29458.result b/mysql-test/suite/roles/role_grant_propagate-29458.result
index a8ee6e7d987..28aa053f38a 100644
--- a/mysql-test/suite/roles/role_grant_propagate-29458.result
+++ b/mysql-test/suite/roles/role_grant_propagate-29458.result
@@ -79,7 +79,6 @@ grant insert(a) on some_db.t1 to r_active_column;
 grant insert on *.* to middle_level;
 grant alter routine on procedure some_db.p1 to r_active_proc;
 grant alter routine on function some_db.f1 to r_active_func;
-flush privileges;
 connect  con1, localhost, foo,,;
 select * from some_db.t1;
 ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't1'
diff --git a/mysql-test/suite/roles/role_grant_propagate-29458.test b/mysql-test/suite/roles/role_grant_propagate-29458.test
index 07c29a3800c..1b0906dce25 100644
--- a/mysql-test/suite/roles/role_grant_propagate-29458.test
+++ b/mysql-test/suite/roles/role_grant_propagate-29458.test
@@ -103,7 +103,6 @@ grant insert on *.* to middle_level;
 
 grant alter routine on procedure some_db.p1 to r_active_proc;
 grant alter routine on function some_db.f1 to r_active_func;
-flush privileges;
 
 --connect (con1, localhost, foo,,)
 --error ER_TABLEACCESS_DENIED_ERROR
diff --git a/sql/sql_acl.cc b/sql/sql_acl.cc
index ecf78f0e049..3b514ed4dee 100644
--- a/sql/sql_acl.cc
+++ b/sql/sql_acl.cc
@@ -9502,20 +9502,21 @@ static bool mysql_routine_grant(THD *thd, TABLE_LIST *table_list,
     table_name= table_list->table_name.str;
     grant_name= routine_hash_search(user->host.str, NullS, db_name,
                                     user->user.str, table_name, sph, 1);
-    if (!grant_name || !grant_name->init_privs)
+    if (revoke_grant && (!grant_name || !grant_name->init_privs))
     {
-      if (revoke_grant)
-      {
-        my_error(ER_NONEXISTING_PROC_GRANT, MYF(0),
-                 user->user.str, user->host.str, table_name);
-        result= TRUE;
-        continue;
-      }
+      my_error(ER_NONEXISTING_PROC_GRANT, MYF(0),
+               user->user.str, user->host.str, table_name);
+      result= TRUE;
+      continue;
+    }
+    if (!grant_name)
+    {
+      DBUG_ASSERT(!revoke_grant);
       grant_name= new GRANT_NAME(user->host.str, db_name,
                                  user->user.str, table_name,
                                  rights, TRUE);
       if (!grant_name ||
-        my_hash_insert(sph.get_priv_hash(), (uchar*) grant_name))
+          my_hash_insert(sph.get_priv_hash(), (uchar*) grant_name))
       {
         result= TRUE;
         continue;