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;

+insert into t1 values(2);

+select * from t1;

+insert into t1(a) values(3);

+select * from t1;

+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;

+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;

+insert into t1 values(2);

+select * from t1;

+insert into t1(a) values(3);

+select * from t1;

+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;

+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