Setting up the synonym [message #250802] |
Wed, 11 July 2007 06:47 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
orausern
Messages: 826 Registered: December 2005
|
Senior Member |
|
|
Hi.
We have a schema (User1) which has a table emp. This table needs to be referred in 3 other schemas (user2, user3 and user4). It needs to be referred by in these 3 schemas by the same name -ie in user2 schema also it should be referred as emp. (not as user1.emp).
Currently what is done is that a public synonym is created for user1's emp table. But this is a security risk as even other schemas (other than user2,user3, user4) can see it. So how can I alter this situation so that only these 3 users can refer to it as emp?
Is it by: giving the create synonum previlege to user2, user3, user4 then create a private synonym in these 3 schemas for user1.emp or is there some other way?
Thanks,
Nirav
|
|
|
|
Re: Setting up the synonym [message #250832 is a reply to message #250817] |
Wed, 11 July 2007 08:32 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
orausern
Messages: 826 Registered: December 2005
|
Senior Member |
|
|
Michel,
Thank you. One problem I found with this is: when I create a private synonym, I can do anything i want from the other schemas...like from the user2 schema with this synonym, I can even delete data from this table..but I want to give specific grants...like may be for emp table , I want to give select and update grants only (not delete) to the user2,user3, user4 schemas. then this approach does not work(because with private synonym all grants are given). So is there a solution for that?
|
|
|
Re: Setting up the synonym [message #250838 is a reply to message #250832] |
Wed, 11 July 2007 09:02 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68649 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Synonym does not grant any privilege on target object.
It is just a synonym, an alias, just like "nirav_hyd" for you.
You have to give privileges on the object (maybe through the synonym).
Regards
Michel
|
|
|