Public Synonyms

Hi:
I just created a schema with few tables and granted public synonyms on those tables.. when i log in as other users and try to access the tables I cannot.. but, when i log in as system user, I can.. what could be the problem?? Urgent..

You have created the synonyms but did you grant privileges? You either need to grant privileges such as select to public or to to the indivildual users. Otherwise they can only describe the table.

magskn

you need to "create" public synonyms and then "grant" permissions on those synonyms to public (or specific users)

- Rajeev

rsuri -
No, you don't grant privileges on Public synonyms. By definition, they are accessible by all users in the database (hence the term 'Public'). However, in order to be of any use, a user DOES have to have privileges granted on the underlying object.

Example:
I create table carp.emp.
If I want to let you have access to the table, I grant the privilege:
GRANT SELECT ON emp TO rsuri;

You can now select from the table, but must fully qualify the table name:
SELECT * FROM carp.emp;

Now I create a public synonym:
CREATE PUBLIC SYNONYM employee FOR carp.emp;

Without any further granting, you can now successfully execute:
SELECT * FROM employee;
and see the data in carp.emp.

Finally, I revoke the privilege on the table:
REVOKE SELECT ON emp FROM rsuri;

Even though you can still "see" the synonym, when you try
SELECT * FROM employee;
you will get a "Table or View Does Not Exist" error.

Contrary to a previous post, you will get a similar error if you try to do a DESC on a public synonym where you have no privileges on the underlying object:
DESC employee
ERROR:
ORA-04043: object carp.emp does not exist

oops.
You are right Carp; Thanks for correcting.

- Rajeev