Users Being Granted The CONNECT Privilege Via Another Role Cannot Connect (Doc ID 556692.1)

Last updated on NOVEMBER 04, 2015

Applies to:

Oracle Server - Enterprise Edition - Version: 8.1.7.0 to 11.1.0.6 - Release: 8.1.7 to 11.1
Information in this document applies to any platform.
Checked for relevance on 19-Sep-2011

Symptoms

A user is being granted the CONNECT role, which is checked to have been granted the CREATE SESSION privilege,  via another role is and is not able to connect :




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

GRANTEE GRANTED_ROLE  ADM DEF
------- ------------- --- ---
WES     WES_ROLE      NO  NO
WES     LILLY07_ROLE  NO  NO

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

GRANTEE      GRANTED_ROLE   ADM DEF
------------ -------------- --- ---
LILLY07_ROLE CONNECT        NO  YES
LILLY07_ROLE APPL_MONITOR   NO  YES
LILLY07_ROLE RESOURCE       NO  YES

SQL> select * from dba_sys_privs where grantee='CONNECT';

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

SQL> connect wes/pwd
ERROR:
ORA-01045: user WES lacks CREATE SESSION privilege; logon denied

After granting the CREATE SESSION privilege directly to the user, it is able to connect :


SQL> grant create session to wes;

Grant succeeded.

SQL> conn wes/wes
Connected


Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms