ORA-01720 ... Error while recreating a view in 12c

We were testing some of my scripts for 12c upgrade . There were few changes to few views . So , we changed the script and deploy it . 

The script was similar to the following 

Create or replace view v_emp_access .... 

When we execute the view , we got the following error .

Error at Command Line : 24 Column : 8

Error report -

SQL Error: ORA-01720: grant option does not exist for 'SCOTT.EMP'

01720. 00000 -  "grant option does not exist for '%s.%s'"

*Cause:    A grant was being performed on a view and the grant option was

           not present for an underlying object.

*Action:   Obtain the grant option on all underlying objects of the view.

All the grants have been provisioned and everything works in lower versions. 

 

After few minutes of R&D , found that the behavior was changed by Oracle from 11.2.0.4 onwards. The resolution is to either drop all the grants and recreate the view or drop the view ( thus dropping the grants )  and recreate it again.  

Per Oracle , this is not a bug ; works as designed. 

You can refer to MOS article (Doc ID 1628033.1) for additional information .

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.