|
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'
|
|