Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Grant Access to View

519002Oct 27 2023 — edited Oct 27 2023

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

Comments
Post Details
Added on Oct 27 2023
1 comment
2,792 views