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 existThose 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 privilegesWe 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