Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> Oracle 10g Upgrade and Restricted Session Privilege, Users With RESTRICTED SESSION Privilege Cannot Connect When DB in Rest
JohnnyCeeVA
post Jan 8 2008, 07:48 PM
Post #1


Newbie
*

Group: Members
Posts: 9
Joined: 16-May 07
Member No.: 8,693



We recently upgraded from 9.2.0.7 to 10.2.0.3. Oracle 9i allowed a user with RESTRICTED SESSION priv to connect (sqlplus user /@db) when the database was in restricted mode as it should. However, our Oracle 10g database will not allow a user with RESTRICTED SESSION privilege to log in after 'alter system enable restricted session' is executed. The DBA role does not work nor does granting the system privs CREATE SESSION and RESTRICTED SESSION directly to the user. The error I get when trying to log on is ORA-12526. Any advice on how to remedy this would be much appreciated.
Go to the top of the page
 
+Quote Post
mbobak
post Jan 9 2008, 01:16 AM
Post #2


Advanced Member
***

Group: Members
Posts: 547
Joined: 25-May 05
From: Belleville, MI
Member No.: 2,186



Hmmm...interesting....I learned something new today.

In the 9i world, the listener was not aware of whether the instance was in restricted mode. All users' connections were handed off to a server process, which then refused login if the instance was in restricted session and you didn't have restricted session privilege.

In 10g, the listener is "smarter". It's aware of whether the instance is in restricted session. If it is, it immediately rejects the connection attempt, even for privileged users, since it's not aware of which user is connecting.

This means that connections via a network connection will all be rejected, i.e., noone can login remotely if the instance is in restricted session. You must be logged in to the database server, and then must initiate a non-networked connection, i.e., "/ as sysdba" or "username/password" without a connect string specified.

It's documented here:
http://download.oracle.com/docs/cd/B19306_...t.htm#sthref543

Also, there's a workaround, add "(UR=A)" to the CONNECT_DATA portion of your connect string definition. This is documented in MetaLink Doc ID 444120.1.

Cool, I learned something new!

Thanks for pointing that out, Johnny!

-Mark


--------------------
--
Mark J Bobak
mark@bobak.net

Do you want good, clear answers to your questions?
Read THIS BEFORE posting!
Go to the top of the page
 
+Quote Post
burleson
post Jan 9 2008, 08:29 AM
Post #3


Advanced Member
***

Group: Members
Posts: 11,601
Joined: 26-January 04
Member No.: 13



Hi Mark,

>> Cool, I learned something new!

Yup, that's why I help here, it's a win-win for all concerned . . .

>> noone can login

What is a "noone"?


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
mbobak
post Jan 9 2008, 11:22 AM
Post #4


Advanced Member
***

Group: Members
Posts: 547
Joined: 25-May 05
From: Belleville, MI
Member No.: 2,186



QUOTE (burleson @ Jan 9 2008, 08:30 AM) *
Hi Mark,

>> Cool, I learned something new!

Yup, that's why I help here, it's a win-win for all concerned . . .

>> noone can login

What is a "noone"?


It's in the dictionary:
http://www.m-w.com/dictionary/noone

biggrin.gif


--------------------
--
Mark J Bobak
mark@bobak.net

Do you want good, clear answers to your questions?
Read THIS BEFORE posting!
Go to the top of the page
 
+Quote Post
JohnnyCeeVA
post Jan 10 2008, 02:04 PM
Post #5


Newbie
*

Group: Members
Posts: 9
Joined: 16-May 07
Member No.: 8,693



Thanks, Mark. It worked like a charm. And thanks for the MetaLink Doc ID.
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 19th October 2014 - 11:22 PM