Why error "ORA-01720: grant option does not exist for 'SYS.DUAL'" when there is a Public.Dual?

Question

I'm on Oracle 11g, and I do understand the issue of a 3rd-party grant.

But, given that have user1 creating a view "view1" as Select 'foo' from dual.

Then I grant Select on view1 to user2 and I get this error.

But note the "dual" in the view is not qualified as sys.dual, it's just dual. I would think with a synonym public.dual that the actual "dual" used would be public.dual, not sys.dual, so no 3rd party issue should exist because it's public.

And if sys.dual is the one Oracle assumes in this view, one would think that given the use of dual is common in views, and that granting privs on views to other users is also common--wouldn't thousands of users be reporting this issue?

I do see sporadic posts about this but no real solution except to create another copy of dual for the user creating the view, but this doesn't make sense to me. Thanks for any help.

Answer 1

As a similar problem, I needed to create an Oracle VIEW based on a Common Table Expression query involving SYS.USER_OBJECTS and (SYS.) DUAL. While the SQL alone worked, when embedded in CREATE OR REPLACE VIEW ... I got the message from earlier in this thread.

My answer was to do the following:

1. Define an Object TYPE matching my result set (which was just 1 column ,but presumably that is not a limitation)
2. Define a TABLE TYPE based on the OBJECT TYPE
3. Create a TABLE function to traverse the result set for the SQL that was working, and create a result TABLE.
4. Recreate the original view to select against the function. VOILA. Presumably, and I have not addressed this, then users can be given appropriate SELECT permissions on the view. They MAY also need EXECUTE privileges on the function and god-knows-what on the TYPE objects.

So (of course, YMWV due to the site specific table stuff here...)

    DROP VIEW V_BRIDGE_RELATED_TABLES_DELETE_TARGETS;
    DROP TYPE t_tn_tab;
    create or replace TYPE t_tn_row as object (table_name VARCHAR2(128));
    / 
    create or replace TYPE T_TN_TAB  as table of t_tn_row;
    / 
    
    CREATE OR REPLACE FUNCTION GET_BRIDGE_RELATED_TABLES_DELETE_TARGETS
    RETURN T_TN_TAB AS
    
    l_tab t_tn_tab := T_TN_TAB();
    
    begin
    
     for cur_r in (
    
    WITH CTE AS
     ( SELECT DISTINCT TABLE_NAME
        from USER_TAB_COLS utc
       INNER JOIN USER_OBJECTS uo
          ON (utc.TABLE_NAME = uo.OBJECT_NAME and uo.OBJECT_TYPE = 'TABLE')
       where utc.COLUMN_NAME = 'BRIDGE_GD'
         AND utc.TABLE_NAME NOT IN ('BRIDGE',
                                    'INSPEVNT',
                                    'ROADWAY',
                                    'STRUCTURE_UNIT',
                                    'KDOTBLP_BRIDGE',
                                    'KDOTBLP_INSPECTIONS',
                                    'USERBRDG',
                                    'USERINSP')
         and utc.TABLE_NAME NOT IN
             (SELECT name
                from sys.all_dependencies
               where type = 'MATERIALIZED VIEW'
                 and referenced_type = 'TABLE'
                 and owner = 'KDOT_BLP'
                 and name = referenced_name)
            -- lots of exclusions for transitional stuff in Portal database
         AND utc.TABLE_NAME NOT LIKE 'MV_%'
         AND utc.TABLE_NAME NOT LIKE '%_MV'
         AND utc.TABLE_NAME NOT LIKE '%_BAK'
         AND utc.TABLE_NAME NOT LIKE '%_BKU'
         AND utc.TABLE_NAME NOT LIKE '%_TEMP'
         AND utc.TABLE_NAME NOT LIKE '%_TMP'
         AND utc.TABLE_NAME NOT LIKE '%_T'
         AND utc.TABLE_NAME NOT LIKE '%_KT'
         AND utc.TABLE_NAME NOT LIKE 'ARC_%'
         AND utc.TABLE_NAME NOT LIKE '%_AR'
         AND utc.TABLE_NAME NOT LIKE '%_BACKUP'
         AND utc.TABLE_NAME NOT LIKE '%_NEW_%'
         AND utc.TABLE_NAME NOT LIKE '%_NEW'
         AND utc.TABLE_NAME NOT LIKE '%_RENAMED'
         AND utc.TABLE_NAME NOT LIKE '%_RENAMED%'
         AND utc.TABLE_NAME NOT LIKE '%_TRANSFORM'
         AND utc.TABLE_NAME NOT LIKE 'TMP%'
         AND utc.TABLE_NAME NOT LIKE '%_202%'
      UNION
      SELECT 'KDOTBLP_BRIDGE' AS TABLE_NAME
        FROM DUAL
      UNION
      SELECT 'KDOTBLP_INSPECTIONS' AS TABLE_NAME
        FROM DUAL
      UNION
      SELECT 'KDOTBLP_LOAD_RATINGS' AS TABLE_NAME
        FROM DUAL
      UNION
      SELECT 'KDOTBLP_QAQC_REVIEW_FINDINGS' AS TABLE_NAME
        FROM DUAL
      UNION
      SELECT 'KDOTBLP_QAQC_REVIEW_FLAGS' AS TABLE_NAME
        FROM DUAL
      UNION
      SELECT 'KDOTBLP_DOCUMENTS' AS TABLE_NAME
        FROM DUAL
      UNION
      SELECT 'USERSTRUNIT' AS TABLE_NAME
        FROM DUAL
      UNION
      SELECT 'USERRWAY' AS TABLE_NAME
        FROM DUAL
      UNION
      SELECT 'USERBRDG' AS TABLE_NAME
        FROM DUAL
      UNION
      SELECT 'USERINSP' AS TABLE_NAME
        FROM DUAL) 
    SELECT DISTINCT (TABLE_NAME) AS TABLE_NAME  
    FROM CTE    
    GROUP BY CTE.TABLE_NAME 
    ORDER BY 1  
      ) loop
     l_tab.extend;
     l_tab(l_tab.last) := t_tn_row(cur_r.TABLE_NAME);
      end loop;
      return l_tab;
    end GET_BRIDGE_RELATED_TABLES_DELETE_TARGETS;
    /
    
    CREATE OR REPLACE VIEW V_BRIDGE_RELATED_TABLES_DELETE_TARGETS AS
    select * from TABLE(GET_BRIDGE_RELATED_TABLES_DELETE_TARGETS());
    /
    
    GRANT EXECUTE ON GET_BRIDGE_RELATED_TABLES_DELETE_TARGETS TO PUBLIC;
    
    GRANT SELECT ON  V_BRIDGE_RELATED_TABLES_DELETE_TARGETS TO PUBLIC;

and this does in fact give me the view I want back.

Answer 2

After consulting our dbas, the issue is an Oracle "Feature" in 11.2.0.4:

TL;DR verison: As of v 11.0.4, if your View uses Dual, then you can't grant that View anything but SELECT.

Why would we want to grant a view more than Select? In our case the app vendor packaged their updates in such a way that the database portion of the updates automatically scripted full CRUD grants to the master app-schema on every new object, and this included views, because it was simply easier to script that way. This all worked fine until 11.0.4, when Oracle said/enforced "Hey, you can't do that".

---

Full version:

(Quoted from Oracle site https://support.oracle.com/epmos/faces/BugDisplay?parent=DOCUMENT&sourceId=1628033.1&id=17994036)

Oracle Database - Enterprise Edition - Version 11.2.0.4 to 11.2.0.4 [Release 11.2] Information in this document applies to any platform. SYMPTOMS After upgrading from 11.2.0.3 to 11.2.0.4, you encounter the following error while executing the "create or replace view" statement: ORA-01720: grant option does not exist Views were created before the upgrade and "CREATE OR REPLACE VIEW" had worked fine. CAUSE The observed behavior is correct. You will get this ORA-1720 error when REPLACING a view that selects from some other user's tables and both of the following conditions are true:

• you have already granted select or other privileges on the VIEW to some other user
• the view owner does not have the GRANT option on the tables being selected from (or view owner may have some privilege with grant option but not others) Development has explained it as follows:

The code was changed in 11.2.0.4 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 11.2.0.4, 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 11.2.0.3 (and earlier) was incorrect; the new behavior in 11.2.0.4 is intentional and correct. SOLUTION To avoid this issue, you can do either of the following:

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.

REFERENCES BUG:17994036 - POST UPGRADE TO 11.2.0.4 CREATE OR REPLACE FAILS WITH ORA-01720 BUG:18024486 - ORA-1720 WHEN CREATING VIEW AFTER TO HAVE UPGRADE FROM 11.2.0.3.0 TO 11.2.0.4.0

Answer 3

With sys (as sysdba ) database user grant the necessary privileges, and after that try yo recreate the view with sys (as sysdba ) database user. This was helpful for me.

Regards, Vase Tusevski