Synonyms in Oracle.

Synonyms are aliases for objects in database. Synonyms make it easy for a schema to access objects in other schema. Normally objects in other schema can be accessed by prefixing schema name to the object, but with the use of synonym there is no need to use schema name while accessing the object.

Synonyms can be public as well as private.

Public Synonym : Public synonyms are objects of public schema and can be accessed by any schema in the same or different database(assuming databases are connected by a link).

Let's see how to create a public synonym.

CREATE PUBLIC SYNONYM tab1 FOR sch1.tab1;

Now the table tab1 can be accessed by any schema using name tab1.

Private Synonym : Private synonyms are private to a schema. They can be referenced only by schema which owns the object. Private synonym can be created by removing the private keyword from the above CREATE statement.

CREATE SYNONYM tab1 FOR sch1.tab1;

Note :

1) Knowing a public synonym for an object doesn't mean that a schema can access the object as it wants. Access policies to the object will always be determined by access permissions granted to the schema for the object.

2) Permission for creation of a public synonym has to be granted by DBA.

For more info visit.