We help IT Professionals succeed at work.

Oracle default roles

MikeHunt asked
Medium Priority
Last Modified: 2013-12-18

can somebody tell me why I can't connect to an Oracle DB with a new user even after I grant the connect role to this user? I needed to set the connect role to be a default role before I could connect. Why? How does that work?

I tried the following:

SQL> grant connect, resource to test;

Grant succeeded.

SQL> connect test/xxxx_1234
ORA-01045: user TEST lacks CREATE SESSION privilege; logon denied

Warning: You are no longer connected to ORACLE.
SQL> connect / as sysdba
SQL> alter user test default role connect;          

User altered.

SQL> connect test/xxxx_1234  
Watch Question

David VanZandtSr Software Engineer

A user account may have default or non-default privileges.  In some shops and applications, additional privileges are granted and revoked on the fly, so that a user at a SQL prompt, for example, cannot make changes outside of the application.
CONNECT happens to have been a role that "ought to" have been designed as default in the first place....  Oh well.  HTH.


Thanks for the reply.
So even though 'create session' is part of the connect role .. I cannot connect to the DB with this role until the role has been assigned as a default role for my user .. or the 'create session' privilege has been explicitly granted to my user. Is this right?
When I create a new user I always have to set the connect role as default or I'll never be able to login .. is this right also?
And this has been designed this way deliberately as a safety measure .. I just don't understand then why you would assign a role without default .. what can you do? You can't even login(with connect)?
Sr Software Engineer
That is correct, and thank you for catching the underlying system priv is indeed "create session".  Oracle continued using CONNECT in 10g, but without its previous DDL capabilities.
I have never read of the reasoning behind this decision; it's likely on Ask Tom or such.  My opinion is that this feature allows an account to be created ahead of its activation or need-by date.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts


Ok, I've got it now. If I create a new role myself it will be set to default .. by default :-) Connect is an exception. Thanks for your explanation.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.