Sunday, October 30, 2011

Synonym in Oracle

 It is used to hide original name and owner of the  Table.
 It provides security by hiding identity of the  component.
 Desc ,DML and Select are allowed.
 DML on Table are reflected in synonym and DML  on synonym are reflected in Table.
 It will not hold data.
 It is stored permanantly in "user_synonyms"  system table.
 It can be shared with other users.
 2 types
 1. Private synonym : created by user
 2. Public synonym : created by DBA only

 Syntax : private synonym
 create synonym <synonym name> for
 <db object name>;


 Ex: create synonym Esyn for emp;
      desc esyn
      select * from esyn;
   insert into esyn values (....................);
   update esyn set sal = sal + 3000
   where deptno = 30;
   delete from esyn where sal > 30000;
   select * from emp;
   grant all on esyn to user1;

  user1 :
  select * from scott.esyn;
  insert into scott.esyn values (................);

  Scott :
 desc user_synonyms
 select * from user_synonyms;
 drop synonym esyn;

 Public Synonym : ( DBA )
 create public synonym stu_info for student;
 grant all on stu_info to public;

 scott :   select * from stu_info;

 user1 :  select * from stu_info;
         insert into stu_info values(...............);

 user2 : select * from stu_info;

 View Vs Synonym :

 Views will support to share selected rows and   columns  with other users.
 Views support to retrieve arithematic expressions   and multiple table contents .

 Synonym supports to share entire object with other  user.

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect