Today i had a peculiar issue, An user is not able to access the view from other schema,
Let me explain the scenario.....
Say there are three users
user_A
user_B
user_C
requirement is to grant select privilege to user_C for the View on user_B whose base table is in user_B.
Even though i have given 'Grant select on user_a.table to user_c;' and 'Grant select on user_b.view to user_c;'
when user_c issues,
select * from user_b.view; it throws ORA-01031: insufficient privileges
SQL> select * from user_b.view;
select * from user_b.view
*
ERROR at line 1:
ORA-01031: insufficient privileges
Finally I got it to work. It fails when the view is being executed from a third schema. For that to work you have to add the grant option:
grant select on user_a.table to user_b with grant option;
After this my view worked fine.
Let me explain the scenario.....
Say there are three users
user_A
user_B
user_C
requirement is to grant select privilege to user_C for the View on user_B whose base table is in user_B.
Even though i have given 'Grant select on user_a.table to user_c;' and 'Grant select on user_b.view to user_c;'
when user_c issues,
select * from user_b.view; it throws ORA-01031: insufficient privileges
SQL> select * from user_b.view;
select * from user_b.view
*
ERROR at line 1:
ORA-01031: insufficient privileges
Finally I got it to work. It fails when the view is being executed from a third schema. For that to work you have to add the grant option:
grant select on user_a.table to user_b with grant option;
After this my view worked fine.
No comments:
Post a Comment