Thanks for the question, chetan.
Asked: August 17, 2017 - 6:36 am UTC
Last updated: December 15, 2017 - 6:44 am UTC
Version: 12c
Viewed 100K+ times! This question is 
You Asked
Dear Team.
Firstly I would like to thank you for your valuable time and quick responses.
I have two users userA, userB.
userA has a table abc and userB want to access it.
When userA grant select, insert, update, delete to userB directly then userB can
access it with no trouble:
------------------------------------------------------------------------------------
userA> grant select, insert, update, delete on userA.abc to userB;
userA> grant succeded
userB> select * from userA.abc; -- works fine
------------------------------------------------------------------------------------
But, when i grant select, insert, update, delete to a role and
then assign this role to userB, then userB can't access it:
------------------------------------------------------------------------------------
userA> create role test_role;
userA> grant select, insert, update, delete on userA.abc to test_role;
userA> grant test_role to userB;
userB> select * from userA.abc; -- gives error table or view does not exists
------------------------------------------------------------------------------------
Please suggest how to do it through roles.
Thanks and Regards,
and Chris said...
Your example looks OK to me...
By any chance does userB have an existing role granted to it which is the default role?
If so, when you grant the new role it won't be a default role. Which means it's not enabled when you connect.
You can overcome this by running "set role all" or altering userB to include this new role in the default list:
grant create session to u identified by u;
create role r1;
create role r2;
grant r1 to u;
alter user u default role r1;
create table t (
x int
);
grant select, insert, update, delete on chris.t to r2;
grant r2 to u;
conn u/u
insert into chris.t values (1);
SQL Error: ORA-00942: table or view does not exist
select * from chris.t;
SQL Error: ORA-00942: table or view does not exist
set role all;
insert into chris.t values (1);
1 row inserted.
select * from chris.t;
X
1
Rating
(2 ratings)
Is this answer out of date? If it is, please let us know via a Comment