Our DBA team created a role (standardRole) to easily managed the minimum system privileges in our organization, this role is having one system privilege currently which is the 'Create Session' privilege.
I created a user and grant him this role (standardRole), the user try to connect using Toad but he failed and this error message appeared - ORA-01045: user user1 lacks CREATE SESSION privilege; logon denied.
Then, I granted him the 'Create Session' Privilege directly this time in addition to the role that he is having already and he successfully connected to the database.
So, I am a little confused, why the 'Create Session' granted through the role in not working, but if its granted directly its working fine??!!
I tried to search about this topic in google, and I found some interesting information in Oracle Help Center, but to be honest I didn't understand it 100%.
We must to specify the role when granted to be Default, if the role is default the database will set the role automatically when the user create his session.
The user can also make the role enabled by using this command:
set role (role name)