Menu

Dipesh Majumdar

Blog and Paintings

Synonym

June 27, 2012

Introduction:

The dictionary meaning of synonym is - a word having the same or nearly the same meaning as another in the language, as happy, joyful, elated. 

Similarly in Oracle Database, synonym is nothing but an alternative name for objects such as table, view, sequence or a stored procedure which resides in a different schema. In other words synonyms are pointers to objects residing in remote schemas. 

In order to illustrate the concept of synonym let us consider an example here:

Given scenario:

Emp_info is table in test1 schema.

Requirement: 

  1. Access emp_info from other schemas without using dot operator. 
  2. How to drop synonyms?
  3. Explain the difference between private synonym and public synonym.

Solution:

  1. This can be achieved by the help of synonyms. And if we want to access emp_info from all schemas we will need public synonym. We will see how in the practical demonstration...
  2. We will use drop synonym command for this. We will see in the practical demonstration below.
  3. Private synonym is local to the schema in which it is created. Public synonym can be used by all users.


Practical Demonstration:

  1. The table emp_info is created in schema 'test1'. 1 row is inserted and then committed. 


create table in user test1

Now I am querying this table from another user 'test'. Here I have to use the dot operator to access table emp_info belonging to user test1.

query table emp_info in test1 from user test

But I don't want to use the DOT operator. What do I do?

I create SYNONYM.

But I will create synonym in which schema?

I will create SYNONYM in that schema from where I am required to access emp_info table. So I will create SYNONYM in schema 'test'.

sql command:create synonym emp_info for test1.emp_info;

create synonym emp_info from user test

Now I can query emp_info without the DOT operator from schema 'test' as shown below.

query emp_info table belonging to test1 from test without using dot operator

Magical! But what exactly is emp_info? You can see it here, by querying user_objects which contains all the objects belonging to user 'test':

emp_info details in user_objects data dictionary view

What if I want to access emp_info (residing in test1) from other users like test2, test3 and so on. Simple! I won't be able to do so unless and until I create a public synonym. Note if I simply say synonym, it is understood as private synonym. To mean public synonym we have to use the word 'public'.

sql command: create or replace public synonym emp_info for test1.emp_info;

creating public synonym

accessing emp_info table in test1 from test2 using public synonym

Yes, Now we are able to access emp_info table belonging to test1 from user test2 as well. And now we can access emp_info from other users as well. And this is possible because of public synonym present in user 'test'. That is what a public synonym is... as the word public suggests... it can be used by anyone (ofcourse provided he has the privilege to access).

Now one important observation. From the same schema you can create both private and public synonyms with the same name. In the schema test, there are two synonyms by the same name - emp_info. Strange, isn't it? However this is little tricky. Though we create a public synonym with the same name emp_info from user test, the owner of this public synonym is not user test. The owner is PUBLIC.  

Showing the two synonyms with same name in the below screen shot.

sql command:select * from all_synonyms where upper(synonym_name)='EMP_INFO';

public and private synonym with same name

2. How to drop synonyms?

If your synonym was created as a private synonym, you can drop it with the following command:
DROP SYNONYM synonym_name; 


If your synonym was created as a public synonym, you can drop it with the following command:
DROP PUBLIC SYNONYM synonym_name;

Note:

1. For the above illustration 3 users have been used. They are test, test1 and test2. All these three users have dba role. This is to simplify the concept of synonyms. We will discuss on roles and privileges in another blog post. 

2. Private synonym is simply referred as synonym. For public synonyms, public word should be used.

3. When same name private and public synonyms coexist, preference of usage is private synonym first and then public synonym.

4. In the above illustration, we have created emp_info private synonym and also emp_info public synonym from the same user test. Now question is can we create another table in the same test schema with the name emp_info? The answer is no (Verified and tested on 11.2.0.1).

5. User and schema are the same things. It's for ease of communication. I say - issue some command as test user. Same thing can be said this way... issue some command in test schema.   

Go Back

if you create a synonym on a table, then you can grant select permission on the synonym to user test2 and then the user test2 can access the synonym and table as well. Same holds good for the case when you grant select on base table...then user test2 can access both table and synonym.



Comment