Oracle user does not get privilege from role

Question

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.

Answer 1

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