Grant Privileges and Create Public Synonym in Oracle

Question

I have Views in Schema A and I want to create a Synonym for these views in Schema B.

Could you please help me write a query for Granting the role and creating a synonym?

Not able to Create Public Synonym directly due to no privilige — SitGeek, Aug 23 '16 at 02:44
What does "grant the role" mean? Are you able to log in both as A and B? — Justin Cave, Aug 23 '16 at 03:40
@JeffreyKemp What would you advise on using if I need the views to be accessed from Schema A to B? — SitGeek, Aug 23 '16 at 13:58
Gaz's answer is fine (grant select from A to B) except just remove the "PUBLIC", they don't need to be public synonyms. Schema B just needs the synonyms so it can refer to `viewname` instead of `A.viewname1`. — Jeffrey Kemp, Aug 24 '16 at 00:25

score 2 · Accepted Answer · edited Dec 07 '16 at 14:34

2

From user A, you only need to grant SELECT privilege to user A's views to user B

GRANT SELECT ON A.viewname1 TO B;
GRANT SELECT ON A.viewname2 TO B;
...

From B, creating synonyms allows reference to user A's views without the schema prefix ("A.").

CREATE SYNONYM viewname1 FOR A.viewname1;
CREATE SYNONYM viewname2 FOR A.viewname2;
...

It should now be possible for user B to select from those views like this:

SELECT * FROM viewname1;

Note that a user can only use CREATE SYNONYM if they have the CREATE SYNONYM privilege.

edited Dec 07 '16 at 14:34

StampyCode

answered Aug 23 '16 at 04:18

Gaz

I don't recommend using public synonyms for this sort of thing. One day schema C will want to have its own version of `viewname1`, and you'll end up with a mess. – Jeffrey Kemp Aug 24 '16 at 00:26
1

@Jeffrey: You are correct. I have edited the answer to reflect this. – Gaz Aug 24 '16 at 01:42

1 Answers1