1

I am running an Oracle XE (11.2.0.2.0). I have one user U1 creating / owning the tables and another user U2 using them. When U1 grants privileges to U2, everything works.

Now U1 has created a role TESTROLE, granted it some privileges, and granted U2 the TESTROLE. U2 can see TESTROLE in USER_ROLE_PRIVS but does not inherit its privileges: It cannot access the tables, and the privileges are not listed in USER_TAB_PRIVS.

Here is what I did (as U1):

CREATE TABLE TABLE1 ( FIELD1 VARCHAR2(20) );
CREATE ROLE TESTROLE NOT IDENTIFIED;
GRANT SELECT ON TABLE1 TO TESTROLE;
GRANT TESTROLE TO U2;
ALTER USER U2 DEFAULT ROLE ALL;

(Yes, I gave U1 the ALTER USER privilege for this test.)

Now when U2 makes a SELECT * FROM U1.TABLE1 it gets ORA-00942 (table or view does not exist). When I grant the SELECT privilege directly to U2, access works. When U2 calls SELECT * FROM USER_ROLE_PRIVS the TESTROLE is there, with DEFAULT_ROLE=YES.

At first I thought that maybe XE does not support roles but I find no such restriction. What am I missing? Any hint appreciated.

Boneist
  • 22,910
  • 1
  • 25
  • 40
Renardo
  • 499
  • 4
  • 13
  • Was U2 logged in when you granted the role to it? If so, have you tried logging U2 off and back in again? I believe I've had issues before where a user hasn't picked up the right permissions when they've been logged in when they were granted. – Boneist Mar 15 '17 at 12:29
  • 2
    Is user `U2` trying to access `U1.TABLE1` directly, or through a procedure? Procedures can't take advantage of privileges `U2` acquires via roles; it can use only privileges acquired through direct grants. https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1065832643319 –  Mar 15 '17 at 12:36
  • @Boneist, that seems to have done the trick! The interesting thing is that the time of *creating* the role seems to be decisive (`U2` was logged in then). Now I can add more grants to that role, and they become effective immediately, without `U2` logging off. I would like to give you credit – want to transform this into an answer? – Renardo Mar 15 '17 at 12:49

1 Answers1

2

If U2 was logged in at the time you granted the role to it, then try logging U2 off and back in again.

Boneist
  • 22,910
  • 1
  • 25
  • 40
  • 2
    (And now I'm wondering what Bono would have to say about all of this! *{;-) ) – Boneist Mar 15 '17 at 12:53
  • That solved my problem. Yes, so much about atomicity of DB requests… I upvoted your answer but as I am a newbie (second question) my upvote is not displayed. Many thanks anyway. – Renardo Mar 15 '17 at 13:01
  • @Renardo you can always [accept an answer](http://stackoverflow.com/help/someone-answers) on questions you've asked, regardless of how many points you've got. – Boneist Mar 15 '17 at 13:19