Home » SQL & PL/SQL » SQL & PL/SQL » Public Synonyms (oracle,9i,sunos)
Public Synonyms [message #291879] Mon, 07 January 2008 02:19 Go to next message
shahnazurs
Messages: 240
Registered: June 2005
Location: India
Senior Member
Hi,
I have created public synonym empsyn in abc schema, and i confirmed that from the DD view all_synonyms

It looks like the owner of this synonym is public. But i try to query in different schema of the same DB, I am getting error like
select * from abc.EMPSYN
*
ERROR at line 1:
ORA-00942: table or view does not exist

Do i need to do anything? and also please confirm how can I delete public shemas object?

Thanks.
Re: Public Synonyms [message #291882 is a reply to message #291879] Mon, 07 January 2008 02:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

i confirmed that from the DD view all_synonyms

Always post the query you used and its result in addition of what you explain.

Quote:

select * from abc.EMPSYN

If you use "abc." then you don't use the public synonym, you use a direct access to the table.

In addition, creating a public synonym does not grant any privilege on the referenced object. You still have to grant the privilege on it to be able to access it.

Regards
Michel


Re: Public Synonyms [message #291889 is a reply to message #291882] Mon, 07 January 2008 02:32 Go to previous messageGo to next message
shahnazurs
Messages: 240
Registered: June 2005
Location: India
Senior Member
Thanks Michel for this information.

If i need to grant explicitly privileges then what is the exact difference b/t private and public synonyms?

Thanks.
Re: Public Synonyms [message #291892 is a reply to message #291889] Mon, 07 January 2008 02:37 Go to previous message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
With a public synonym anyone that has been granted the privilege to access the table can do it without specifying the owner schema:
select * from EMPSYN;

Note that the public synonym can have the same name than the original object. In this case, anyone, owner or not, access the table with the exactly same statement.

Regards
Michel

Previous Topic: help with outer join
Next Topic: Analyze Table
Goto Forum:
  


Current Time: Sat Nov 02 13:55:16 CDT 2024