0

In a Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production I'm trying to assign the CREATE SESSION privilege to a custom role MYROLE and then assign MYROLE role to MYUSER user.

GRANT CREATE SESSION to "MYROLE"
GRANT "MYROLE" to "MYUSER"

When I try to connect I received:

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

What am I missing?

If I give CREATE SESSION grant directly to MYUSER it works.

EDIT:

I think that my issue was related to the fact that MYUSER hadn't a DEFAULT ROLE.

  • That should work. The (deprecated) CONNECT role does exactly the same thing. – a_horse_with_no_name Sep 20 '17 at 10:03
0

You might be connected to wrong container database, probably CDB.

Here is a demo which is working fine for me.

I am connected to orclpdb, a pluggable database as sysdba.

[oracle@oracdb ~]$ sqlplus sys@orclpdb as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Sep 20 15:26:59 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> create role myrole;

Role created.

SQL> grant create session to myrole;

Grant succeeded.

SQL> create user testuser identified by testuser;

User created.

SQL> grant myrole to testuser;

Grant succeeded.

SQL> conn testuser/testuser@orclpdb
Connected.
SQL>
  • I've done your example in a clean PDB and worked as expected. I've to investigate why is not working on my other PDB... – sgargel Sep 20 '17 at 10:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.