Sunday, March 15, 2009

Public Synonym

If you are the owner of a table or view and you create a public synonym for the table
or view, when you grant select on the synonym mane you are in effect granting select
on the table or view. The synonym eliminates the need to clarify the table or view
with the schema.table name.

And here is how yo create public synonym

CREATE public SYNONYM [table/view name] FOR [owner].[table/view name];

and then you can grant select/insert/update/delete on this object to the role which will apply to any user who was assigned that role. Without the role assigned to that specific user he/she would still not be able to access that object event it was create as public synonym