MDEV-28455: CREATE TEMPORARY TABLES privilege is insufficient for SHOW COLUMNS

=========== Problem =============
- `show columns` is not working for temporary tables, even though there
is enough privilege `create temporary tables`.
=========== Solution =============
- Append `TMP_TABLE_ACLS` privilege when running `show columns` for temp
tables.
- Additionally `check_access()` for database only once, not for each
field
=========== Additionally =============
- Update comments for function `check_table_access` arguments

Reviewed by: <vicentiu@mariadb.org>

6 files changed, 122 insertions, 6 deletions

diff --git a/mysql-test/main/grant2.result b/mysql-test/main/grant2.result
index 5bd44d0bd22..de192ecc28e 100644
--- a/mysql-test/main/grant2.result
+++ b/mysql-test/main/grant2.result
@@ -723,6 +723,7 @@ a
 #
 SHOW COLUMNS FROM t1;
 Field	Type	Null	Key	Default	Extra
+a	int(11)	YES		NULL	
 SHOW KEYS FROM t3;
 Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
 t3	0	PRIMARY	1	a	A	0	NULL	NULL		BTREE		
diff --git a/mysql-test/main/grant5.result b/mysql-test/main/grant5.result
index 7868effeb2c..908a05aadf1 100644
--- a/mysql-test/main/grant5.result
+++ b/mysql-test/main/grant5.result
@@ -242,4 +242,58 @@ connection default;
 disconnect con1;
 drop database db1;
 drop user foo@localhost;
+#
+# MDEV-28455: CREATE TEMPORARY TABLES privilege
+#            is insufficient for SHOW COLUMNS
+#
+create database db;
+create user foo@localhost;
+create user bar@localhost;
+create user buz@localhost;
+grant create temporary tables on db.* to foo@localhost;
+grant create temporary tables on db.* to bar@localhost;
+connect  con1,localhost,foo,,db;
+create temporary table tmp (a int, key(a));
+show tables;
+Tables_in_db
+show full tables;
+Tables_in_db	Table_type
+show table status;
+Name	Engine	Version	Row_format	Rows	Avg_row_length	Data_length	Max_data_length	Index_length	Data_free	Auto_increment	Create_time	Update_time	Check_time	Collation	Checksum	Create_options	Comment	Max_index_length	Temporary
+show index in tmp;
+Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
+tmp	1	a	1	a	A	NULL	NULL	NULL	YES	BTREE		
+show columns in tmp;
+Field	Type	Null	Key	Default	Extra
+a	int(11)	YES	MUL	NULL	
+show full columns in tmp;
+Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
+a	int(11)	NULL	YES	MUL	NULL		select,insert,update,references	
+# we don't expect to show temporary tables in information_schema.columns
+select * from information_schema.columns where table_schema='db';
+TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	ORDINAL_POSITION	COLUMN_DEFAULT	IS_NULLABLE	DATA_TYPE	CHARACTER_MAXIMUM_LENGTH	CHARACTER_OCTET_LENGTH	NUMERIC_PRECISION	NUMERIC_SCALE	DATETIME_PRECISION	CHARACTER_SET_NAME	COLLATION_NAME	COLUMN_TYPE	COLUMN_KEY	EXTRA	PRIVILEGES	COLUMN_COMMENT	IS_GENERATED	GENERATION_EXPRESSION
+disconnect con1;
+connect  con1,localhost,bar,,db;
+show full columns in tmp;
+ERROR 42000: SELECT command denied to user 'bar'@'localhost' for table `db`.`tmp`
+disconnect con1;
+connection default;
+grant select on db.* to bar@localhost;
+connect  con1,localhost,bar,,db;
+show grants for current_user;
+Grants for bar@localhost
+GRANT USAGE ON *.* TO `bar`@`localhost`
+GRANT SELECT, CREATE TEMPORARY TABLES ON `db`.* TO `bar`@`localhost`
+show full columns in tmp;
+ERROR 42S02: Table 'db.tmp' doesn't exist
+disconnect con1;
+connect  con1,localhost,buz,,;
+show columns in db.tmp;
+ERROR 42000: SELECT command denied to user 'buz'@'localhost' for table `db`.`tmp`
+disconnect con1;
+connection default;
+drop database db;
+drop user foo@localhost;
+drop user bar@localhost;
+drop user buz@localhost;
 # End of 10.3 tests
diff --git a/mysql-test/main/grant5.test b/mysql-test/main/grant5.test
index dc61c10646b..35df5a6ec03 100644
--- a/mysql-test/main/grant5.test
+++ b/mysql-test/main/grant5.test
@@ -206,5 +206,57 @@ show create view t_v;
 --disconnect con1
 drop database db1;
 drop user foo@localhost;
+--echo #
+--echo # MDEV-28455: CREATE TEMPORARY TABLES privilege
+--echo #            is insufficient for SHOW COLUMNS
+--echo #
+
+create database db;
+create user foo@localhost;
+create user bar@localhost;
+create user buz@localhost;
+grant create temporary tables on db.* to foo@localhost;
+grant create temporary tables on db.* to bar@localhost;
+
+--connect (con1,localhost,foo,,db)
+create temporary table tmp (a int, key(a));
+show tables;
+show full tables;
+show table status;
+show index in tmp;
+show columns in tmp;
+show full columns in tmp;
+--echo # we don't expect to show temporary tables in information_schema.columns
+select * from information_schema.columns where table_schema='db';
+--disconnect con1
+
+--connect (con1,localhost,bar,,db)
+# User doesn't have `select` privilege on table
+--error ER_TABLEACCESS_DENIED_ERROR
+show full columns in tmp;
+
+--disconnect con1
+
+--connection default
+grant select on db.* to bar@localhost;
+
+--connect (con1,localhost,bar,,db)
+# Table doesn't exist for this session
+show grants for current_user;
+--error ER_NO_SUCH_TABLE
+show full columns in tmp;
+--disconnect con1
+
+--connect (con1,localhost,buz,,)
+--error ER_TABLEACCESS_DENIED_ERROR
+show columns in db.tmp;
+--disconnect con1
+
+--connection default
+# Cleanup
+drop database db;
+drop user foo@localhost;
+drop user bar@localhost;
+drop user buz@localhost;
 
 --echo # End of 10.3 tests
diff --git a/sql/sql_acl.h b/sql/sql_acl.h
index dc8a085c96c..3d415051f28 100644
--- a/sql/sql_acl.h
+++ b/sql/sql_acl.h
@@ -103,7 +103,7 @@
 */
 #define TMP_TABLE_ACLS \
 (SELECT_ACL | INSERT_ACL | UPDATE_ACL | DELETE_ACL | CREATE_ACL | DROP_ACL | \
- INDEX_ACL | ALTER_ACL)
+ INDEX_ACL | ALTER_ACL | REFERENCES_ACL)
 
 /*
   Defines to change the above bits to how things are stored in tables
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index ca82c36d568..385360168a1 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -6892,13 +6892,13 @@ static bool check_show_access(THD *thd, TABLE_LIST *table)
   @brief Check if the requested privileges exists in either User-, Host- or
     Db-tables.
   @param thd          Thread context
-  @param want_access  Privileges requested
+  @param requirements Privileges requested
   @param tables       List of tables to be compared against
-  @param no_errors    Don't report error to the client (using my_error() call).
   @param any_combination_of_privileges_will_do TRUE if any privileges on any
     column combination is enough.
   @param number       Only the first 'number' tables in the linked list are
                       relevant.
+  @param no_errors    Don't report error to the client (using my_error() call).
 
   The suppled table list contains cached privileges. This functions calls the
   help functions check_access and check_grant to verify the first three steps
@@ -6925,7 +6925,7 @@ static bool check_show_access(THD *thd, TABLE_LIST *table)
 
 bool
 check_table_access(THD *thd, ulong requirements,TABLE_LIST *tables,
-		   bool any_combination_of_privileges_will_do,
+                   bool any_combination_of_privileges_will_do,
                    uint number, bool no_errors)
 {
   TABLE_LIST *org_tables= tables;
diff --git a/sql/sql_show.cc b/sql/sql_show.cc
index bae712a407d..17437e683f4 100644
--- a/sql/sql_show.cc
+++ b/sql/sql_show.cc
@@ -6029,6 +6029,15 @@ static int get_schema_column_record(THD *thd, TABLE_LIST *tables,
   show_table->use_all_columns();               // Required for default
   restore_record(show_table, s->default_values);
 
+#ifndef NO_EMBEDDED_ACCESS_CHECKS
+  check_access(thd, SELECT_ACL, db_name->str,
+               &tables->grant.privilege, 0, 0, MY_TEST(tables->schema_table));
+  if (is_temporary_table(tables))
+  {
+    tables->grant.privilege|= TMP_TABLE_ACLS;
+  }
+#endif
+
   for (; (field= *ptr) ; ptr++)
   {
     if(field->invisible > INVISIBLE_USER)
@@ -6049,13 +6058,13 @@ static int get_schema_column_record(THD *thd, TABLE_LIST *tables,
 
 #ifndef NO_EMBEDDED_ACCESS_CHECKS
     uint col_access;
-    check_access(thd,SELECT_ACL, db_name->str,
-                 &tables->grant.privilege, 0, 0, MY_TEST(tables->schema_table));
     col_access= get_column_grant(thd, &tables->grant,
                                  db_name->str, table_name->str,
                                  field->field_name.str) & COL_ACLS;
+
     if (!tables->schema_table && !col_access)
       continue;
+
     char *end= tmp;
     for (uint bitnr=0; col_access ; col_access>>=1,bitnr++)
     {