In a scenario when there are three user U1, U2, U3. User 1 have create a table and granted select access to user 2.
User 2 have create a view on this table and sysdba have granted select access on this view to User 3.
Now user 3 have access to select date from view create by user 2 because access is granted by sysdba, but even then User 3 can not select from the view and getting error insufficient privileges.
Explanation:
U2 can select from the table hence U2 can create the view on it and select from the view, but when U3 tries to select from the view it checks as U2 and see if U2 can grant select access on the underlying table which is owned by U1.
Because U1 just granted select access to U2 but did not mention "WITH GRANT OPTION" hence U3 can not select from the view and getting the permission error.
To fix the issue grant select to U2 on table create by U1 with "WITH GRANT OPTION" and this will fix the issue.
The example is described as below:
User 2 have create a view on this table and sysdba have granted select access on this view to User 3.
Now user 3 have access to select date from view create by user 2 because access is granted by sysdba, but even then User 3 can not select from the view and getting error insufficient privileges.
Explanation:
U2 can select from the table hence U2 can create the view on it and select from the view, but when U3 tries to select from the view it checks as U2 and see if U2 can grant select access on the underlying table which is owned by U1.
Because U1 just granted select access to U2 but did not mention "WITH GRANT OPTION" hence U3 can not select from the view and getting the permission error.
To fix the issue grant select to U2 on table create by U1 with "WITH GRANT OPTION" and this will fix the issue.
The example is described as below:
SQL> create user &user identified by &pass default tablespace harvey
SQL> temporary tablespace temp2;
Enter value for user: u1
Enter value for pass: u1
old 1: create user &user identified by &pass default tablespace harvey temporary tablespace temp2
new 1: create user u1 identified by u1 default tablespace harvey temporary tablespace temp2
User created.
SQL> /
Enter value for user: u2
Enter value for pass: u2
old 1: create user &user identified by &pass default tablespace harvey temporary tablespace temp2
new 1: create user u2 identified by u2 default tablespace harvey temporary tablespace temp2
User created.
SQL> /
Enter value for user: u3
Enter value for pass: u3
old 1: create user &user identified by &pass default tablespace harvey temporary tablespace temp2
new 1: create user u3 identified by u3 default tablespace harvey temporary tablespace temp2
User created.
SQL> conn / as sysdba
Connected.
SQL> create table u1.t1 as select * from scott.emp
2 /
Table created.
SQL> conn u1/u1
Connected.
SQL> select count(*) from t1;
COUNT(*)
----------
14
SQL> grant select on t1 to u2;
Grant succeeded.
SQL> conn u2/u2
Connected.
SQL> create view v2t1 as select * from u1.t1;
View created.
SQL>
SQL> grant select on v2t1 to u3;
grant select on v2t1 to u3
*
ERROR at line 1:
ORA-01720: grant option does not exist for 'U1.T1'
SQL> conn / as sysdba
Connected.
SQL> grant select on u2.v2t1 to u3;
Grant succeeded.
SQL> conn u3/u3
Connected.
SQL> select count(*) from u2.v2t1;
select count(*) from u2.v2t1
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> conn u1/u1
Connected.
SQL> grant select on t1 to u2 with grant option;
Grant succeeded.
SQL> conn u3/u3
Connected.
SQL> select count(*) from u2.v2t1;
COUNT(*)
----------
14
