SQL Error: ORA-01720: grant option does not exist

After upgrading from 12.1.0.2 to 12.2.0.1, application team raised one issue. They not able to create view even all required grants exits. I reproduced the same issue. Let me explain.

Assume their is user scott. Now scott wants to create a view using tables of HR schema. SCOTT has select access on all tables of HR schema along with create view access.

e.g.,

create or replace view scott.vu_emp_details

(empid, ename, locality,street, city, state, pin, phone, email)

as

select * from

(select  empid, ename, address1,address2, city, state, pin, phone1, email1

from hr.emp_details);

Error received:

SQL Error: ORA-01720: grant option does not exist for 'hr.emp_details'

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.

After long verification, I did not find any solution. But below workaround helped me.

 

-- drop the view first

drop view scott.vu_emp_details;

-- create the view then

create view scott.vu_emp_details ....

The reason behind:

"The code was changed in 12.2.0.1 so that create view behavior is similar to grant. If you try to make a GRANT on an existing view and the view owner does not have the grant option, then ORA-1720 is the expected result (even in 11.2.0.3). In 12.2.0.1, we have simply made CREATE VIEW consistent with the GRANT behavior, i.e. if an incompatible grant exists, then the new view definition must not be allowed (even with FORCE). In other words, we do not allow incompatible grants to coexist with the view definition and so an error must be thrown. The behavior in release 12.1.0.2 (and earlier) was incorrect; the new behavior in 12.2.0.1 is intentional and correct."

To correct:

1. Remove all grants on the view before REPLACING the view. This will ensure that no incompatible grants exist.

2. Drop and recreate the view. Dropping the view will automatically remove all grants.

In my case, the customer had just upgrade to 12.2.0.1. Hope this can help someone else!