Oracle default roles

Posted on 2009-02-18
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  
Question by:MikeHunt
    LVL 23

    Expert Comment

    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.
    LVL 1

    Author Comment

    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)?
    LVL 23

    Accepted Solution

    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.
    LVL 1

    Author Comment

    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.

    Featured Post

    Gigs: Get Your Project Delivered by an Expert

    Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

    Join & Write a Comment

    Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
    Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
    This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now