Why doesn't Oracle think my user has the "Create Session" privilege when I try to connect?

Question

I've created a role in my Oracle 11g database called TestUserRole that will eventually have more privileges, but currently has only the Create Session privilege. I've assigned that role to a user, TestUser. It is their only role.

I created this as follows:

CREATE ROLE TestUserRole IDENTIFIED BY somepassword;
GRANT Create Session TO TestUserRole;
CREATE USER TestUser IDENTIFIED BY somepassword;
GRANT TestUserRole TO TestUser;

When I try to connect to the database, I receive:

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

I have verified (I think) that the user and role were setup successfully. If I query

select * from dba_role_privs where grantee = 'TESTUSER'

I get

| Grantee  | Granted_Role | Admin_Option | Default_Role |
---------------------------------------------------------
| TESTUSER | TESTUSERROLE | NO           | YES          |

Then if I query

select * from role_sys_privs where role = 'TESTUSERROLE'

I get

| Role         | Privilege      | Admin_Option |
------------------------------------------------
| TESTUSERROLE | CREATE SESSION | NO           |

So it appears that I have created the user and role successfully, the user has the role, and the role has the create session permission. Yet, when I try to log on, Oracle is telling me that the user doesn't have the Create Session permission. Where am I going wrong? Do I have to assign this privilege directly to the user rather than through a role?

Answer 1

Can you try creating your role without the "IDENTIFIED BY password" clause ?

Here it says the following:

The BY password clause lets you create a local role and indicates that the user must specify the password to the database when enabling the role.

Then the problem is because when logging in the role is not enabled yet, the user must enable it using the "SET ROLE" statement as specified here but this can only be done after logging in.