Thanks for the question, Jeannette.
Asked: February 11, 2016 - 3:07 pm UTC
Last updated: May 21, 2018 - 12:24 pm UTC
Version: 12c
Viewed 10K+ times! This question is
You Asked
I have this create view statement that is failing with ORA-01720: grant option does not exist for 'SYS.DUAL' on line 7
create or replace view v_decisions
(Sort_No, Name, Abbreviation, Include_In_EM, WPSetup_ID, Include_In_MBP, Web_Name )
as
select Sort_No, Name, Abbreviation, Include_In_EM, WPSetup_ID, Include_In_MBP, Web_Name
from decision
union
select -2, 'Inquiry', 'INQ', 'N', null, 'N', null from dual
union
select -1, 'Applicant', 'APP', 'N', null, 'N', null from dual
;
The user was created by the sys user as sysdba and given the select on sys.dual with this statement.
grant select on sys.dual to newuser with grant option;
I am running Oracle 12c release 1201000100.
This code runs without problems in 10g.
What do I need to do to create this view in 12c?
Jeannette
and Chris said...
You shouldn't need to grant select on dual to your user explicitly - it should already have access:
create user tu identified by tu;
grant connect, create view to tu;
conn tu/tu
create or replace view v_decisions ( sort_no, name, abbreviation, include_in_em, wpsetup_id, include_in_mbp, web_name )
as
select -2, 'Inquiry', 'INQ', 'N', null, 'N', null from dual
union
select -1, 'Applicant', 'APP', 'N', null, 'N', null from dual
;
select * from v_decisions;
SORT_NO NAME ABB I WPSETUP_ID I WEB_NAME
---------- --------- --- - ---------- - --------
-2 Inquiry INQ N N
-1 Applicant APP N N
You'll need to take this up with support.
Rating
(4 ratings)
Is this answer out of date? If it is, please let us know via a Comment