I created a view based on some synonyms that reference tables in another schema.
First, I granted access to the underlying tables:
-- in schema1
GRANT SELECT ON schema1.table1 TO schema2;
GRANT SELECT ON schema1.table2 TO schema2;
Then, I created synonyms:
-- in schema2
CREATE OR REPLACE EDITIONABLE SYNONYM table1 FOR schema1.table1;
CREATE OR REPLACE EDITIONABLE SYNONYM table1 FOR schema1.table1;
Now, I created a view based on the synonyms:
-- in schema2
CREATE OR REPLACE EDITIONABLE VIEW
view1
AS
select
table1.column1,
table1.column2,
table2.column3,
table2.column4
from
table1 inner join table2 on table1.column5 = table2.column6
;
Everything works fine, I can access everything in schema2.view1
as long as I am connected as user schema2
.
Now, since I am not allowed to do that, I want my database admins to grant select
privileges on schema2.view1
to some role like
-- in schema2
grant select on schema2.view1 to some_role;
They tell me this fails because I use synonyms in the view definition. Unfortunately, they did not send me any error message. Would I really have to base the view on the underlying tables directly or is there any other way to grant access to the view to a role or other users?
Thanks
Michael