MDEV-5876: MySQL bug #11766767 - "59957: VIEW USING MERGE PERMISSIONS IN MULTI-TABLE UPDATE"

Backported multi_update_check_table_access() from 5.6

The code is slightly different in MariaDB, becasue we instansiate fields in merged tables earlier.

mysql-test/mysql-test-run.pl:
  Fixed comment
mysql-test/r/view_grant.result:
  Merged test case from 5.6
mysql-test/t/view_grant.test:
  Merged test case from 5.6
sql/sql_parse.cc:
  Reset orig_want_privilege as this will be rechecked later.
  If not, we will have a problem in mysql_multi_update_prepare() for the call to mysql_handle_derived()
sql/sql_update.cc:
  Backport multi_update_check_table_access() from 5.6

5 files changed, 401 insertions, 12 deletions

diff --git a/mysql-test/mysql-test-run.pl b/mysql-test/mysql-test-run.pl
index d3e866673a3..883ddd1d672 100755
--- a/mysql-test/mysql-test-run.pl
+++ b/mysql-test/mysql-test-run.pl
@@ -5229,7 +5229,7 @@ sub report_failure_and_restart ($) {
     # In these cases we may want valgrind report from normal termination
     $tinfo->{'dont_kill_server'}= 1;
   }
-  # Shotdown properly if not to be killed (for valgrind)
+  # Shutdown properly if not to be killed (for valgrind)
   stop_all_servers($tinfo->{'dont_kill_server'} ? $opt_shutdown_timeout : 0);
 
   $tinfo->{'result'}= 'MTR_RES_FAILED';
diff --git a/mysql-test/r/view_grant.result b/mysql-test/r/view_grant.result
index 85e64078daf..8f0ffdda742 100644
--- a/mysql-test/r/view_grant.result
+++ b/mysql-test/r/view_grant.result
@@ -1575,3 +1575,152 @@ USE test;
 DROP DATABASE mysqltest1;
 DROP USER 'mysqluser1'@'%';
 DROP USER 'mysqluser2'@'%';
+#
+# Test for bug #11766767 - "59957: VIEW USING MERGE PERMISSIONS
+#                           IN MULTI-TABLE UPDATE".
+#
+drop database if exists mysqltest1;
+drop database if exists mysqltest2;
+#
+# Prepare playground. 
+create database mysqltest1;
+create database mysqltest2;
+create user user_11766767;
+grant select on mysqltest1.* to user_11766767;
+grant all on mysqltest2.* to user_11766767;
+use mysqltest1;
+create table t1 (id int primary key, val varchar(20));
+insert into t1 values (1, 'test1');
+create table t11 (id int primary key);
+insert into t11 values (1);
+create algorithm=temptable view v1_temp as select * from t1;
+create algorithm=merge view v1_merge as select * from t1;
+create algorithm=temptable view v11_temp as
+select t1.id as id, t1.val as val from t1, t11 where t1.id= t11.id;
+create algorithm=merge view v11_merge as
+select t1.id as id, t1.val as val from t1, t11 where t1.id= t11.id;
+use mysqltest2;
+create table t2 (id int primary key, val varchar(20));
+insert into t2 values (1, 'test2');
+create table t21 (id int primary key);
+insert into t21 values (1);
+create algorithm=temptable view v2_temp as select * from t2;
+create algorithm=merge view v2_merge as select * from t2;
+create algorithm=temptable view v21_temp as 
+select t2.id as id, t2.val as val from t2, t21 where t2.id= t21.id;
+create algorithm=merge view v21_merge as
+select t2.id as id, t2.val as val from t2, t21 where t2.id= t21.id;
+create algorithm=temptable sql security invoker view v3_temp as
+select t1.id as id, t1.val as val from mysqltest1.t1 as t1, mysqltest1.t11 as t11
+where t1.id = t11.id;
+create algorithm=merge sql security invoker view v3_merge as
+select t1.id as id, t1.val as val from mysqltest1.t1 as t1, mysqltest1.t11 as t11
+where t1.id = t11.id;
+create sql security invoker view v31 as
+select t2.id as id, t2.val as val from mysqltest2.t2 as t2, mysqltest1.t11 as t11
+where t2.id = t11.id;
+create sql security invoker view v4 as
+select t2.id as id, t2.val as val from mysqltest2.t2 as t2, mysqltest1.v1_merge as v1
+where t2.id = v1.id;
+create sql security invoker view v41 as
+select v1.id as id, v1.val as val from mysqltest2.t2 as t2, mysqltest1.v1_merge as v1
+where t2.id = v1.id;
+create sql security invoker view v42 as
+select v2.id as id, v2.val as val from mysqltest2.t2 as t2, mysqltest2.v2_merge as v2
+where t2.id = v2.id;
+#
+# Connect as user_11766767
+#
+# A) Check how we handle privilege checking in multi-update for 
+#    directly used views.
+#
+# A.1) Originally reported problem, view is used in read-only mode.
+#      This should work with only SELECT privilege for both mergeable
+#      and temptable algorithms.
+update mysqltest2.t2 as t2, mysqltest1.v1_merge as v1 set t2.val= 'test3'
+  where t2.id= v1.id;
+update mysqltest2.t2 as t2, mysqltest1.v1_temp as v1 set t2.val= 'test4'
+  where t2.id= v1.id;
+#
+# A.2) If view is updated an UPDATE privilege on it is required.
+#      Temptable views can't be updated.
+update mysqltest2.t2 as t2, mysqltest1.v1_merge as v1 set v1.val= 'test5'
+  where t2.id= v1.id;
+ERROR 42000: UPDATE command denied to user 'user_11766767'@'localhost' for table 'v1_merge'
+update mysqltest1.t1 as t1, mysqltest2.v2_merge as v2 set v2.val= 'test6'
+  where t1.id= v2.id;
+#
+#      Note that the below error is OK even though user lacks UPDATE
+#      privilege on v1_temp since he/she still has SELECT privilege on
+#      this view.
+update mysqltest2.t2 as t2, mysqltest1.v1_temp as v1 set v1.val= 'test7'
+  where t2.id= v1.id;
+ERROR HY000: The target table v1 of the UPDATE is not updatable
+update mysqltest1.t1 as t1, mysqltest2.v2_temp as v2 set v2.val= 'test8'
+  where t1.id= v2.id;
+ERROR HY000: The target table v2 of the UPDATE is not updatable
+#
+# A.3) This also works for correctly for multi-table views.
+#      When usage is read-only SELECT is enough.
+update mysqltest2.t2 as t2, mysqltest1.v11_merge as v11 set t2.val= 'test9'
+  where t2.id= v11.id;
+update mysqltest2.t2 as t2, mysqltest1.v11_temp as v11 set t2.val= 'test10'
+  where t2.id= v11.id;
+#      When one of view's tables is updated, UPDATE is required
+#      on a view.
+update mysqltest2.t2 as t2, mysqltest1.v11_merge as v11 set v11.val= 'test11'
+  where t2.id= v11.id;
+ERROR 42000: UPDATE command denied to user 'user_11766767'@'localhost' for table 'v11_merge'
+update mysqltest1.t1 as t1, mysqltest2.v21_merge as v21 set v21.val= 'test12'
+  where t1.id= v21.id;
+#      As before, temptable views are not updateable.
+update mysqltest2.t2 as t2, mysqltest1.v11_temp as v11 set v11.val= 'test13'
+  where t2.id= v11.id;
+ERROR HY000: The target table v11 of the UPDATE is not updatable
+update mysqltest1.t1 as t1, mysqltest2.v21_temp as v21 set v21.val= 'test14'
+  where t1.id= v21.id;
+ERROR HY000: The target table v21 of the UPDATE is not updatable
+#
+# B) Now check that correct privileges are required on underlying
+#    tables. To simplify this part of test we will use SECURITY
+#    INVOKER views in it.
+#
+# B.1) In case when view is used for read only it is enough to have
+#      SELECT on its underlying tables.
+update mysqltest2.t2 as t2, mysqltest2.v3_merge as v3 set t2.val= 'test15'
+  where t2.id= v3.id;
+update mysqltest2.t2 as t2, mysqltest2.v3_temp as v3 set t2.val= 'test16'
+  where t2.id= v3.id;
+#
+# B.2) If view is updated, UPDATE privilege on the table being updated
+#      is required (since we already checked that temptable views are
+#      not updateable we don't test them here).
+update mysqltest2.t2 as t2, mysqltest2.v3_merge as v3 set v3.val= 'test17'
+  where t2.id= v3.id;
+ERROR HY000: View 'mysqltest2.v3_merge' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
+update mysqltest1.t11 as t11, mysqltest2.v31 as v31 set v31.val= 'test18'
+  where t11.id= v31.id;
+#
+# C) Finally, check how we handle privilege checking in case when
+#    view is used through another view. Again we will use SECURITY
+#    INVOKER views for simplicity.
+#
+# C.1) As usual, when a view used by another view is going to be used
+#      in read-only fashion, only SELECT privilege is necessary.
+update mysqltest1.t11 as t11, mysqltest2.v4 as v4 set v4.val= 'test19'
+  where t11.id= v4.id;
+#
+# C.2) If one of underlying tables of the view is updated then
+#      UPDATE on a view is necessary.
+update mysqltest1.t11 as t11, mysqltest2.v41 as v4 set v4.val= 'test20'
+  where t11.id= v4.id;
+ERROR HY000: View 'mysqltest2.v41' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
+update mysqltest1.t11 as t11, mysqltest2.v42 as v4 set v4.val= 'test20'
+  where t11.id= v4.id;
+#
+# Clean-up.
+#
+# Switching to connection 'default'.
+drop user user_11766767;
+drop database mysqltest1;
+drop database mysqltest2;
diff --git a/mysql-test/t/view_grant.test b/mysql-test/t/view_grant.test
index 847153d19f4..9477f93c94f 100644
--- a/mysql-test/t/view_grant.test
+++ b/mysql-test/t/view_grant.test
@@ -2047,6 +2047,166 @@ DROP DATABASE mysqltest1;
 DROP USER 'mysqluser1'@'%';
 DROP USER 'mysqluser2'@'%';
 
+--echo #
+--echo # Test for bug #11766767 - "59957: VIEW USING MERGE PERMISSIONS
+--echo #                           IN MULTI-TABLE UPDATE".
+--echo #
+--disable_warnings
+drop database if exists mysqltest1;
+drop database if exists mysqltest2;
+--enable_warnings
+--echo #
+--echo # Prepare playground. 
+create database mysqltest1;
+create database mysqltest2;
+create user user_11766767;
+grant select on mysqltest1.* to user_11766767;
+grant all on mysqltest2.* to user_11766767;
+use mysqltest1;
+create table t1 (id int primary key, val varchar(20));
+insert into t1 values (1, 'test1');
+create table t11 (id int primary key);
+insert into t11 values (1);
+create algorithm=temptable view v1_temp as select * from t1;
+create algorithm=merge view v1_merge as select * from t1;
+create algorithm=temptable view v11_temp as
+  select t1.id as id, t1.val as val from t1, t11 where t1.id= t11.id;
+create algorithm=merge view v11_merge as
+  select t1.id as id, t1.val as val from t1, t11 where t1.id= t11.id;
+use mysqltest2;
+create table t2 (id int primary key, val varchar(20));
+insert into t2 values (1, 'test2');
+create table t21 (id int primary key);
+insert into t21 values (1);
+create algorithm=temptable view v2_temp as select * from t2;
+create algorithm=merge view v2_merge as select * from t2;
+create algorithm=temptable view v21_temp as 
+  select t2.id as id, t2.val as val from t2, t21 where t2.id= t21.id;
+create algorithm=merge view v21_merge as
+  select t2.id as id, t2.val as val from t2, t21 where t2.id= t21.id;
+create algorithm=temptable sql security invoker view v3_temp as
+  select t1.id as id, t1.val as val from mysqltest1.t1 as t1, mysqltest1.t11 as t11
+    where t1.id = t11.id;
+create algorithm=merge sql security invoker view v3_merge as
+  select t1.id as id, t1.val as val from mysqltest1.t1 as t1, mysqltest1.t11 as t11
+    where t1.id = t11.id;
+create sql security invoker view v31 as
+  select t2.id as id, t2.val as val from mysqltest2.t2 as t2, mysqltest1.t11 as t11
+    where t2.id = t11.id;
+create sql security invoker view v4 as
+  select t2.id as id, t2.val as val from mysqltest2.t2 as t2, mysqltest1.v1_merge as v1
+    where t2.id = v1.id;
+create sql security invoker view v41 as
+  select v1.id as id, v1.val as val from mysqltest2.t2 as t2, mysqltest1.v1_merge as v1
+    where t2.id = v1.id;
+create sql security invoker view v42 as
+  select v2.id as id, v2.val as val from mysqltest2.t2 as t2, mysqltest2.v2_merge as v2
+    where t2.id = v2.id;
+
+
+--echo #
+--echo # Connect as user_11766767
+connect (conn_11766767, localhost, user_11766767,,);
+
+--echo #
+--echo # A) Check how we handle privilege checking in multi-update for 
+--echo #    directly used views.
+--echo #
+--echo # A.1) Originally reported problem, view is used in read-only mode.
+--echo #      This should work with only SELECT privilege for both mergeable
+--echo #      and temptable algorithms.
+update mysqltest2.t2 as t2, mysqltest1.v1_merge as v1 set t2.val= 'test3'
+  where t2.id= v1.id;
+update mysqltest2.t2 as t2, mysqltest1.v1_temp as v1 set t2.val= 'test4'
+  where t2.id= v1.id;
+--echo #
+--echo # A.2) If view is updated an UPDATE privilege on it is required.
+--echo #      Temptable views can't be updated.
+--error ER_TABLEACCESS_DENIED_ERROR
+update mysqltest2.t2 as t2, mysqltest1.v1_merge as v1 set v1.val= 'test5'
+  where t2.id= v1.id;
+update mysqltest1.t1 as t1, mysqltest2.v2_merge as v2 set v2.val= 'test6'
+  where t1.id= v2.id;
+--echo #
+--echo #      Note that the below error is OK even though user lacks UPDATE
+--echo #      privilege on v1_temp since he/she still has SELECT privilege on
+--echo #      this view.
+--error ER_NON_UPDATABLE_TABLE
+update mysqltest2.t2 as t2, mysqltest1.v1_temp as v1 set v1.val= 'test7'
+  where t2.id= v1.id;
+--error ER_NON_UPDATABLE_TABLE
+update mysqltest1.t1 as t1, mysqltest2.v2_temp as v2 set v2.val= 'test8'
+  where t1.id= v2.id;
+--echo #
+--echo # A.3) This also works for correctly for multi-table views.
+--echo #      When usage is read-only SELECT is enough.
+update mysqltest2.t2 as t2, mysqltest1.v11_merge as v11 set t2.val= 'test9'
+  where t2.id= v11.id;
+update mysqltest2.t2 as t2, mysqltest1.v11_temp as v11 set t2.val= 'test10'
+  where t2.id= v11.id;
+--echo #      When one of view's tables is updated, UPDATE is required
+--echo #      on a view.
+--error ER_TABLEACCESS_DENIED_ERROR
+update mysqltest2.t2 as t2, mysqltest1.v11_merge as v11 set v11.val= 'test11'
+  where t2.id= v11.id;
+update mysqltest1.t1 as t1, mysqltest2.v21_merge as v21 set v21.val= 'test12'
+  where t1.id= v21.id;
+--echo #      As before, temptable views are not updateable.
+--error ER_NON_UPDATABLE_TABLE
+update mysqltest2.t2 as t2, mysqltest1.v11_temp as v11 set v11.val= 'test13'
+  where t2.id= v11.id;
+--error ER_NON_UPDATABLE_TABLE
+update mysqltest1.t1 as t1, mysqltest2.v21_temp as v21 set v21.val= 'test14'
+  where t1.id= v21.id;
+
+--echo #
+--echo # B) Now check that correct privileges are required on underlying
+--echo #    tables. To simplify this part of test we will use SECURITY
+--echo #    INVOKER views in it.
+--echo #
+--echo # B.1) In case when view is used for read only it is enough to have
+--echo #      SELECT on its underlying tables.
+update mysqltest2.t2 as t2, mysqltest2.v3_merge as v3 set t2.val= 'test15'
+  where t2.id= v3.id;
+update mysqltest2.t2 as t2, mysqltest2.v3_temp as v3 set t2.val= 'test16'
+  where t2.id= v3.id;
+--echo #
+--echo # B.2) If view is updated, UPDATE privilege on the table being updated
+--echo #      is required (since we already checked that temptable views are
+--echo #      not updateable we don't test them here).
+--error ER_VIEW_INVALID
+update mysqltest2.t2 as t2, mysqltest2.v3_merge as v3 set v3.val= 'test17'
+  where t2.id= v3.id;
+update mysqltest1.t11 as t11, mysqltest2.v31 as v31 set v31.val= 'test18'
+  where t11.id= v31.id;
+
+--echo #
+--echo # C) Finally, check how we handle privilege checking in case when
+--echo #    view is used through another view. Again we will use SECURITY
+--echo #    INVOKER views for simplicity.
+--echo #
+--echo # C.1) As usual, when a view used by another view is going to be used
+--echo #      in read-only fashion, only SELECT privilege is necessary.
+update mysqltest1.t11 as t11, mysqltest2.v4 as v4 set v4.val= 'test19'
+  where t11.id= v4.id;
+--echo #
+--echo # C.2) If one of underlying tables of the view is updated then
+--echo #      UPDATE on a view is necessary.
+--error ER_VIEW_INVALID
+update mysqltest1.t11 as t11, mysqltest2.v41 as v4 set v4.val= 'test20'
+  where t11.id= v4.id;
+update mysqltest1.t11 as t11, mysqltest2.v42 as v4 set v4.val= 'test20'
+  where t11.id= v4.id;
+
+--echo #
+--echo # Clean-up.
+--echo #
+--echo # Switching to connection 'default'.
+disconnect conn_11766767;
+connection default;
+drop user user_11766767;
+drop database mysqltest1;
+drop database mysqltest2;
 
 # Wait till we reached the initial number of concurrent sessions
 --source include/wait_until_count_sessions.inc
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index 830661a5b81..65a76538c73 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -7685,6 +7685,7 @@ bool multi_update_precheck(THD *thd, TABLE_LIST *tables)
               check_grant(thd, SELECT_ACL, table, FALSE, 1, FALSE)))
       DBUG_RETURN(TRUE);
 
+    table->grant.orig_want_privilege= 0;
     table->table_in_first_from_clause= 1;
   }
   /*
diff --git a/sql/sql_update.cc b/sql/sql_update.cc
index b9655984264..01fe0336814 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -1217,6 +1217,87 @@ bool unsafe_key_update(List<TABLE_LIST> leaves, table_map tables_for_update)
   return false;
 }
 
+/**
+  Check if there is enough privilege on specific table used by the
+  main select list of multi-update directly or indirectly (through
+  a view).
+
+  @param[in]      thd                Thread context.
+  @param[in]      table              Table list element for the table.
+  @param[in]      tables_for_update  Bitmap with tables being updated.
+  @param[in/out]  updated_arg        Set to true if table in question is
+                                     updated, also set to true if it is
+                                     a view and one of its underlying
+                                     tables is updated. Should be
+                                     initialized to false by the caller
+                                     before a sequence of calls to this
+                                     function.
+
+  @note To determine which tables/views are updated we have to go from
+        leaves to root since tables_for_update contains map of leaf
+        tables being updated and doesn't include non-leaf tables
+        (fields are already resolved to leaf tables).
+
+  @retval false - Success, all necessary privileges on all tables are
+                  present or might be present on column-level.
+  @retval true  - Failure, some necessary privilege on some table is
+                  missing.
+*/
+
+static bool multi_update_check_table_access(THD *thd, TABLE_LIST *table,
+                                            table_map tables_for_update,
+                                            bool *updated_arg)
+{
+  if (table->view)
+  {
+    bool updated= false;
+    /*
+      If it is a mergeable view then we need to check privileges on its
+      underlying tables being merged (including views). We also need to
+      check if any of them is updated in order to find if this view is
+      updated.
+      If it is a non-mergeable view then it can't be updated.
+    */
+    DBUG_ASSERT(table->merge_underlying_list ||
+                (!table->updatable &&
+                 !(table->table->map & tables_for_update)));
+
+    for (TABLE_LIST *tbl= table->merge_underlying_list; tbl;
+         tbl= tbl->next_local)
+    {
+      if (multi_update_check_table_access(thd, tbl, tables_for_update,
+                                          &updated))
+      {
+        tbl->hide_view_error(thd);
+        return true;
+      }
+    }
+    if (check_table_access(thd, updated ? UPDATE_ACL: SELECT_ACL, table,
+                           FALSE, 1, FALSE))
+      return true;
+    *updated_arg|= updated;
+    /* We only need SELECT privilege for columns in the values list. */
+    table->grant.want_privilege= SELECT_ACL & ~table->grant.privilege;
+  }
+  else
+  {
+    /* Must be a base or derived table. */
+    const bool updated= table->table->map & tables_for_update;
+    if (check_table_access(thd, updated ? UPDATE_ACL : SELECT_ACL, table,
+                           FALSE, 1, FALSE))
+      return true;
+    *updated_arg|= updated;
+    /* We only need SELECT privilege for columns in the values list. */
+    if (!table->derived)
+    {
+      table->grant.want_privilege= SELECT_ACL & ~table->grant.privilege;
+      table->table->grant.want_privilege= (SELECT_ACL &
+                                           ~table->table->grant.privilege);
+    }
+  }
+  return false;
+}
+
 
 /*
   make update specific preparation and checks after opening tables
@@ -1351,19 +1432,17 @@ int mysql_multi_update_prepare(THD *thd)
         tl->table->reginfo.lock_type= tl->lock_type;
     }
   }
+
+  /*
+    Check access privileges for tables being updated or read.
+    Note that unlike in the above loop we need to iterate here not only
+    through all leaf tables but also through all view hierarchy.
+  */
   for (tl= table_list; tl; tl= tl->next_local)
   {
-    /* Check access privileges for table */
-    if (!tl->is_derived())
-    {
-      uint want_privilege= tl->updating ? UPDATE_ACL : SELECT_ACL;
-      if (check_access(thd, want_privilege, tl->db,
-                       &tl->grant.privilege,
-                       &tl->grant.m_internal,
-                       0, 0) ||
-          check_grant(thd, want_privilege, tl, FALSE, 1, FALSE))
-        DBUG_RETURN(TRUE);
-    }
+    bool not_used= false;
+    if (multi_update_check_table_access(thd, tl, tables_for_update, &not_used))
+      DBUG_RETURN(TRUE);
   }
 
   /* check single table update for view compound from several tables */