If you are recompiling a view with create or replace view… and that view references DUAL, you may get ORA-01720: grant option does not exist for ‘SYS.DUAL’.
Why, you may ask?
Well from 11.2.0.4 onwards, Oracle corrected the permissions model on views after a long standing oversight.
In brief, you will get this ORA-1720 error when REPLACING a view that selects from some other user’s tables (or views / mviews) and both of the following conditions are true:
- Non “select” grants exist : You have already granted privileges other than select on the VIEW to some other user.
- Non matching grants exist on referenced tables / views / mviews : The view owner does not have the matching GRANT option on the tables / views / mviews being selected from. Incompatible grants cannot exist.
Here’s a simple demo of the error
create table t_table (val number);
create view v_view as select * from t_table;
grant all on v_view to public;
create or replace view v_view as select 2 as x from dual;
>> create or replace view v_view as select 2 as x from dual
Error at line 7
ORA-01720: grant option does not exist for 'SYS.DUAL'
The line
grant all on v_view to public;
Effectively grants DELETE, INSERT, SELECT, UPDATE, REFERENCES, MERGE VIEW, ON COMMIT REFRESH, QUERY REWRITE, DEBUG, FLASHBACK, READ on the view to public.
When we recompile v_view
create or replace view v_view as select 2 as x from dual;
Oracle checks DUAL has the same grants as the view (DELETE, INSERT, SELECT, UPDATE, REFERENCES, MERGE VIEW, ON COMMIT REFRESH, QUERY REWRITE, DEBUG, FLASHBACK, READ to public), whereas only SELECT on DUAL has been granted to public, breaking the conditions above.
The easiest way to fix the problem is to either drop the view first :
[snip...]
drop view v_view;
create view v_view as select 2 as x from dual;
View created.
Or revoke the non select grants and only add the select grant:
[snip...]
revoke all on v_view;
grant select on v_view;
create or replace view v_view as select 2 as x from dual;
View created.
