Oracle Database 11g -- CREATE OR REPLACE VIEW Fails with ORA-01720: grant option does not exist

Problem

CREATE OR REPLACE VIEW statement fails with error ORA-01720: grant option does not exist

Solution

The statement will fail when there are invalid privileges granted on the view which would not have been granted normally, such as the owner of the view does not have the required privileges on the dependent objects in the other schemas

This is possible with the SYS user

Consider the following scenario:

Prepare the environment:

SQL> grant connect, create table, create view, unlimited tablespace to user_A identified by user_A;
SQL> grant connect, create table, create view, unlimited tablespace to user_B identified by user_B;
SQL> grant connect, create table, create view, unlimited tablespace to user_C identified by user_C;

User_A will create objects and will grant privileges to User_B:

SQL> conn user_a/user_A
SQL> create table t1 as select * from all_objects;
SQL> create view v1 as select * from t1;
SQL> grant select on v1 to user_B;

User_B now can exercise the grant privilege on view user_a.v1 and create a view based on that object, but will not be able to give access on the view:

SQL> conn user_B/user_B
SQL> select count(*) from user_A.v1;

  COUNT(*)
----------
     57239

SQL> create or replace view v1 as select * from user_A.v1;

View created.

SQL> select count(*) from v1;

  COUNT(*)
----------
     57239

SQL> grant select on v1 to user_C;
grant select on v1 to user_C
                *
ERROR at line 1:
ORA-01720: grant option does not exist for 'USER_A.V1'

But the SYS user may still grant privileges on user_b.v1 view to other users, though those other users won't be able to exercise the privilege:

SQL> grant select on user_b.v1 to user_C;

Grant succeeded.

User_C still cannot access user_b.v1 as the grant is invalid:

SQL> conn user_C/user_C
Connected.
SQL> select count(*) from user_B.v1;
select count(*) from user_B.v1
                            *
ERROR at line 1:
ORA-01031: insufficient privileges

Now user_B cannot replace the view:

SQL> create or replace view v1 as select * from user_A.v1;
create or replace view v1 as select * from user_A.v1
                                                  *
ERROR at line 1:
ORA-01720: grant option does not exist for 'USER_A.V1'

Workarounds:

1) Drop the view and re-create from scratch. Dropping a view also drops all privileges granted on it

2) Identify the invalid privileges and either revoke them or do grant the missing privileges

To help identify the invalid privileges and formulate the required statements to fix them use the script below. Choose the correct option applicable for your environment, either GRANT or REVOKE, and optionally limit the query to specific schemas and objects:

with
options(opt) as (
    select /* uncomment required option */
        'GRANT'
     -- 'REVOKE'
    from dual
),
owners(owner) as ( -- filter by schemas
    select '%' from dual
),
objects(object_name) as ( -- filter by objects
    select '%' from dual
),
dependencies as (
    select
        connect_by_root owner owner,
        connect_by_root name name,
        type,
        referenced_owner,
        referenced_name,
        referenced_type
    from dba_dependencies  t
    where referenced_owner <> owner and owner = connect_by_root owner
    connect by
        prior referenced_owner = owner
        and prior referenced_name = name
        and  referenced_type in ( 'TABLE', 'VIEW')
    start with
        type='VIEW'
        and owner like (select owner from owners)
        and name  like (select object_name from objects)
)
select distinct
    case (select opt from options)
    when 'GRANT' then
    'grant ' || p1.privilege
    || ' on ' || d.referenced_owner ||'.'|| d.referenced_name
    || ' to ' ||  p1.owner || ' with grant option;'
    when 'REVOKE' then
    'revoke ' || p1.privilege
    || ' on ' || p1.owner||'.'||table_name
    || ' from ' || p1.grantee ||';' end command
from
    dba_tab_privs p1
    join dependencies d
        on d.owner = p1.owner and d.name = p1.table_name and p1.type = d.type
    where
        p1.type='VIEW' and p1.owner like (select owner from owners)
        and p1.table_name like (select object_name from objects)
        and d.referenced_type in ( 'TABLE', 'VIEW')
        and not exists (
        select * from dba_tab_privs p2
            where p2.grantee=p1.owner
            and p2.privilege = p1.privilege
            and p2.owner = d.referenced_owner
            and p2.table_name = d.referenced_name
            and p2.grantable='YES')