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
