Oracle SELECT granted but still can't access table across users

Question

Can any one see what's wrong with this:

User ABC:

create table def.something (
  id number,
  ref number references def.anotherTable(id)
);

create role ROUser;

grant select on def.something to ROUser;

grant ROUser to ghi;

User DEF:

select * from something;
...
X rows returned

User GHI:

select * from def.something;
ORA-00942: table or view does not exist

Is the fact that there's a foreign key, that GHI doesn't have access to, on def.something the problem?

---

EDIT I've just tried this again on another server and it works fine (i.e., as expected). Not entirely sure what's going on here, but I think it may have something to do with some error on my part... As such, I'm voting to close the question.

Answer 1

You are most probably running that SELECT statement in PL/SQL block? In PL/SQL, priviliges granted through roles are not recognized. Try adding direct SELECT privilege on that table and see if it works.