[Commits] MDEV-14853 Grant does not work correctly when table contains...

15 May 2018

commit 3055012b0b0cd22b14769096677ed77fc2e0c44b
Author: sachin <sachin.setiya@maridb.com>
Date:   Tue May 15 14:01:24 2018 +0530

    MDEV-14853 Grant does not work correctly when table contains...
    SYSTEM_INVISIBLE or COMPLETELY_INVISIBLE

    This commit does multiple things to solve this mdev
    1st add field into the parameter of check_column_grant_in_table_ref, so
that
    we can find out field invisibility.
    2nd If field->invisible >= INVISIBLE_SYSTEM skip access check and simple
    grant access.

diff --git a/mysql-test/main/invisible_field_grant_completely.result
b/mysql-test/main/invisible_field_grant_completely.result
new file mode 100644
index 00000000000..208a9f24ddf
--- /dev/null
+++ b/mysql-test/main/invisible_field_grant_completely.result
@@ -0,0 +1,68 @@
+set @old_debug= @@debug_dbug;
+create user user_1;
+show grants for user_1;
+Grants for user_1@%
+GRANT USAGE ON *.* TO 'user_1'@'%'
+# create user
+create database d;
+use d;
+
+#Completely Invisible
+set debug_dbug= "+d,test_completely_invisible";
+create table t1(a int);
+insert into t1 values(1);
+select a,invisible from t1;
+a invisible
+1 9
+set debug_dbug=@old_debug;
+grant insert(a) on t1 to user_1;
+grant update(a) on t1 to user_1;
+grant select(a) on t1 to user_1;
+grant delete on t1 to user_1;
+connect  con1, localhost, user_1,,test;
+connection con1;
+select user();
+user()
+user_1@localhost
+use d;
+select * from t1;
+a
+1
+insert into t1 values(2);
+select * from t1;
+a
+1
+2
+insert into t1(a) values(3);
+select * from t1;
+a
+1
+2
+3
+select invisible,a from t1;
+ERROR 42S22: Unknown column 'invisible' in 'field list'
+delete from t1 where a =1;
+update t1 set a=1 where a=3;
+select * from t1;
+a
+2
+1
+connection default;
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM user_1;
+connection con1;
+select * from t1;
+ERROR 42000: SELECT command denied to user 'user_1'@'localhost' for table
't1'
+select invisible from t1;
+ERROR 42000: SELECT command denied to user 'user_1'@'localhost' for table
't1'
+disconnect con1;
+
+#Final Cleanup
+connection default;
+set debug_dbug= "+d,test_completely_invisible";
+select a,invisible from t1;
+a invisible
+2 9
+1 9
+drop user user_1;
+drop database d;
+set @old_debug= @@debug_dbug;
diff --git a/mysql-test/main/invisible_field_grant_completely.test
b/mysql-test/main/invisible_field_grant_completely.test
new file mode 100644
index 00000000000..3035906c61f
--- /dev/null
+++ b/mysql-test/main/invisible_field_grant_completely.test
@@ -0,0 +1,54 @@
+--source include/have_debug.inc
+##TEST for invisible coloumn level 3
+set @old_debug= @@debug_dbug;
+create user user_1;
+show grants for user_1;
+--echo # create user
+create database d;
+use d;
+
+--echo
+--echo #Completely Invisible
+set debug_dbug= "+d,test_completely_invisible";
+create table t1(a int);
+insert into t1 values(1);
+select a,invisible from t1;
+set debug_dbug=@old_debug;
+grant insert(a) on t1 to user_1;
+grant update(a) on t1 to user_1;
+grant select(a) on t1 to user_1;
+grant delete on t1 to user_1;
+connect (con1, localhost, user_1,,test);
+connection con1;
+select user();
+use d;
+select * from t1;
+insert into t1 values(2);
+select * from t1;
+insert into t1(a) values(3);
+select * from t1;
+--error ER_BAD_FIELD_ERROR
+select invisible,a from t1;
+delete from t1 where a =1;
+update t1 set a=1 where a=3;
+select * from t1;
+
+connection default;
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM user_1;
+
+connection con1;
+--error ER_TABLEACCESS_DENIED_ERROR
+select * from t1;
+--error ER_TABLEACCESS_DENIED_ERROR
+select invisible from t1;
+
+disconnect con1;
+--source include/wait_until_disconnected.inc
+--echo
+--echo #Final Cleanup
+connection default;
+set debug_dbug= "+d,test_completely_invisible";
+select a,invisible from t1;
+drop user user_1;
+drop database d;
+set @old_debug= @@debug_dbug;
diff --git a/mysql-test/main/invisible_field_grant_system.result
b/mysql-test/main/invisible_field_grant_system.result
new file mode 100644
index 00000000000..7290363b7eb
--- /dev/null
+++ b/mysql-test/main/invisible_field_grant_system.result
@@ -0,0 +1,62 @@
+create user user_1;
+show grants for user_1;
+Grants for user_1@%
+GRANT USAGE ON *.* TO 'user_1'@'%'
+# create user
+create database d;
+use d;
+
+#System_Invisible
+create table t1(a int) with system versioning;
+insert into t1 values(1);
+select a,row_start from t1;
+a row_start
+1 2018-05-15 14:55:47.588665
+grant insert(a) on t1 to user_1;
+grant update(a) on t1 to user_1;
+grant select(a) on t1 to user_1;
+grant delete on t1 to user_1;
+connect  con1, localhost, user_1,,test;
+connection con1;
+select user();
+user()
+user_1@localhost
+use d;
+select * from t1;
+a
+1
+insert into t1 values(2);
+select * from t1;
+a
+1
+2
+insert into t1(a) values(3);
+select * from t1;
+a
+1
+2
+3
+select row_start,a from t1;
+row_start a
+2018-05-15 14:55:47.588665 1
+2018-05-15 14:55:47.595771 2
+2018-05-15 14:55:47.596832 3
+delete from t1 where a =1;
+update t1 set a=1 where a=3;
+select * from t1;
+a
+2
+1
+connection default;
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM user_1;
+connection con1;
+select * from t1;
+ERROR 42000: SELECT command denied to user 'user_1'@'localhost' for table
't1'
+select row_start from t1;
+ERROR 42000: SELECT command denied to user 'user_1'@'localhost' for table
't1'
+disconnect con1;
+
+#Cleanup
+connection default;
+drop user user_1;
+drop database d;
diff --git a/mysql-test/main/invisible_field_grant_system.test
b/mysql-test/main/invisible_field_grant_system.test
new file mode 100644
index 00000000000..3a66b4a8728
--- /dev/null
+++ b/mysql-test/main/invisible_field_grant_system.test
@@ -0,0 +1,47 @@
+##TEST for invisible coloumn level 2
+create user user_1;
+show grants for user_1;
+--echo # create user
+create database d;
+use d;
+
+--echo
+--echo #System_Invisible
+create table t1(a int) with system versioning;
+insert into t1 values(1);
+select a,row_start from t1;
+grant insert(a) on t1 to user_1;
+grant update(a) on t1 to user_1;
+grant select(a) on t1 to user_1;
+grant delete on t1 to user_1;
+connect (con1, localhost, user_1,,test);
+connection con1;
+select user();
+use d;
+select * from t1;
+insert into t1 values(2);
+select * from t1;
+insert into t1(a) values(3);
+select * from t1;
+select row_start,a from t1;
+delete from t1 where a =1;
+update t1 set a=1 where a=3;
+select * from t1;
+connection default;
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM user_1;
+
+connection con1;
+--error ER_TABLEACCESS_DENIED_ERROR
+select * from t1;
+--error ER_TABLEACCESS_DENIED_ERROR
+select row_start from t1;
+
+disconnect con1;
+--source include/wait_until_disconnected.inc
+
+--echo
+--echo #Cleanup
+--source include/wait_until_disconnected.inc
+connection default;
+drop user user_1;
+drop database d;
diff --git a/sql/sp_rcontext.cc b/sql/sp_rcontext.cc
index 65c4e801cf1..7c8c676d337 100644
--- a/sql/sp_rcontext.cc
+++ b/sql/sp_rcontext.cc
@@ -196,11 +196,12 @@ bool sp_rcontext::init_var_table(THD *thd,
 */
 static inline bool
 check_column_grant_for_type_ref(THD *thd, TABLE_LIST *table_list,
-                                const char *str, size_t length)
+                                const char *str, size_t length,
+                                Field *fld)
 {
 #ifndef NO_EMBEDDED_ACCESS_CHECKS
   table_list->table->grant.want_privilege= SELECT_ACL;
-  return check_column_grant_in_table_ref(thd, table_list, str, length);
+  return check_column_grant_in_table_ref(thd, table_list, str, length,
fld);
 #else
   return false;
 #endif
@@ -238,7 +239,7 @@ bool Qualified_column_ident::resolve_type_ref(THD *thd,
Column_definition *def)
     {
       if (!(rc= check_column_grant_for_type_ref(thd, table_list,
                                                 m_column.str,
-                                                m_column.length)))
+                                                m_column.length, src)))
       {
         *def= Column_definition(thd, src, NULL/*No defaults,no
constraints*/);
         def->flags&= (uint) ~NOT_NULL_FLAG;
@@ -302,7 +303,7 @@ bool Table_ident::resolve_table_rowtype_ref(THD *thd,
       LEX_CSTRING tmp= src[0]->field_name;
       Spvar_definition *def;
       if ((rc= check_column_grant_for_type_ref(thd, table_list,
-                                               tmp.str, tmp.length)) ||
+                                               tmp.str,
tmp.length,src[0])) ||
           (rc= !(src[0]->field_name.str= thd->strmake(tmp.str,
tmp.length))) ||
           (rc= !(def= new (thd->mem_root) Spvar_definition(thd, *src))))
         break;
diff --git a/sql/sql_acl.cc b/sql/sql_acl.cc
index adad8622bc3..aec15d38847 100644
--- a/sql/sql_acl.cc
+++ b/sql/sql_acl.cc
@@ -7782,6 +7782,8 @@ bool check_grant_column(THD *thd, GRANT_INFO *grant,
     table_ref        table reference where to check the field
     name             name of field to check
     length           length of name
+    fld              use fld object to check invisibility when it is
+                     not 0, not_found_field, view_ref_found

   DESCRIPTION
     Check the access rights to a column depending on the type of table
@@ -7796,13 +7798,17 @@ bool check_grant_column(THD *thd, GRANT_INFO *grant,
 */

 bool check_column_grant_in_table_ref(THD *thd, TABLE_LIST * table_ref,
-                                     const char *name, size_t length)
+                                     const char *name, size_t length,
+                                     Field *fld)
 {
   GRANT_INFO *grant;
   const char *db_name;
   const char *table_name;
   Security_context *sctx= table_ref->security_ctx ?
                           table_ref->security_ctx : thd->security_ctx;
+  if (fld && fld != not_found_field && fld != view_ref_found
+          && fld->invisible >= INVISIBLE_SYSTEM)
+      return false;

   if (table_ref->view || table_ref->field_translation)
   {
@@ -7878,6 +7884,9 @@ bool check_grant_all_columns(THD *thd, ulong
want_access_arg,

   for (; !fields->end_of_fields(); fields->next())
   {
+    if (fields->field() &&
+        fields->field()->invisible >= INVISIBLE_SYSTEM)
+      continue;
     LEX_CSTRING *field_name= fields->name();

     if (table_name != fields->get_table_name())
diff --git a/sql/sql_acl.h b/sql/sql_acl.h
index a608ef0dd77..6da7d4d5db4 100644
--- a/sql/sql_acl.h
+++ b/sql/sql_acl.h
@@ -239,7 +239,7 @@ bool check_grant_column (THD *thd, GRANT_INFO *grant,
                          const char *db_name, const char *table_name,
                          const char *name, size_t length, Security_context
*sctx);
 bool check_column_grant_in_table_ref(THD *thd, TABLE_LIST * table_ref,
-                                     const char *name, size_t length);
+                                     const char *name, size_t length,
Field *fld);
 bool check_grant_all_columns(THD *thd, ulong want_access,
                              Field_iterator_table_ref *fields);
 bool check_grant_routine(THD *thd, ulong want_access,
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index b6dab95774a..77e251f8bb1 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -5877,7 +5877,7 @@ find_field_in_table_ref(THD *thd, TABLE_LIST
*table_list,
 #ifndef NO_EMBEDDED_ACCESS_CHECKS
     /* Check if there are sufficient access rights to the found field. */
     if (check_privileges &&
-        check_column_grant_in_table_ref(thd, *actual_table, name, length))
+        check_column_grant_in_table_ref(thd, *actual_table, name, length,
fld))
       fld= WRONG_GRANT;
     else
 #endif
@@ -6054,7 +6054,7 @@ find_field_in_tables(THD *thd, Item_ident *item,
 #ifndef NO_EMBEDDED_ACCESS_CHECKS
       /* Check if there are sufficient access rights to the found field. */
       if (found && check_privileges &&
-          check_column_grant_in_table_ref(thd, table_ref, name, length))
+          check_column_grant_in_table_ref(thd, table_ref, name, length,
found))
         found= WRONG_GRANT;
 #endif
     }


-- 
Regards
Sachin Setiya
Software Engineer at  MariaDB

    