Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.5
-
None
Description
create database db; |
create table db.t (pk int auto_increment primary key, a int default 0); |
create user foo; |
grant insert on db.t to foo; |
|
|
--connect (con1,localhost,foo,,)
|
insert into db.t () values () returning pk, a; |
|
|
# Cleanup
|
--disconnect con1
|
--connection default
|
drop database db; |
drop user foo; |
|
10.5 6cee9b195 |
MariaDB [test]> insert into db.t () values () returning pk, a; |
+----+------+ |
| pk | a |
|
+----+------+ |
| 1 | 0 |
|
+----+------+ |
1 row in set (0.020 sec) |
MariaDB [test]> show grants;
|
+---------------------------------------+ |
| Grants for foo@% | |
+---------------------------------------+ |
| GRANT USAGE ON *.* TO `foo`@`%` | |
| GRANT INSERT ON `db`.`t` TO `foo`@`%` | |
+---------------------------------------+ |
2 rows in set (0.001 sec) |
It's not specified in the KB, but one could reasonably expect that RETURNING requires a SELECT privilege at least on the columns which it returns. It also seems to be the case for PostgreSQL, or at least it's documented this way there:
Use of the RETURNING clause requires SELECT privilege on all columns mentioned in RETURNING.
Besides, if it works as it is by design, there is an inconsistency because RETURNING * for the same table throws an error:
MariaDB [test]> insert into db.t () values () returning *; |
ERROR 1143 (42000): SELECT command denied to user 'foo'@'localhost' for column 'pk' in table 't' |
DELETE .. RETURNING expectedly returns an error for both variants:
MariaDB [test]> show grants;
|
+---------------------------------------+ |
| Grants for foo@% | |
+---------------------------------------+ |
| GRANT USAGE ON *.* TO `foo`@`%` | |
| GRANT DELETE ON `db`.`t` TO `foo`@`%` | |
+---------------------------------------+ |
2 rows in set (0.001 sec) |
|
|
MariaDB [test]> delete from db.t returning pk, a; |
ERROR 1143 (42000): SELECT command denied to user 'foo'@'localhost' for column 'pk' in table 't' |
MariaDB [test]> delete from db.t returning *; |
ERROR 1143 (42000): SELECT command denied to user 'foo'@'localhost' for column 'pk' in table 't' |