Oracle : ORA-01045: user username lacks CREATE SESSION privilege; logon denied : When grants are given through role

If the user has "grant create session" grant through a role and the schema is not able to create session then it must be the problem with the user not picking up the role.

Solution :

alter user <username> default role all;

More About Roles

__Alter user scott default role all;__


1. DEFAULT ROLE is the clause that is used to enable a particular role as a default role when user log on to the database.

2. To use the DEFAULT ROLE clause OR before using DEFAULT ROLE command, you must grant a ROLE to USER.


For ex> Assume a user has beed granted three roles...,

Role1, Role2,Role3... Connect privileage granted to Role1, I need only role1 to be a default role.

So, I issued a command to enable a role1 as a default role.

SQL>ALTER USER scott DEFAULT ROLE ROLE1;

What oracle does do, it enable a Role1 as a default role WHEN USER LOGS ON . Now you have connect priviliage only. All other roles are disabled.

If you specify ALTER USER scott DEFAULT ROLE ALL.

\\ All roleS granted to user are active\\

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

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

Warning: You are no longer connected to ORACLE.

Resolution:

SQL> grant create session to <username>;