SQL - SYNONYMS


A synonym is an alias for a schema object. You can create, view and drop a synonym. Synonyms can provide a level of security by masking the name and owner of an object and by providing location transparency for remote objects of a distributed database.

There are two types of synonyms
1. Private synonym
2. Public synonym

PRIVATE SYNONYM:

A private synonym is a synonym within a database schema that a developer typically uses to mask the true name of a table, view stored procedure, or other database object in an application schema. Private synonyms, unlike public synonyms, can be referenced only by the schema that owns the table or object.

Step 1: Connect to the user and check if the user has privilege to create private synonym.
Step 2:

Create private synonym for table emp.
Step 3:

Connect to any other user/sys user to check if the private synonym can be accessed.
Step 4:

Try dropping the private synonym from other user

	
	We can see that private synonym cannot be accessed or
	dropped by any other user.
Step 5: Connect to the owner of the synonym and drop the synonym

PUBLIC SYNONYM
Public synonyms are accessible to all users.
Step 1:

connect to the user and check if the user has privilege to create public synonym.
Step 2: 
	create public synonym for table emp

	SQL> create public synonym s1 for emp;
	synonym created.
Step 3:

connect to sys user and check for the created public synonym
Step 4: Try connecting other user and check if the public synonym can be accessed
Step 5: drop the public synonym
(SQL - Sequences)