Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL)
-
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
-
-
- Closed
-
-
-
- Closed
-
-
-
- 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
-
-
-
- Closed
-
- links to
Activity
anel I think you misunderstood elenst here when writing this patch.
It looks like you made it such that CREATE TEMPORARY TABLE privilege is not enough to run SHOW INDEX in <temp-table>. This is in addition to how it is not enough to show content for SHOW COLUMNS in <temp-table>.
The bug is reported as SHOW COLUMNS should only require CREATE TEMPORARY TABLE privilege for it to show temporary tables columns. Please adjust the patch accordingly, then send it again for review.
Hi elenst why would CREATE TEMPORARY TABLE privilege be enough to run show index or show columns on temporary tables ?
Note that CREATE TABLE grant is not enough to also to run show index or show columns on base tables, it has to have SELECT_ACL example.
So should temporary tables behave different than base tables ?
Or should we expand the MDEV to include that create grant is enough to run show columns/index without select_acl for both temporary/base tables?
Or should we accept the patch as suggested, to run show index/columns you need to have select_acl?
Note also that show create table works without select_acl for both cases.
Hi Elena Stepanova why would CREATE TEMPORARY TABLE privilege be enough to run show index or show columns on temporary tables ?
Note that CREATE TABLE grant is not enough to also to run show index or show columns on base tables, it has to have SELECT_ACL example.
So should temporary tables behave different than base tables ?
They are already very different in regard to grants. Unlike CREATE privilege on a base table, by definition, CREATE TEMPORARY TABLE privilege allows all actions on the temporary table. It is said in the KB and even better in MySQL manual (I don't believe there have been intentional changes comparing to MySQL in this regard yet):
After a session has created a temporary table, the server performs no further privilege checks on the table. The creating session can perform any operation on the table, such as DROP TABLE, INSERT, UPDATE, or SELECT.
Of course the list of examples is incomplete, "no further privilege checks" and "any operation" are the main part here.
It doesn't concern base tables, those have to have specific privileges of course.
That said, I don't insist that it must be made sufficient for SHOW INDEX and SHOW COLUMNS, I leave it to ACL decision-makers. I do however believe that it must be the same for SHOW INDEX and SHOW COLUMNS – either allow both, or don't allow either.
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 ?
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?
Where is a 10.5 version of this patch? MDEV-21702 refactored TMP_TABLE_ACLS.
The same is repeatable with older versions (not related only to
MDEV-12459).Tested with 10.6