MDEV-28455: CREATE TEMPORARY TABLES privilege is insufficient for SHOW COLUMNSbb-10.3-anel-MDEV-28455-temporary-grant

Problem:
=========
When calling `show columns` or `show index` the function pointer for SCH_COLUMNS or SCH_STATISTICS is called for `CREATE_TMP_ACL` privilege.
However the functor is not called for the `CREATE_ACL` privilege, where the following backtrace is called `check_table_access()->check_show_access()-> check_grant()` and the last function will raise the error in case of insufficient privilege. One need to have `SELECT_ACL` DB privilege as well.

Solution:
==========
Check if there `SELECT_ACL` DB privilege exists in order to show the underlying content.

Reviewed by: <>

5 files changed, 264 insertions, 4 deletions

diff --git a/mysql-test/main/grant2.result b/mysql-test/main/grant2.result
index 022b94d641b..0bc282d3b4d 100644
--- a/mysql-test/main/grant2.result
+++ b/mysql-test/main/grant2.result
@@ -722,10 +722,9 @@ a
 # SHOW COLUMNS/DESCRIBE and SHOW KEYS.
 #
 SHOW COLUMNS FROM t1;
-Field	Type	Null	Key	Default	Extra
+ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 't1'
 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		
+ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 't3'
 #
 # SHOW CREATE TABLE.
 #
diff --git a/mysql-test/main/grant2.test b/mysql-test/main/grant2.test
index 1f7450df6c1..5f583f709e1 100644
--- a/mysql-test/main/grant2.test
+++ b/mysql-test/main/grant2.test
@@ -818,7 +818,10 @@ SELECT * FROM t1 ORDER BY a;
 --echo #
 --echo # SHOW COLUMNS/DESCRIBE and SHOW KEYS.
 --echo #
+# Need SELECT_PRIV
+--error ER_TABLEACCESS_DENIED_ERROR
 SHOW COLUMNS FROM t1;
+--error ER_TABLEACCESS_DENIED_ERROR
 SHOW KEYS FROM t3;
 
 --echo #
diff --git a/mysql-test/main/grant5.result b/mysql-test/main/grant5.result
index 83cd8677089..5a6398d1eee 100644
--- a/mysql-test/main/grant5.result
+++ b/mysql-test/main/grant5.result
@@ -181,4 +181,134 @@ GRANT USAGE ON *.* TO `test-user`@`%`
 SET DEFAULT ROLE `r``o'l"e` FOR `test-user`@`%`
 DROP ROLE `r``o'l"e`;
 DROP USER 'test-user';
+#
+# MDEV-28455: CREATE TEMPORARY TABLES privilege
+#            is insufficient for SHOW COLUMNS
+#
+create database db;
+create user foo@localhost;
+create user bar@localhost;
+create user monitor@localhost;
+grant create temporary tables on db.* to foo@localhost;
+grant create on db.* to bar@localhost;
+grant create temporary tables, select on db.* to monitor@localhost;
+# Check CREATE_ACL
+connect  con1,localhost,bar,,db;
+create temporary table tmpbar (b int, key(b));
+ERROR 42000: Access denied for user 'bar'@'localhost' to database 'db'
+create table bar (b int, key(b));
+show index in bar;
+ERROR 42000: SELECT command denied to user 'bar'@'localhost' for table 'bar'
+show columns in bar;
+ERROR 42000: SELECT command denied to user 'bar'@'localhost' for table 'bar'
+show create table bar;
+Table	Create Table
+bar	CREATE TABLE `bar` (
+  `b` int(11) DEFAULT NULL,
+  KEY `b` (`b`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+# Check CREATE_TMP_ACL
+connect  con2,localhost,foo,,db;
+create temporary table tmp (a int, key(a));
+create table t (t int);
+ERROR 42000: CREATE command denied to user 'foo'@'localhost' for table 't'
+show index in tmp;
+ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 'tmp'
+show columns in tmp;
+ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 'tmp'
+show create table tmp;
+Table	Create Table
+tmp	CREATE TEMPORARY TABLE `tmp` (
+  `a` int(11) DEFAULT NULL,
+  KEY `a` (`a`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+# Check user with SELECT_ACL privilege can see tables
+connect  con3,localhost,monitor,,db;
+show grants;
+Grants for monitor@localhost
+GRANT USAGE ON *.* TO `monitor`@`localhost`
+GRANT SELECT, CREATE TEMPORARY TABLES ON `db`.* TO `monitor`@`localhost`
+show columns in tmp;
+ERROR 42S02: Table 'db.tmp' doesn't exist
+show index in tmp;
+ERROR 42S02: Table 'db.tmp' doesn't exist
+show create table tmp;
+ERROR 42S02: Table 'db.tmp' doesn't exist
+show create table bar;
+Table	Create Table
+bar	CREATE TABLE `bar` (
+  `b` int(11) DEFAULT NULL,
+  KEY `b` (`b`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+show columns in bar;
+Field	Type	Null	Key	Default	Extra
+b	int(11)	YES	MUL	NULL	
+show index in bar;
+Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
+bar	1	b	1	b	A	NULL	NULL	NULL	YES	BTREE		
+create temporary table temp(t int);
+show create table temp;
+Table	Create Table
+temp	CREATE TEMPORARY TABLE `temp` (
+  `t` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+show columns in temp;
+Field	Type	Null	Key	Default	Extra
+t	int(11)	YES		NULL	
+show index in temp;
+Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
+# Give SELECT_ACL to both users
+connection default;
+grant select on db.* to foo@localhost;
+show grants for foo@localhost;
+Grants for foo@localhost
+GRANT USAGE ON *.* TO `foo`@`localhost`
+GRANT SELECT, CREATE TEMPORARY TABLES ON `db`.* TO `foo`@`localhost`
+grant select on db.* to bar@localhost;
+show grants for bar@localhost;
+Grants for bar@localhost
+GRANT USAGE ON *.* TO `bar`@`localhost`
+GRANT SELECT, CREATE ON `db`.* TO `bar`@`localhost`
+connection con2;
+show create table tmp;
+Table	Create Table
+tmp	CREATE TEMPORARY TABLE `tmp` (
+  `a` int(11) DEFAULT NULL,
+  KEY `a` (`a`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+show columns in tmp;
+ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 'tmp'
+show index in tmp;
+ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 'tmp'
+connection con1;
+show create table bar;
+Table	Create Table
+bar	CREATE TABLE `bar` (
+  `b` int(11) DEFAULT NULL,
+  KEY `b` (`b`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+show columns in bar;
+ERROR 42000: SELECT command denied to user 'bar'@'localhost' for table 'bar'
+show index in bar;
+ERROR 42000: SELECT command denied to user 'bar'@'localhost' for table 'bar'
+disconnect con1;
+disconnect con2;
+connect  con1,localhost,bar,,db;
+show create table tmp;
+ERROR 42S02: Table 'db.tmp' doesn't exist
+connect  con2,localhost,foo,,db;
+show columns in bar;
+Field	Type	Null	Key	Default	Extra
+b	int(11)	YES	MUL	NULL	
+show index in bar;
+Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
+bar	1	b	1	b	A	NULL	NULL	NULL	YES	BTREE		
+connection default;
+disconnect con1;
+disconnect con2;
+disconnect con3;
+drop database db;
+drop user foo@localhost;
+drop user bar@localhost;
+drop user monitor@localhost;
 # End of 10.3 tests
diff --git a/mysql-test/main/grant5.test b/mysql-test/main/grant5.test
index bd711640acb..952e01ef341 100644
--- a/mysql-test/main/grant5.test
+++ b/mysql-test/main/grant5.test
@@ -135,4 +135,112 @@ SHOW GRANTS FOR 'test-user';
 DROP ROLE `r``o'l"e`;
 DROP USER 'test-user';
 
+--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 monitor@localhost;
+grant create temporary tables on db.* to foo@localhost;
+grant create on db.* to bar@localhost;
+grant create temporary tables, select on db.* to monitor@localhost;
+
+--echo # Check CREATE_ACL
+--connect (con1,localhost,bar,,db)
+ --error ER_DBACCESS_DENIED_ERROR
+create temporary table tmpbar (b int, key(b));
+create table bar (b int, key(b));
+# No SELECT_ACL
+--error ER_TABLEACCESS_DENIED_ERROR
+show index in bar;
+--error ER_TABLEACCESS_DENIED_ERROR
+show columns in bar;
+show create table bar;
+
+--echo # Check CREATE_TMP_ACL
+--connect (con2,localhost,foo,,db)
+create temporary table tmp (a int, key(a));
+# User cannot create base table with CREATE_TMP_ACL privileges
+--error ER_TABLEACCESS_DENIED_ERROR
+create table t (t int);
+# No SELECT_ACL
+--error ER_TABLEACCESS_DENIED_ERROR
+show index in tmp;
+--error ER_TABLEACCESS_DENIED_ERROR
+show columns in tmp;
+show create table tmp;
+
+--echo # Check user with SELECT_ACL privilege can see tables
+--connect (con3,localhost,monitor,,db)
+show grants;
+# Current connnection cannot see temporary table from other connection (con2)
+--error ER_NO_SUCH_TABLE
+show columns in tmp;
+--error ER_NO_SUCH_TABLE
+show index in tmp;
+--error ER_NO_SUCH_TABLE
+show create table tmp;
+# But can see the base table
+show create table bar;
+show columns in bar;
+show index in bar;
+create temporary table temp(t int);
+show create table temp;
+show columns in temp;
+show index in temp;
+
+--echo # Give SELECT_ACL to both users
+--connection default
+grant select on db.* to foo@localhost;
+show grants for foo@localhost;
+grant select on db.* to bar@localhost;
+show grants for bar@localhost;
+
+# It is expected that even when granting SELECT_ACL `current` session
+# cannot see the result, one needs to disconnect first.
+
+--connection con2
+# Still can see the temporary table footprint
+show create table tmp;
+# But cannot access the internals (SELECT_ACL didn't take place)
+--error ER_TABLEACCESS_DENIED_ERROR
+show columns in tmp;
+--error ER_TABLEACCESS_DENIED_ERROR
+show index in tmp;
+
+--connection con1
+# The same as for temporary table
+show create table bar;
+--error ER_TABLEACCESS_DENIED_ERROR
+show columns in bar;
+--error ER_TABLEACCESS_DENIED_ERROR
+show index in bar;
+
+--disconnect con1
+--disconnect con2
+
+--connect (con1,localhost,bar,,db)
+# There are valid grants but sessions closed, no temporary table
+--error ER_NO_SUCH_TABLE
+show create table tmp;
+
+--connect (con2,localhost,foo,,db)
+# Now we can see the internal meta data
+show columns in bar;
+show index in bar;
+
+# Cleanup
+
+--connection default
+--disconnect con1
+--disconnect con2
+--disconnect con3
+drop database db;
+drop user foo@localhost;
+drop user bar@localhost;
+drop user monitor@localhost;
+
 --echo # End of 10.3 tests
diff --git a/sql/sql_show.cc b/sql/sql_show.cc
index 1316dcd9c94..a2c39c47c1e 100644
--- a/sql/sql_show.cc
+++ b/sql/sql_show.cc
@@ -6054,8 +6054,16 @@ 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,
+    check_access(thd, SELECT_ACL, db_name->str,
                  &tables->grant.privilege, 0, 0, MY_TEST(tables->schema_table));
+    // We need to have at least SELECT_ACL to access data, otherwise raise the error
+    TABLE_LIST table_acl_check;
+    bzero((char*) &table_acl_check, sizeof(table_acl_check));
+    table_acl_check.db= *db_name;
+    table_acl_check.table_name= *table_name;
+    table_acl_check.grant.privilege= tables->grant.privilege;
+    check_grant(thd, SELECT_ACL, &table_acl_check, TRUE, 1, FALSE);
+
     col_access= get_column_grant(thd, &tables->grant,
                                  db_name->str, table_name->str,
                                  field->field_name.str) & COL_ACLS;
@@ -6716,6 +6724,18 @@ static int get_schema_stat_record(THD *thd, TABLE_LIST *tables,
   {
     TABLE *show_table= tables->table;
     KEY *key_info=show_table->s->key_info;
+#ifndef NO_EMBEDDED_ACCESS_CHECKS
+    check_access(thd, SELECT_ACL, db_name->str,
+                 &tables->grant.privilege, 0, 0, MY_TEST(tables->schema_table));
+    // We need to have at least SELECT_ACL to access data, otherwise raise the error
+    TABLE_LIST table_acl_check;
+    bzero((char*) &table_acl_check, sizeof(table_acl_check));
+    table_acl_check.db= *db_name;
+    table_acl_check.table_name= *table_name;
+    table_acl_check.table_name= *table_name;
+    table_acl_check.grant.privilege= tables->grant.privilege;
+    check_grant(thd, SELECT_ACL, &table_acl_check, TRUE, 1, FALSE);
+#endif
     if (show_table->file)
     {
       (void) read_statistics_for_tables(thd, tables);