We help IT Professionals succeed at work.

Oracle default roles

MikeHunt
MikeHunt asked
on
Medium Priority
1,693 Views
Last Modified: 2013-12-18
Hi,

can somebody tell me why I can't connect to an Oracle 10.2.0.3 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
ERROR:
ORA-01045: user TEST lacks CREATE SESSION privilege; logon denied


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

User altered.

SQL> connect test/xxxx_1234  
Connected.
SQL>
Comment
Watch Question

David VanZandtSr Software Engineer
CERTIFIED EXPERT

Commented:
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.

Author

Commented:
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
CERTIFIED EXPERT
Commented:
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.
dvz

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

Ask the Experts

Author

Commented:
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.

OR

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.