Skip to Main Content
Forums

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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

private synonym

813348Jan 18 2011 — edited Jan 18 2011
hii i want to know that if i create a private synonym vinod_test_prv on a table vinod_test in hr schema and do not give select privilege on it but give privilege on the table can i select from the synonym in other schema say scott



create synonym vinod_test_prv for vinod_test;-- in hr schema

revoke select on vinod_test_prv from scott;-- in hr schema

SQL> grant select on vinod_test to scott;-- in hr schema

Grant succeeded.



scott schema

SQL> select count(*) from hr.vinod_test_prv;-- here i am able to select from synonym whereas i have revoked select from synonym

COUNT(*)
----------
107

Edited by: 810345 on Jan 18, 2011 3:50 PM
This post has been answered by Sven W. on Jan 18 2011
Jump to Answer

Comments

Sven W.
Answer
You can't give priviledges on a synonym.

A synonym is only an alias, a kind of "tinyurl" that allows you to write the name of an object in a different way.

So if your object is name "HR"."VINOD_TEST" and you create a synonym on that. Then the grant statement will use name resolution, finds the synonym and translates this synonym to the proper object name. The grant however will be given on the object, not on the synonym.

So lets check what happens during your example statements:
810345 wrote:
hii i want to know that if i create a private synonym vinod_test_prv on a table vinod_test in hr schema and do not give select privilege on it but give privilege on the table can i select from the synonym in other schema say scott
create synonym vinod_test_prv for vinod_test;-- in hr schema

revoke select on vinod_test_prv from scott;-- in hr schema
You just revoked the select priv on HR.VINOD_TEST for Scott
SQL> grant select on vinod_test to scott;-- in hr schema

Grant succeeded.
You just gave the select priv to HR.VINOD_TEST to Scott

Therefore Scott can now select from this table. And he can also select from the synonym.

Edited by: Sven W. on Jan 18, 2011 11:22 AM
Marked as Answer by 813348 · Sep 27 2020
789895
Hi,

The privileges on the object which you created the synonym for will be treated while accessing the synonyms. So when you create a synonym on a table, the privileges associated for the table will be applicable on the synonym.

cheers

VT
1 person found this helpful
813348
so does that mean
1> i can use private synonym inside a schema if i m going to use it in other schema i have to use the owner prefix??


2> giving grant on a synonym is useless so even if i do not grant select on PRIVATE synonym but have select grant on a table i can still select from that synonym??
789895
>
so does that mean
1> i can use private synonym inside a schema if i m going to use it in other schema i have to use the owner prefix?? - YES if there is a select privilege on the object which the synonym is created.


2> giving grant on a synonym is useless so even if i do not grant select on PRIVATE synonym but have select grant on a table i can still select from that synonym?? YES the object is main not the synonym
>

cheers

VT
1 person found this helpful
1 - 4
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Feb 15 2011
Added on Jan 18 2011
4 comments
1,366 views