Skip to content
Advertisements

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

I just review a answer from OTN forums.

I tested here

SQL> create role testme_role identified by test;

Role created.

SQL> grant create session to testme_role;

Grant succeeded.

SQL> create user testme identified by testme;

User created.

SQL> grant testme_role to testme;

Grant succeeded.

SQL> conn testme/testme;
ERROR:
ORA-01045: user TESTME lacks CREATE SESSION privilege; logon denied

Warning: You are no longer connected to ORACLE.

The issue is caused by the fact that the roles are not active in the user's session. 
Although the roles have been granted, they may not be default roles

SQL> select * from dba_role_privs where grantee='TESTME';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
TESTME                         TESTME_ROLE                    NO  YES
SQL> SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE='CONNECT';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
CONNECT                        CREATE SESSION                           NO

SQL> grant create session to testme;

Grant succeeded.

SQL> conn testme/testme;
Connected.
SQL> select * from session_roles;

no rows selected

SQL> create table abc(empno number);
create table abc(empno number)
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> conn / as sysdba
Connected.
SQL> grant create table to testme_role;

Grant succeeded.

SQL> conn testme/testme
Connected.
SQL> set role testme_role;
set role testme_role
*
ERROR at line 1:
ORA-01979: missing or invalid password for role 'TESTME_ROLE'

SQL> set role testme_role identified by test;

Role set.

SQL> select * from session_roles;

ROLE
------------------------------
TESTME_ROLE

SQL> create table abc(empno number);

Table created.

SQL>
Advertisements
2 Comments Post a comment
  1. Interesting…

    thank you for sharing.

    October 10, 2011

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: