Improve test coverage for show commands with denies

2 files changed, 96 insertions, 4 deletions

diff --git a/mysql-test/suite/deny/show_generic.result b/mysql-test/suite/deny/show_generic.result
index 4c699a2874a..d3d481d6006 100644
--- a/mysql-test/suite/deny/show_generic.result
+++ b/mysql-test/suite/deny/show_generic.result
@@ -96,8 +96,8 @@ drop database some_db;
 #
 create database some_db;
 create user foo;
-create table some_db.t1 (a int);
-create table some_db.t2 (a int);
+create table some_db.t1 (a int, b int);
+create table some_db.t2 (a int, b int);
 grant select on *.* to foo;
 deny select on some_db.t1 to foo;
 deny insert on some_db.t2 to foo;
@@ -114,6 +114,12 @@ ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't1'
 show columns from some_db.t2;
 Field	Type	Null	Key	Default	Extra
 a	int(11)	YES		NULL	
+b	int(11)	YES		NULL	
+select table_name, column_name, privileges from information_schema.columns
+where table_schema like 'some_db' order by table_name, column_name;
+table_name	column_name	privileges
+t2	a	select
+t2	b	select
 disconnect con1;
 connection default;
 grant insert on some_db.* to foo;
@@ -122,22 +128,69 @@ show tables from some_db;
 Tables_in_some_db
 t1
 t2
+#
+# See MDEV-28783, this should not error out when global/db grants exist
+# (except for SELECT priv).
+#
 show columns from some_db.t1;
 ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't1'
 show columns from some_db.t2;
 Field	Type	Null	Key	Default	Extra
 a	int(11)	YES		NULL	
+b	int(11)	YES		NULL	
+select table_name, column_name, privileges from information_schema.columns
+where table_schema like 'some_db' order by table_name, column_name;
+table_name	column_name	privileges
+t1	a	insert
+t1	b	insert
+t2	a	select
+t2	b	select
 disconnect con1;
 connection default;
 deny insert on some_db.t1 to foo;
 deny select on some_db.t2 to foo;
 connect  con1,localhost,foo,,;
+#
+# some_db should still be visible, but it should show up as empty.
+#
+show databases;
+Database
+information_schema
+mtr
+mysql
+performance_schema
+some_db
+sys
+test
 show tables from some_db;
 Tables_in_some_db
 show columns from some_db.t1;
 ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't1'
 show columns from some_db.t2;
 ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't2'
+select table_name, column_name, privileges from information_schema.columns
+where table_schema like 'some_db' order by table_name, column_name;
+table_name	column_name	privileges
+disconnect con1;
+connection default;
+grant select(a) on some_db.t1 to foo;
+grant update(a) on some_db.t1 to foo;
+connect  con1,localhost,foo,,;
+#
+# Update privilege on the column is not masked, only see a column.
+#
+show tables from some_db;
+Tables_in_some_db
+t1
+show columns from some_db.t1;
+Field	Type	Null	Key	Default	Extra
+a	int(11)	YES		NULL	
+show columns from some_db.t2;
+ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't2'
+select table_name, column_name, privileges from information_schema.columns
+where table_schema like 'some_db' order by table_name, column_name;
+table_name	column_name	privileges
+t1	a	update
 disconnect con1;
 connection default;
 drop user foo;
diff --git a/mysql-test/suite/deny/show_generic.test b/mysql-test/suite/deny/show_generic.test
index 6cdc12f97f1..20d7d8c9090 100644
--- a/mysql-test/suite/deny/show_generic.test
+++ b/mysql-test/suite/deny/show_generic.test
@@ -106,8 +106,8 @@ drop database some_db;
 --echo #
 create database some_db;
 create user foo;
-create table some_db.t1 (a int);
-create table some_db.t2 (a int);
+create table some_db.t1 (a int, b int);
+create table some_db.t2 (a int, b int);
 
 grant select on *.* to foo;
 deny select on some_db.t1 to foo;
@@ -122,6 +122,10 @@ show tables from some_db;
 --error ER_TABLEACCESS_DENIED_ERROR
 show columns from some_db.t1;
 show columns from some_db.t2;
+
+select table_name, column_name, privileges from information_schema.columns
+where table_schema like 'some_db' order by table_name, column_name;
+
 disconnect con1;
 
 connection default;
@@ -129,9 +133,17 @@ grant insert on some_db.* to foo;
 
 --connect (con1,localhost,foo,,)
 show tables from some_db;
+--echo #
+--echo # See MDEV-28783, this should not error out when global/db grants exist
+--echo # (except for SELECT priv).
+--echo #
 --error ER_TABLEACCESS_DENIED_ERROR
 show columns from some_db.t1;
 show columns from some_db.t2;
+
+select table_name, column_name, privileges from information_schema.columns
+where table_schema like 'some_db' order by table_name, column_name;
+
 disconnect con1;
 
 connection default;
@@ -139,11 +151,38 @@ deny insert on some_db.t1 to foo;
 deny select on some_db.t2 to foo;
 
 --connect (con1,localhost,foo,,)
+--echo #
+--echo # some_db should still be visible, but it should show up as empty.
+--echo #
+show databases;
 show tables from some_db;
 --error ER_TABLEACCESS_DENIED_ERROR
 show columns from some_db.t1;
 --error ER_TABLEACCESS_DENIED_ERROR
 show columns from some_db.t2;
+
+select table_name, column_name, privileges from information_schema.columns
+where table_schema like 'some_db' order by table_name, column_name;
+
+disconnect con1;
+
+connection default;
+
+grant select(a) on some_db.t1 to foo;
+grant update(a) on some_db.t1 to foo;
+
+--connect (con1,localhost,foo,,)
+--echo #
+--echo # Update privilege on the column is not masked, only see a column.
+--echo #
+show tables from some_db;
+show columns from some_db.t1;
+--error ER_TABLEACCESS_DENIED_ERROR
+show columns from some_db.t2;
+
+select table_name, column_name, privileges from information_schema.columns
+where table_schema like 'some_db' order by table_name, column_name;
+
 disconnect con1;
 
 connection default;