Permissions problem with views

usao

We are having issues with two views that I can only run with pseudosuperuser but the roles should be set for everyone to use.
They can all run the actual code in the view so it is just an issue with accessing the view itself.
View is
create or replace view MY.my_organization_nv_pair_view as
select
organization_id,
nv_pair_id,
value,
effective_dt,
end_dt,
created_by,
created_on,
updated_by,
updated_on
from MYHIST.my_organization_nv_pair_h
where h_latest=1;

alter view MY.my_organization_nv_pair_view owner to MY;

create role rl_my_my_organization_nv_pair_view_s;
grant select on MY.my_organization_nv_pair_view to rl_my_s;
grant select on MY.my_organization_nv_pair_view to rl_my_my_organization_nv_pair_view_s;

I have done a lot of debugging with the roles and nothing seems to work.
So I decide to short circuit the roles and just grant select direct to view.
grant select on MY.my_organization_nv_pair_view to etl;

now it shows in my query
select grantor,privileges_description,object_schema,object_name,object_type,grantee
from v_catalog.grants
where object_name = 'my_organization_nv_pair_view'

MY SELECT MY my_organization_nv_pair_view VIEW rl_my_s
MY SELECT MY my_organization_nv_pair_view VIEW rl_my_my_organization_nv_pair_view_s
MY SELECT MY my_organization_nv_pair_view VIEW etl

The issue I am having seems to stem back to having a view select data out of a different schema than the view schema.
There is one view that works fine but its source tables are within the same schema.
I am wanting to say there is something with the view where it is owned by MY and is selecting from MYHIST.
etl can run the query inside the view but it cannot run the view.
I have tried

grant select,usage on schema MYHIST to MY;
but it does not help.

What am I lacking?

Jim_Knicely

Hi,

I think this might be tied to the ALTER VIEW OWNER command you executed. I was able to reproduce the issue and I opened a support ticket. I'll keep you updated on what I hear.

For example, this works fine (where the view owner remains dbadmin):

dbadmin=> create schema test1;
CREATE SCHEMA

dbadmin=> create table test1.test (c int);
CREATE TABLE

dbadmin=> create schema test2;
CREATE SCHEMA

dbadmin=> create view test2.test_vw as select * from test1.test;
CREATE VIEW

dbadmin=> create user jim;
CREATE USER

dbadmin=> grant usage on schema test2 to jim;
GRANT PRIVILEGE

dbadmin=> grant select on test2.test_vw to jim;
GRANT PRIVILEGE

dbadmin=> \c dbadmin jim
You are now connected to database "dbadmin" as user "jim".

dbadmin=> select * from test2.test_vw;
 c
---
(0 rows)