1

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.

Xophmeister
  • 8,884
  • 4
  • 44
  • 87

1 Answers1

0

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.

Przemyslaw Kruglej
  • 8,003
  • 2
  • 26
  • 41
  • Adding an explicit `grant select` does fix the problem, but the GHI example above is not being executed within a PL/SQL block: It's straight SQL. – Xophmeister Oct 31 '13 at 10:43
  • @Xophmeister strange, I created all the objects and users and it works for me. Which database version are you using? Where are you running your queries (SQLDeveloper?)? Can you try creating another table (simplest, one numerical column) in `def` schema, grant select on it to `ROUSer` role and check if you can query it as `ghi` user? – Przemyslaw Kruglej Oct 31 '13 at 10:55
  • 10gR2 through Oracle SQL Developer. Tried using a table with just one numeric column and it works as I would have expected. Also, I can't replicate the problem on a different server... As such, I think I've done something stupid, but just haven't spotted it yet! Voting to close (and thank you for your help :) – Xophmeister Oct 31 '13 at 11:42
  • Faced the same issue, tried running the select statement via sqlplus got the output but not in SQL developer – rainu Apr 13 '19 at 06:30