Permissions problem with views
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.
create or replace view MY.my_organization_nv_pair_view as
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
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?