Synonyms and Privileges

Usually synonyms are used to abstract the location / schema dependency of the underlying object. In other words , if I have a table "emp" in a remote database "Sales" ( in Oracle / other DBMS such as SQL Server , DB2 via transparent gateway) , 

It is easier to create synonym as "semp" in our local database to make our SQLs a lot more readable and also to make it portable ( across DEV , QA regions) . 

For an example 

Create synonym s_emp for scott.emp@salesdb;

Here s_emp is the name of the synonym and it references "emp" table/view in "Scott" schema in "SalesDB" ( via dblink) . 

The SQL (  'select ename from scott.emp@salesdb ') can be rewritten as 
'select ename from s_emp' , hiding/abstracting  all the location details .

In the development phase , the remote database(DBlink)  could be a development remote database ; and when the application is deployed to LIVE ,only the synonyms needs to be changed to point to LIVE database , thus your code is  lot more portable than harcoding the remote database names in the code. 


However , we need to be careful with how the privileges are being applied . Let us look at a simple example . 

In the following example , we will create a synonym is SCOTT schema and grant the  "SELECT" privilege to "APPUSR".


SQL> show user
USER is "SCOTT"
SQL> desc emp
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                              NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

SQL> Create synonym s_emp for scott.emp;

Synonym created.

SQL> grant select on s_emp to appusr ;

Grant succeeded.


In another session , let us connect as "APPUSR" and view the data in the synonym as well as the base table. Yes  , we can see the data. 


SQL> show user
USER is "APPUSR"
SQL>
SQL> Select empno , ename from scott.s_emp;

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS
      7900 JAMES
      7902 FORD
      7934 MILLER

14 rows selected.

SQL> select empno , ename from scott.emp;

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS
      7900 JAMES
      7902 FORD
      7934 MILLER

14 rows selected.



Now , let us go back to SCOTT schema and drop the synonym .

SQL> show user
USER is "SCOTT"

SQL> Drop synonym s_emp;

Synonym dropped.

You would think APPUSR will not be able to view the data . 
Not really. As an APPUSR  , I can look at the data from the underlying object as shown below. 

SQL> show user
USER is "APPUSR"
SQL>


SQL> Select ename , empno  from scott.s_emp;
Select ename , empno  from scott.s_emp
                                 *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> Select ename , empno  from scott.emp;

ENAME           EMPNO
---------- ----------
SMITH            7369
ALLEN            7499
WARD             7521
JONES            7566
MARTIN           7654
BLAKE            7698
CLARK            7782
SCOTT            7788
KING             7839
TURNER           7844
ADAMS            7876
JAMES            7900
FORD             7902
MILLER           7934

14 rows selected.


So , as always , better to understand on how the privileges works .

Comments Welcome.