Oracle-L: RE: The lightbulb goes on - WAS-Debate on rc commands Solaris and

From: Jesse, Rich <Rich.Jesse_at_qtiworld.com>
Date: Fri, 31 Jan 2003 08:50:53 -0800
Message-ID: <F001.00540924.20030131085053@fatcity.com>

Hey Dave,

In order for any session to be created by a user, that user MUST have CREATE SESSION, AFAIK. The only users that should be able to connect to a restricted DB are those with the RESTRICTED SESSION priv. By default, this is granted to the DBA role.

You might want to throw some info/debug output in your script after the startup restrict. Perhaps SELECTs from V$INSTANCE and/or V$SESSION. V$INSTANCE should tell you if the DB is really in restricted mode or not ("logins" column???) as well as provide an audit in your logfile of exactly what DB is being bounced/shutdown. V$SESSION will tell you if another user's sneaking in there.

Also, I mentioned in another post that the semi-Intelligent Agent may cause problems. But now that I look at the account privs, it doesn't look like it should be able to login restricted. Hmmmmm...

Good Beer to you,
Rich

Rich Jesse                           System/Database Administrator
rjesse_at_qtiworld.com                  Quad/Tech International, Sussex, WI USA

-----Original Message-----
Sent: Friday, January 31, 2003 9:34 AM
To: Multiple recipients of list ORACLE-L and Oracle

Ok, so I changed my cold backup script lastnight so it does this;

connect internal/amianidiot
shutdown abort
startup restrict
shutdown normal
exit

It then does the abort and the startup and then the shutdown normal but then I get this and my database hangs;

Shutting down instance (normal)
License high water mark = 10
Fri Jan 31 03:05:35 2003
SHUTDOWN: waiting for logins to complete.

So now I was totally ready to go postal, but then rational thinking prevailed so I RTFMed. I then come across this in the FM;

Typically, all users with the CREATE SESSION system privilege can connect to an open database. Opening a database in restricted mode allows database access only to users with both the CREATE SESSION and RESTRICTED SESSION system privilege; only database administrators should have the RESTRICTED SESSION system privilege

So my question is, should all users be created with the system privilege of CREATE SESSION and only I get the RESTRICTED SESSION in addition? Am I reading this correctly that for the STARTUP RESTRICT to work all users need to have the CREATE SESSION privilege? We have some third party replication software that is trying to make a connection to Oracle every 5 seconds and I think this was the culprit. It DID NOT have the CREATE SESSION but it does now so I am curious as to if the database will shutdown tonight. Did I actually learn something??

Thanks,

Dave(iamanidiot) :o)
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Jesse, Rich
INET: Rich.Jesse_at_qtiworld.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Fri Jan 31 2003 - 10:50:53 CST