Dimitry Sibiryakov napsal(a):

>> I can't select from master_some - not to mention that I can't select
>> even from master_all, which is not dependent on any other view, just
>> tables (to which it seems to have the correct permissions).
>>
>
> Just to narrow field of possibilities - try to use JOIN+GROUP BY
> instead of subquery.
>

Looks like either a bug or a feature of Firebird now - I _can_ select
from a view which is based on groups, but not from a similar view based
on subselects. I used the following script:

create table mstr (
klic integer not null,
hodn integer,
constraint pk_mstr primary key (klic)
);
/
create table det (
klic integer not null,
mstr integer,
hodn integer,
constraint pk_det primary key (klic)
);
/
create view mstr_all (
klic,
hodn,
det_hodn
) as
select m.klic, m.hodn, (select first 1 d.hodn from det d where
d.mstr=m.klic order by d.klic)
from mstr m;
/
create view mstr_some (
klic,
hodn,
det_hodn
) as
select * from mstr_all where klic>123;
/
create view mstr_all2 (
klic,
hodn,
det_hodn
) as
select m.klic, m.hodn, MAX(d.hodn)
from mstr m
left join det d on d.mstr=m.klic
group by m.klic, m.hodn
/
create view mstr_some2 (
klic,
hodn,
det_hodn
) as
select * from mstr_all2 where klic>123;
/
grant select on mstr_all to test, view mstr_some;
/
grant select on mstr_some to test;
/
grant select on mstr_all2 to test, view mstr_some2;
/
grant select on mstr_some2 to test;
/
grant select on mstr to view mstr_all, mstr_all2;
/
grant select on det to mstr_all, mstr_all2;
/


With this setup, database owner can select from all views while user
"test" can only select from mstr_all2 and mstr_some2, but not from
mstr_all and mstr_some. Unfortunately, I can't rewrite my views to use
joins rather than subselect - I need to find the oldest (or the newest)
d.hodn, which does not necessarily be largest/smallest. Besides, the
tables concerned are quite large and I am getting significantly better
(as in "several orders of magnitude better") performance from subselect
than a join.

Pepa Kokes