[MDEV-32502] CREATE table privilege allows to see SHOW CREATE output, but not columns Created: 2023-10-17  Updated: 2023-11-28

Status: Open
Project: MariaDB Server
Component/s: Authentication and Privilege System
Affects Version/s: 10.4, 10.5, 10.6, 10.10, 10.11, 11.0, 11.1, 11.2
Fix Version/s: 10.4, 10.5, 10.6, 10.11, 11.0, 11.1

Type: Bug Priority: Minor
Reporter: Elena Stepanova Assignee: Sergei Golubchik
Resolution: Unresolved Votes: 0
Labels: None


 Description   

I'm setting it to minor, because it is apparently intentional. I can't find it in the KB, but at least the MySQL manual says:

SHOW COLUMNS displays information only for those columns for which you have some privilege

so one can say that CREATE grant doesn't give any column privileges, thus nothing is shown.

It doesn't seem logical though, if I can see all the columns through SHOW CREATE TABLE anyway, why cannot I see them via SHOW COLUMNS or from INFORMATION_SCHEMA.COLUMNS. 

create database db;
 
create table db.t (a int);
 
 
 
create user u@localhost;
 
grant create on db.t to u@localhost;
 
 
 
--connect (con1,localhost,u,,db)
 
show create table db.t;
 
show columns in db.t;
 
select table_name, column_name from information_schema.columns where table_name = 't';
 
 
 
# Cleanup
 
--disconnect con1
 
--connection default
 
drop user u@localhost;
 
drop database db;

Actual result, 10.4 b1c8ea83

 
show create table db.t;
 
Table	Create Table
 
t	CREATE TABLE `t` (
 
  `a` int(11) DEFAULT NULL
 
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
 
show columns in db.t;
 
Field	Type	Null	Key	Default	Extra
 
select table_name, column_name from information_schema.columns where table_name = 't';
 
table_name	column_name
 
disconnect con1;


Generated at Thu Feb 08 10:31:51 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.