support of view underlying tables and SP functions security check added (BUG#9505) (WL#2787)

mysql-test/r/information_schema.result:
  error message changed
mysql-test/r/sp.result:
  error message changed
mysql-test/r/sql_mode.result:
  fixed test suite
mysql-test/r/view.result:
  error message changed
mysql-test/r/view_grant.result:
  test of underlying view tables check
mysql-test/t/sql_mode.test:
  fixed test suite
mysql-test/t/view_grant.test:
  test of underlying view tables check
sql/item.cc:
  check of underlying tables privilege added
sql/item.h:
  Name the resolution context points to the security  context of view (if item belong to the view)
sql/item_func.cc:
  a view error hiding for execution of prepared function belonged to a view
  fixed checking privileges if stored functions belonds to some view
sql/mysql_priv.h:
  refult of derived table processing functions changed to bool
  Security_context added as an argument to find_field_in_table()
sql/share/errmsg.txt:
  error message fixed
sql/sql_acl.cc:
  Storing requested privileges of tables added
  View underlying tables privilege check added
sql/sql_base.cc:
  View underlying tables privilege check added
sql/sql_cache.cc:
  Code cleunup: we should not register underlying tables of view second time
sql/sql_delete.cc:
  ancestor -> merge_underlying_list renaming
sql/sql_derived.cc:
  refult of derived table processing functions changed to bool
  do not give SELECT_ACL for TEMPTABLE views
sql/sql_lex.h:
  The comment added
sql/sql_parse.cc:
  registration of requested privileges added
sql/sql_prepare.cc:
  registration of requested privileges added
sql/sql_update.cc:
  manipulation of requested privileges for underlying tables made the same as for table which we are updating
sql/sql_view.cc:
  underlying tables of view security check support added
sql/table.cc:
  renaming and fixing view preparation methods, methods for checking underlyoing tables security context added
sql/table.h:
  storege for reuested privileges added

1 files changed, 213 insertions, 0 deletions

diff --git a/mysql-test/t/view_grant.test b/mysql-test/t/view_grant.test
index ea93345e894..760f0e1550f 100644
--- a/mysql-test/t/view_grant.test
+++ b/mysql-test/t/view_grant.test
@@ -401,8 +401,221 @@ grant all privileges on mysqltest.* to mysqltest_1@localhost;
 connection user1;
 use mysqltest;
 create view v1 as select * from t1;
+use test;
 
 connection root;
 revoke all privileges on mysqltest.* from mysqltest_1@localhost;
 drop database mysqltest;
 
+#
+# view definer grants revoking
+#
+connection root;
+--disable_warnings
+create database mysqltest;
+--enable_warnings
+
+create table mysqltest.t1 (a int, b int);
+
+grant select on mysqltest.t1 to mysqltest_1@localhost;
+grant create view,select on test.* to mysqltest_1@localhost;
+
+connection user1;
+
+create view v1 as select * from mysqltest.t1;
+
+connection root;
+# check view definer information
+show create view v1;
+revoke select on mysqltest.t1 from mysqltest_1@localhost;
+-- error ER_VIEW_INVALID
+select * from v1;
+grant select on mysqltest.t1 to mysqltest_1@localhost;
+select * from v1;
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost;
+drop view v1;
+drop database mysqltest;
+
+#
+# rights on execution of view underlying functiond (BUG#9505)
+#
+connection root;
+--disable_warnings
+create database mysqltest;
+--enable_warnings
+
+use mysqltest;
+create table t1 (a int);
+insert into t1 values (1);
+create table t2 (s1 int);
+--disable_warnings
+drop function if exists f2;
+--enable_warnings
+delimiter //;
+create function f2 () returns int begin declare v int; select s1 from t2
+into v; return v; end//
+delimiter ;//
+create algorithm=TEMPTABLE view v1 as select f2() from t1;
+create algorithm=MERGE view v2 as select f2() from t1;
+create algorithm=TEMPTABLE SQL SECURITY INVOKER view v3 as select f2() from t1;
+create algorithm=MERGE SQL SECURITY INVOKER view v4 as select f2() from t1;
+create SQL SECURITY INVOKER view v5 as select * from v4;
+grant select on v1 to mysqltest_1@localhost;
+grant select on v2 to mysqltest_1@localhost;
+grant select on v3 to mysqltest_1@localhost;
+grant select on v4 to mysqltest_1@localhost;
+grant select on v5 to mysqltest_1@localhost;
+
+connection user1;
+use mysqltest;
+select * from v1;
+select * from v2;
+-- error ER_VIEW_INVALID
+select * from v3;
+-- error ER_VIEW_INVALID
+select * from v4;
+-- error ER_VIEW_INVALID
+select * from v5;
+use test;
+
+connection root;
+drop view v1, v2, v3, v4, v5;
+drop function f2;
+drop table t1, t2;
+use test;
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost;
+drop database mysqltest;
+
+#
+# revertion of previous test, definer of view lost his/her rights to execute
+# function
+#
+
+connection root;
+--disable_warnings
+create database mysqltest;
+--enable_warnings
+
+use mysqltest;
+create table t1 (a int);
+insert into t1 values (1);
+create table t2 (s1 int);
+--disable_warnings
+drop function if exists f2;
+--enable_warnings
+delimiter //;
+create function f2 () returns int begin declare v int; select s1 from t2
+into v; return v; end//
+delimiter ;//
+grant select on t1 to mysqltest_1@localhost;
+grant execute on function f2 to mysqltest_1@localhost;
+grant create view on mysqltest.* to mysqltest_1@localhost;
+
+connection user1;
+use mysqltest;
+create algorithm=TEMPTABLE view v1 as select f2() from t1;
+create algorithm=MERGE view v2 as select f2() from t1;
+create algorithm=TEMPTABLE SQL SECURITY INVOKER view v3 as select f2() from t1;
+create algorithm=MERGE SQL SECURITY INVOKER view v4 as select f2() from t1;
+use test;
+
+connection root;
+create view v5 as select * from v1;
+revoke execute on function f2 from mysqltest_1@localhost;
+-- error ER_VIEW_INVALID
+select * from v1;
+-- error ER_VIEW_INVALID
+select * from v2;
+select * from v3;
+select * from v4;
+-- error ER_VIEW_INVALID
+select * from v5;
+
+drop view v1, v2, v3, v4, v5;
+drop function f2;
+drop table t1, t2;
+use test;
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost;
+drop database mysqltest;
+
+#
+# definer/invoker rights for columns
+#
+connection root;
+--disable_warnings
+create database mysqltest;
+--enable_warnings
+
+use mysqltest;
+create table t1 (a int);
+create table v1 (a int);
+insert into t1 values (1);
+grant select on t1 to mysqltest_1@localhost;
+grant select on v1 to mysqltest_1@localhost;
+grant create view on mysqltest.* to mysqltest_1@localhost;
+drop table v1;
+
+connection user1;
+use mysqltest;
+create algorithm=TEMPTABLE view v1 as select *, a as b from t1;
+create algorithm=MERGE view v2 as select *, a as b from t1;
+create algorithm=TEMPTABLE SQL SECURITY INVOKER view v3 as select *, a as b from t1;
+create algorithm=MERGE SQL SECURITY INVOKER view v4 as select *, a as b from t1;
+create view v5 as select * from v1;
+use test;
+
+connection root;
+revoke select on t1 from mysqltest_1@localhost;
+-- error ER_VIEW_INVALID
+select * from v1;
+-- error ER_VIEW_INVALID
+select * from v2;
+select * from v3;
+select * from v4;
+-- error ER_VIEW_INVALID
+select * from v5;
+
+#drop view v1, v2, v3, v4, v5;
+drop table t1;
+use test;
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost;
+drop database mysqltest;
+
+
+connection root;
+--disable_warnings
+create database mysqltest;
+--enable_warnings
+
+use mysqltest;
+create table t1 (a int);
+insert into t1 values (1);
+create algorithm=TEMPTABLE view v1 as select *, a as b from t1;
+create algorithm=MERGE view v2 as select *, a as b from t1;
+create algorithm=TEMPTABLE SQL SECURITY INVOKER view v3 as select *, a as b from t1;
+create algorithm=MERGE SQL SECURITY INVOKER view v4 as select *, a as b from t1;
+create SQL SECURITY INVOKER view v5 as select * from v4;
+grant select on v1 to mysqltest_1@localhost;
+grant select on v2 to mysqltest_1@localhost;
+grant select on v3 to mysqltest_1@localhost;
+grant select on v4 to mysqltest_1@localhost;
+grant select on v5 to mysqltest_1@localhost;
+
+connection user1;
+use mysqltest;
+select * from v1;
+select * from v2;
+-- error ER_VIEW_INVALID
+select * from v3;
+-- error ER_VIEW_INVALID
+select * from v4;
+-- error ER_VIEW_INVALID
+select * from v5;
+use test;
+
+connection root;
+drop view v1, v2, v3, v4, v5;
+drop table t1;
+use test;
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost;
+drop database mysqltest;