Grants by role changed in Oracle 11g?

Question

I tried to grant CONNECT to a user through a role:

CREATE ROLE my_role IDENTIFIED BY "passwd";
GRANT CONNECT TO my_role;

CREATE USER my_user IDENTIFIED BY "passwd";
GRANT my_role TO my_user;

When I try this in 10g it works fine, while in 11g login is rejected:

ORA-01045:user MY_USER lacks CREATE SESSION privilege; logon denied

Granting CREATE SESSION to the role does not make a difference.
I can only login after directly granting CONNECT (or CREATE SESSION) to the user.

Has Oracle changed this behavior or am I doing something wrong?

Answer 1

I think you might have gotten away with a security "feature" in 10g. The way I read the SQL Reference and Security Guide for 11g indicates that password-enabled roles require the use of the SET ROLE my_role IDENTIFIED BY passwd before any rights granted by that role are effective.

You can't CREATE SESSION until you have the role, and you can't have the role until you issue SET ROLE.

Catch-22.

Answer 2

I tried to execute a PLSQL block where I use SET ROLE as in the example:

SQL> EXEC BEGIN DBMS_SESSION.SET_ROLE ('SMPXXX IDENTIFIED BY PASIVASXXX');INSERT INTO SMPXXX.HISTORIAL(H_ID,H_FECHA,H_FUNCEJECUTADA,H_USUARIO) VALUES(99995,sysd ate,'A','B');END; BEGIN BEGIN DBMS_SESSION.SET_ROLE ('SMPXXX IDENTIFIED BY PASIVASXXX');INSERT INT O SMPXXX.HISTORIAL(H_ID,H_FECHA,H_FUNCEJECUTADA,H_USUARIO) VALUES(99995,sysdate, 'A','B');END; END;

     *

ERROR at line 1: ORA-06550: line 1, column 90: PL/SQL: ORA-00942: table or view does not exist ORA-06550: line 1, column 71: PL/SQL: SQL Statement ignored

If I try the same by console, it´s Ok : SQL> set role SMPXXX identified by PASIVASXXX;

Role set.

SQL> INSERT INTO SMPXXX.HISTORIAL(H_ID,H_FECHA,H_FUNCEJECUTADA,H_USUARIO) VALUES (99994,SYSDATE,'A','B');

1 row created.

Has Oracle changed something between 9i and 10g? Gus