Thread: accessing cross-schema materialized views

We have:

    create materialized view schema1.matview.....
    grant select on table schema1.matview to mygroup

    create view schema2.usingview as select ... from schema1.matview
    grant select on table schema2.using to mygroup

and yet we receive "permission denied for materialized view" on a user with the mygroup role selecting from schema2.usingview? The same user can select from schema1.matview without issue?

I must be tired and I can't see why that should fail.... :(

We have:

    create materialized view schema1.matview.....
    grant select on table schema1.matview to mygroup

    create view schema2.usingview as select ... from schema1.matview
    grant select on table schema2.using to mygroup

Tim Clarke <tim.clarke@minerva.info> writes:
> We have:
>     create materialized view schema1.matview.....
>     grant select on table schema1.matview to mygroup

>     create view schema2.usingview as select ... from schema1.matview
>     grant select on table schema2.using to mygroup

> and yet we receive "permission denied for materialized view" on a user with the mygroup role selecting from
schema2.usingview?

The owner of the schema2.usingview is the one who must have
privilege to read the underlying schema1.matview.  Our
permissions messages are, I fear, frequently not very good
about saying whose privileges were checked.

            regards, tom lane

On 31/03/2021 23:42, Tom Lane wrote:
> The owner of the schema2.usingview is the one who must have
> privilege to read the underlying schema1.matview.  Our
> permissions messages are, I fear, frequently not very good
> about saying whose privileges were checked.
>
> regards, tom lane


That nailed it Tom, thanks.

Tim Clarke MBCS
IT Director
Direct: +44 (0)1376 504510 | Mobile: +44 (0)7887 563420


Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | Frankfurt: +49 (0)69 7191 6000 | Hong Kong: +852
58031687 | Toronto: +1 647 503 2848
 
Web: https://www.manifest.co.uk/



Minerva Analytics Ltd - A Solactive Company
9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United Kingdom

________________________________

Copyright: This e-mail may contain confidential or legally privileged information. If you are not the named addressee
youmust not use or disclose such information, instead please report it to
admin@minerva.info<mailto:admin@minerva.info>
Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: Registered in England Number 11260966 & The
ManifestVoting Agency Ltd: Registered in England Number 2920820 Registered Office at above address. Please Click Here
https://www.manifest.co.uk/legal/for further information.