Runtime error ORA-01031: insufficient privileges

After a new version of software was installed in production, the end users reported a runtime error: ORA-01031: insufficient privileges, when selecting from a view. The developers of the code were investigating the problem and half way through, they asked me to have a look at the problem. I saw a function from schema3, which was used in a view in schema2, which was used by schema1. I had just finished learning their situation, when they had tried granting an EXECUTE-privilege on the function with grant option, which solved the error. The privilege with grant option was quickly granted in production and everything went back to normal and all interest in the problem vanished. But I was puzzled: how could that missing grant option lead to the error at runtime. It should not have been possible to grant the SELECT-privilege to schema1 at compile/install time, or at least I thought so. So I did a little investigation.

SQL> create user rwijk3 identified by rwijk3
  2  /

User created.

SQL> grant create session, create procedure to rwijk3
  2  /

Grant succeeded.

SQL> create user rwijk2 identified by rwijk2
  2  /

User created.

SQL> grant create session, create view to rwijk2
  2  /

Grant succeeded.

SQL> create user rwijk1 identified by rwijk1
  2  /

User created.

SQL> grant create session to rwijk1
  2  /

Grant succeeded.

SQL> conn rwijk3/rwijk3@ora11202
Connected.
Sessie:  (no access to V$ tables)

SQL> create function f return number
  2  is
  3  begin
  4    return 42;
  5  end f;
  6  /

Function created.

SQL> grant execute on f to rwijk2
  2  /

Grant succeeded.

SQL> conn rwijk2/rwijk2@ora11202
Connected.
Sessie:  (no access to V$ tables)

SQL> create view v
  2  as
  3  select rwijk3.f() x
  4    from dual
  5  /

View created.

SQL> grant select on v to rwijk1
  2  /
grant select on v to rwijk1
                *
ERROR at line 1:
ORA-01720: grant option does not exist for 'RWIJK3.F'

This is the scenario I expected. Granting the select privilege leads to an error message at compile/install time. But our scenario was a little different than this clean install: the view already existed without the function call and the privilege without the grant option was already granted. Like this:

SQL> show user
USER is "RWIJK2"
SQL> drop view v
  2  /

View dropped.

SQL> create view v
  2  as
  3  select 42 x
  4    from dual
  5  /

View created.

SQL> grant select on v to rwijk1
  2  /

Grant succeeded.

And user rwijk1 could happily query the view of course:

SQL> conn rwijk1/rwijk1@ora11202
Connected.
Sessie:  (no access to V$ tables)

SQL> select x
  2    from rwijk2.v
  3  /

         X
----------
        42

1 row selected.

The new version introduced the function call:

SQL> create or replace view v
  2  as
  3  select rwijk3.f() x
  4    from dual
  5  /

View created.

Which now succeeds, even though select privileges on this view were already granted to schema1. Oracle didn't raise an ORA-01720, like it did in the fresh install scenario. So Oracle does not perform a check whether all already granted privileges remain effective. It also doesn't remove the privileges as user RWIJK1 can describe the view because of the SELECT privilege without the grant option:

SQL> conn rwijk1/rwijk1@ora11202
Connected.
Sessie:  (no access to V$ tables)

SQL> desc rwijk2.v
 Name                                    Null?    Type
 --------------------------------------- -------- ---------------------------
 X                                                NUMBER

It just issues a runtime error message when you issue a select against the view:

SQL> select x
  2    from rwijk2.v
  3  /
  from rwijk2.v
              *
ERROR at line 2:
ORA-01031: insufficient privileges

An ORA-01031 during the parse phase, which you can also see in the trace file:

PARSE ERROR #47952083822528:len=25 dep=0 uid=114 oct=3 lid=114 tim=1299371043385365 err=1031
select x
  from rwijk2.v

When doing a "CREATE OR REPLACE VIEW", and the view is used inside a program unit, like a package, procedure or function, these dependent program units are marked invalid so they need to be compiled again against the new view definition.

When doing a "CREATE OR REPLACE VIEW", and the view has instead of triggers defined against the view, the triggers are removed when the view definition is replaced. And you need to install the instead of triggers again.

And when doing a "CREATE OR REPLACE VIEW", and privileges on this view were granted before replacing the view definition, these privileges are not re-evaluated when the view definition is replaced.