[MDEV-13439] Database permissions are not enough to run a subquery with GROUP BY within a view Created: 2017-08-03  Updated: 2020-08-25  Resolved: 2017-08-10

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Subquery, Views
Affects Version/s: 10.2
Fix Version/s: 10.2.8

Type: Bug Priority: Critical
Reporter: Elena Stepanova Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 1
Labels: None

Issue Links:
Relates
relates to MDEV-13523 Group By in a View, called within a S... Closed

 Description    

create database db;
 
use db;
 
create table t (i int);
 
 
 
create user foo@localhost;
 
grant all on db.* to foo@localhost;
 
 
 
--connect (con1,localhost,foo,,)
 
 
 
use db;
 
create view v as select * from (select i from t group by i) sq;
 
select * from v;
 
 
 
# Cleanup
 
--disconnect con1
 
--connection default
 
drop view v;
 
drop table t;
 
drop user foo@localhost;

The view gets created all right, but SELECT * FROM v fails:

mysqltest: At line 12: query 'select * from v' failed: 1356: View 'db.v' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

Here is a slightly different test case which might give a hint for a reason:

create database db;
 
use db;
 
create table t1 (i int);
 
create table t2 (j int);
 
 
 
create user foo@localhost;
 
grant all on db.* to foo@localhost;
 
 
 
--connect (con1,localhost,foo,,)
 
 
 
use db;
 
create view v as select 1 from t1 inner join (select j from t2 group by j) `rs` on(i = j);
 
select * from v;
 
 
 
# Cleanup
 
--disconnect con1
 
--connection default
 
drop view v;
 
drop table t1, t2;
 
drop user foo@localhost;

In this case, SELECT ends with a different error:

mysqltest: At line 13: query 'select * from v' failed: 1143: SELECT command denied to user 'foo'@'localhost' for column 'j' in table '/data/bld/10.2/mysql-test/var/tmp/mysqld.1/#sql_23ef_0'

So, it looks like it's the temporary table that the user has no access to.

 Comments   
Comment by Oleksandr Byelkin [ 2017-08-04 ]

Problem is not in "group by" but in temporary table of subquery in the FROM clause. This also fail:

set @@optimizer_switch='derived_merge=OFF';
 
create view v as select * from (select i from t) sq;
 
select * from v;

Comment by Oleksandr Byelkin [ 2017-08-07 ]

revision-id: cf66c30ce592ad88d447568272bdced2046f2906 (mariadb-10.2.7-20-gcf66c30ce59)
parent(s): bcc10a5a447805ce64aa13ee6a037c1618219616
committer: Oleksandr Byelkin
timestamp: 2017-08-07 13:42:35 +0200
message:

MDEV-13439: Database permissions are not enough to run a subquery with GROUP BY within a view

The bug is result adding ability to have derived tables inside views.
Fixed checks should be a switch between view/derived or select derived and information schema.

—

Comment by Oleksandr Byelkin [ 2017-08-07 ]

github branch is bb-10.2-MDEV-13439

Generated at Thu Feb 08 08:05:36 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.