[MDEV-28455] CREATE TEMPORARY TABLES privilege is insufficient for SHOW COLUMNS - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL)
Fix Version/s: 10.3.37, 10.4.27, 10.5.18, 10.6.11, 10.7.7, 10.8.6, 10.9.4, 10.10.2, 10.11.1
Component/s: Authentication and Privilege System, Data Definition - Temporary
Labels:
None

Description

CREATE TEMPORARY TABLES privilege is supposed to allow all actions with temporary tables created by the session.
However, while it also allows to run SHOW COLUMNS on such tables, it's not sufficient to get a result, it remains empty.
Oddly, SHOW INDEX works.

create database db;

create user foo@localhost;

grant create temporary tables on db.* to foo@localhost;

--connect (con1,localhost,foo,,db)

create temporary table tmp (a int, key(a));

show create table tmp;

show columns in tmp;

show index in tmp;

# Cleanup

--disconnect con1

--connection default

drop database db;

drop user foo@localhost;

bb-10.9-anel 32274c3e8a4
show columns in tmp;
Field Type Null Key Default Extra
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 Ignored
tmp 1 a 1 a A NULL NULL NULL YES BTREE NO

Attachments

Issue Links

relates to

MDEV-28544 Appending SELECT privilege not applied on show columns and show index

Closed

MDEV-28548 ER_TABLEACCESS_DENIED_ERROR is missing information about DB

Closed

MDEV-28783 Privilege inconsistency between show columns and information_schema.columns

Open

MDEV-12459 The information_schema tables for getting temporary tables info is missing, at least for innodb there is no INNODB_TEMP_TABLE_INFO

Closed

MDEV-28544 Appending SELECT privilege not applied on show columns and show index

Closed

links to

PR #2180

(1 links to)

Activity

Ascending order - Click to sort in descending order

View 6 older comments

Anel Husakovic added a comment - 2022-06-09 10:44

Thanks elenst,
have tried to test MySQL, seems there is bug too for show columns in <table>, which results with empty set, same as with MariaDB:

mysql> select @@version;

+-----------+

| @@version |

+-----------+

| 8.0.29    |

+-----------+

1 row in set (0.00 sec)

mysql> create user foo@localhost identified by '123';

mysql> grant create temporary tables on test.* to foo@localhost;

mysql> show grants for current_user;

+----------------------------------------------------------------+

| Grants for foo@localhost                                       |

+----------------------------------------------------------------+

| GRANT USAGE ON *.* TO `foo`@`localhost`                        |

| GRANT CREATE TEMPORARY TABLES ON `test`.* TO `foo`@`localhost` |

+----------------------------------------------------------------+

2 rows in set (0.00 sec)

mysql> create temporary table tmpbar (b int, key(b));

Query OK, 0 rows affected (0.00 sec)

mysql> show index in tmpbar;

+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |

+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

| tmpbar |          1 | b        |            1 | b           | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               | YES     | NULL       |

+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

1 row in set (0.00 sec)

mysql> show columns in tmpbar;

Empty set (0.00 sec)

mysql> insert into tmpbar values (3),(4);

Query OK, 2 rows affected (0.00 sec)

Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from tmpbar;

+------+

| b    |

+------+

|    3 |

|    4 |

+------+

2 rows in set (0.00 sec)

mysql> show columns in test.tmpbar;

Empty set (0.00 sec)

So regarding the decision, I guess we should stick to MySQL documentation CREATE TEMPORARY TABLES (without SELECT_ACL, as in case for base tables) is enough to get SHOW COLUMNS what is actually the real bug, right serg ?

Anel Husakovic added a comment - 2022-06-09 10:44 Thanks elenst , have tried to test MySQL , seems there is bug too for show columns in <table> , which results with empty set, same as with MariaDB : mysql> select @@version; + -----------+ | @@version | + -----------+ | 8.0.29 | + -----------+ 1 row in set (0.00 sec) mysql> create user foo@localhost identified by '123' ; mysql> grant create temporary tables on test.* to foo@localhost; mysql> show grants for current_user ; + ----------------------------------------------------------------+ | Grants for foo@localhost | + ----------------------------------------------------------------+ | GRANT USAGE ON *.* TO `foo`@`localhost` | | GRANT CREATE TEMPORARY TABLES ON `test`.* TO `foo`@`localhost` | + ----------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> create temporary table tmpbar (b int , key (b)); Query OK, 0 rows affected (0.00 sec) mysql> show index in tmpbar; + --------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | + --------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | tmpbar | 1 | b | 1 | b | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL | + --------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ 1 row in set (0.00 sec) mysql> show columns in tmpbar; Empty set (0.00 sec) mysql> insert into tmpbar values (3),(4); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from tmpbar; + ------+ | b | + ------+ | 3 | | 4 | + ------+ 2 rows in set (0.00 sec) mysql> show columns in test.tmpbar; Empty set (0.00 sec) So regarding the decision, I guess we should stick to MySQL documentation CREATE TEMPORARY TABLES (without SELECT_ACL , as in case for base tables) is enough to get SHOW COLUMNS what is actually the real bug, right serg ?

Sergei Golubchik added a comment - 2022-08-07 21:47 - edited

Of course, CREATE TEMPORARY TABLES should be enough for SHOW COLUMNS to work on temporary tables.
SHOW COLUMNS doesn't tell you anything you don't know, you've created the table, you already know all the columns it has, why would you ever need additional privileges to see what you already know?

Sergei Golubchik added a comment - 2022-08-07 21:47 - edited Of course, CREATE TEMPORARY TABLES should be enough for SHOW COLUMNS to work on temporary tables. SHOW COLUMNS doesn't tell you anything you don't know, you've created the table, you already know all the columns it has, why would you ever need additional privileges to see what you already know?

Anel Husakovic added a comment - 2022-08-08 13:14

Yes, understood and implemented in PR #2180. Thanks.

Anel Husakovic added a comment - 2022-08-08 13:14 Yes, understood and implemented in PR #2180. Thanks.

Vicențiu Ciorbaru added a comment - 2022-10-18 06:49

OK to push

Vicențiu Ciorbaru added a comment - 2022-10-18 06:49 OK to push

Marko Mäkelä added a comment - 2022-10-25 07:45

Where is a 10.5 version of this patch? ~~MDEV-21702~~ refactored TMP_TABLE_ACLS.

Marko Mäkelä added a comment - 2022-10-25 07:45 Where is a 10.5 version of this patch? MDEV-21702 refactored TMP_TABLE_ACLS .

MariaDB Server

CREATE TEMPORARY TABLES privilege is insufficient for SHOW COLUMNS

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration