On ORA-01031: insufficient privileges error received when selecting from a view

Tweet

I was asked to give an example on how to solve the ORA-01031: insufficient privileges error received by a user who select a view OWNED by another user (view created on a table owned by another user as well).
I mean: user W is the owner of a table (T1); user V has built a view (VIEW_T1) on that table; user R want to query V.VIEW_T1 view... but he receives the ORA-01031: insufficient privileges error.
I think the easy way is to provide some line codes. First of all the following are the schema and objects settings:

CREATE USER W
  IDENTIFIED BY W
  DEFAULT TABLESPACE USERS
  TEMPORARY TABLESPACE TEMP_GROUP_SYS
  PROFILE DEFAULT
  ACCOUNT UNLOCK
/
  -- 1 Role for W 
  GRANT CONNECT TO W
/
  ALTER USER W DEFAULT ROLE ALL
/
  GRANT CREATE TABLE TO W
/
  GRANT CREATE SESSION TO W
/
  ALTER USER W QUOTA 1M ON USERS
  /
CREATE USER V
  IDENTIFIED BY V
  DEFAULT TABLESPACE USERS
  TEMPORARY TABLESPACE TEMP_GROUP_SYS
  PROFILE DEFAULT
  ACCOUNT UNLOCK
/
  -- 1 Role for R 
  GRANT CONNECT TO V
/
  ALTER USER V DEFAULT ROLE ALL
/
  GRANT CREATE TABLE TO V
/
  GRANT CREATE SESSION TO V
/
  ALTER USER V QUOTA 1M ON USERS
/
CREATE USER R
  IDENTIFIED BY R
  DEFAULT TABLESPACE USERS
  TEMPORARY TABLESPACE TEMP_GROUP_SYS
  PROFILE DEFAULT
  ACCOUNT UNLOCK
/
  -- 1 Role for R 
  GRANT CONNECT TO R
/
  ALTER USER R DEFAULT ROLE ALL
/
  GRANT CREATE TABLE TO R
/
  GRANT CREATE SESSION TO R
/
  ALTER USER R QUOTA 1M ON USERS
/
CREATE TABLE W.T1 (A NUMBER)
/
INSERT INTO W.T1 VALUES (1)
/
INSERT INTO W.T1 VALUES (2)
/
INSERT INTO W.T1 VALUES (3)
/
GRANT SELECT ON W.T1 TO V
/
CREATE OR REPLACE VIEW V.T1_VIEW (A_VIEW) AS SELECT A FROM W.T1
/

Now let's try to select those records from all the 3 users, W, V and R respectively and let's see what we get:

user@hostname:~$ sqlplus w/w
SQL> select * from t1;

  A
----------
  1
  2
  3

SQL> set feed on
SQL> /

  A
----------
  1
  2
  3

3 rows selected.

Now connect using the V user...

SQL> conn v/v
Connected.
SQL> select * from t1_view;

    A_VIEW
----------
  1
  2
  3

3 rows selected.

SQL> select * from w.t1;

  A
----------
  1
  2
  3

3 rows selected.

And now using the R user...

SQL> conn r/r
Connected.
SQL> select * from v.t1_view;
select * from v.t1_view
                *
ERROR at line 1:
ORA-00942: table or view does not exist

Those code lines state: 1) V user has the permissions to query his view and, of course, the table on which his view was built on; 2) R user is trying to query an object that seems it doesn't exist on the V schema; that's because R has no permissions to query that object; So let's try to grant the select permission to R user so he can query the view on V schema:

SQL> conn sys/my_pwd
Connected.
SQL> grant select on v.t1_view to r;

Grant succeeded.

Let's try to query again V.T1_VIEW from R user and see what happens:

SQL> conn r/r
Connected.
SQL> select * from v.t1_view;
select * from v.t1_view
                *
ERROR at line 1:
ORA-01031: insufficient privileges

We are getting finally the ORA-01031: insufficient privileges error. Now the question was: Do I need to grant select permission on the W.T1 table directly to R user ? The answer is: no.

SQL> conn sys/my_pwd
Connected.
SQL> grant select on w.t1 to r;

Grant succeeded.

SQL> conn r/r
Connected.
SQL> select * from v.t1_view;
select * from v.t1_view
                *
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> select * from w.t1;

  A
----------
  1
  2
  3

3 rows selected.

What's happening ? We are able to select rows directly from W user, but not from the view owned by V, built and with the right privileges on W.T1 table ?!?! The error is in the grant option granted to V user to select W.T1 table. It should be like the following command:

SQL> conn sys/my_pwd
Connected.
SQL> grant select on w.t1 to v with grant option;

Grant succeeded.

And now...

SQL> conn r/r
Connected.
SQL> select * from v.t1_view;

    A_VIEW
----------
  1
  2
  3

3 rows selected.

Of course even if we revoke the privilege to select directly the W.T1 table from R user, R user is still able to query the view owned by V user.

SQL> conn sys/my_pwd
Connected.
SQL> revoke select on w.t1 from r;

Revoke succeeded.

SQL> conn r/r
Connected.
SQL> select * from v.t1_view;

    A_VIEW
----------
  1
  2
  3

3 rows selected.

That's all