?
Solved

Oracle default roles

Posted on 2009-02-18
4
Medium Priority
?
1,665 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>
0
Comment
Question by:MikeHunt
  • 2
  • 2
4 Comments
 
LVL 23

Expert Comment

by:David
ID: 23669843
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.
0
 
LVL 1

Author Comment

by:MikeHunt
ID: 23670416
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)?
0
 
LVL 23

Accepted Solution

by:
David earned 200 total points
ID: 23670761
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
0
 
LVL 1

Author Comment

by:MikeHunt
ID: 23671225
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.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses
Course of the Month13 days, 18 hours left to enroll

807 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