Home » RDBMS Server » Security » Setting up the synonym
Setting up the synonym [message #250802] Wed, 11 July 2007 06:47 Go to next message
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 #250817 is a reply to message #250802] Wed, 11 July 2007 07:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is it by: giving the create synonum previlege to user2, user3, user4 then create a private synonym in these 3 schemas for user1.emp[...]?

This seems correct.

Regards
Michel
Re: Setting up the synonym [message #250832 is a reply to message #250817] Wed, 11 July 2007 08:32 Go to previous messageGo to next message
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
Michel Cadot
Messages: 68625
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

Previous Topic: Please Suggest some privelege
Next Topic: Reference privilege
Goto Forum:
  


Current Time: Thu Mar 28 20:43:07 CDT 2024