Home » RDBMS Server » Server Administration » Oracle start/shutdown with non sysdba account
Oracle start/shutdown with non sysdba account [message #206899] Sat, 02 December 2006 02:07 Go to next message
kumarpk108
Messages: 7
Registered: December 2006
Junior Member
Hi,
We are running SAP 4.6C, Oracle 9.2.0.6 and we have a following
requirement from SOX audit point of view.

We logon to unix server using our individual UNIX
accounts and then su to ora<SID> which is oraC11
where C11 is the sid for the oracle database.
Once we are in orac11 unix level
anyone can connect to Oracle like the following

>sqlplus "/ as sysdba"
then issue commands like shutdown, startup etc

According to SOX audit guidelines there is an
exposure and security hole here. Since the orac11
unix password is common anyone of team members
who can get in, can perform shutdown, startup type
of activities.

We tried to create an individual oracle user account
and granted connect, resource, dba and other
privileges to that.and then tried to connect to
oracle using that account.

$ sqlplus myuser/mypassword
shutdown
Insufficient privleges

We are not able to shutdown / startup using myuser
(oracle account).

Can you please guide us in the above in terms of
how to close the hole and perform database activites
using myuser account instead of
> sqlplus "/ as sysdba"

Also please advise us about how to disable the
functionality of logging in as sqlplus "/ as sysdba"
and performing shutdown/startup operations.

Appreciate your help.

Thanks and Regards
Kumar

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.6.0 - Production

SQL> grant sysdba to myuser;

Grant succeeded.

SQL> grant sysoper to myuser;

Grant succeeded.

SQL> connect myuser/mypass;
Connected.
SQL> shutdown immediate;
ORA-01031: insufficient privileges
SQL>
Re: Oracle start/shutdown with non sysdba account [message #206926 is a reply to message #206899] Sat, 02 December 2006 06:18 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Quote:
anyone can connect to Oracle like the following
>sqlplus "/ as sysdba"
then issue commands like shutdown, startup etc


Even without Sarbanes-Oxley compliance, this is just plainly wrong. No shop with real business is supposed to allow this.

edit the sqlnet.ora. comment all the entries (meanwhile, what are you entries in sqlnet.ora?
Using any sqlnet encryption?) if you are not sure which to comment off,
Comment only this entry with a #. Like this
#SQLNET.AUTHENTICATION_SERVICES = (NTS)

Quote:

common anyone of team members
who can get in, can perform shutdown, startup type
of activities.


Only DBA's should be allowed to shutdown/startup activities.
Unix account 'oracle' (or any account that was used to install the database)
should not be used for ANY NORMAL database operations (treat it like root).

All you need to do is,
create individual unix accounts. Add them to dba group.
Withing database level,create one or more administrative accounts. ENABLE AUDITING on these accounts.
Grant SYSDBA to the account. and login as sysdba.
This will not work
sqlplus myuser/mypassword

You need to login as SYSDBA
SYS > connect scott/tiger
Connected.
SCOTT > shutdown
ORA-01031: insufficient privileges
SCOTT > connect scott/tiger as sysdba
Connected.
SYS > shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.


Note a subtle difference here in above session (i am using 10g. But it should make any difference). THe sqlprompt will change accordingly. If you are logged in as a regular user (scott/tiger), you are logged in as SCOTT. If you are logged in as SYDBA (scott/tiger as sysdba) , you are logged in as SYS.
This is almost equivalent to 'sudo' in unix.
Re: Oracle start/shutdown with non sysdba account [message #207034 is a reply to message #206899] Mon, 04 December 2006 00:09 Go to previous messageGo to next message
jesuisantony
Messages: 132
Registered: July 2006
Location: Chennai
Senior Member
Login the user as sysdba and please check.
Re: Oracle start/shutdown with non sysdba account [message #207146 is a reply to message #206899] Mon, 04 December 2006 05:27 Go to previous messageGo to next message
kumarpk108
Messages: 7
Registered: December 2006
Junior Member
Thanks Mahesh for your detailed reply. I have some clarificiations. Hope you do not mind.

we are on Sun Solaris / Oracle 9.0.2.6:
my sqlnet.ora does not contain the parameter which you had mentioned. I have only the following and they are similar in most of our instances.

AUTOMATIC_IPC = ON
TRACE_LEVEL_CLIENT = OFF
SQLNET.EXPIRE_TIME = 0
NAMES.DEFAULT_DOMAIN = WORLD
NAME.DEFAULT_ZONE = WORLD
TCP.NODELAY=YES

I created a different unix level account(oa2006) and placed in DBA group and login to the server as that account(oa2006)

login as oa2006 - unix level

sqlplus "/ as sysdba"
shutdown
startup
all works fine.

Becasue oa2006 is in DBA group it is allowing oa2006 to perform tasks like startup / shutdown etc,. even though at oracle level I have not done anything. Is that correct?

I am assuming in your example of connection using scott/tiger
you are referring to creating the account within oracle with
connect, resource, dba and grant sysdba to scott.
And scott is not an OS level account. Is that correct?

I also tried to create few other adminstrative account and I tried to
>connect "myadmin/correctpass" as sysdba - works
>connect "myadmin/wrongpass" as sysdba - even the wrong password this goes in and works
i am wondering why is it like that? Is it because of oslevel account oa2006 is part of DBA group?
It looks like on OS/UNIX level if I am logged on as DBA group user then it allows me to perform sysdba/sysoper type of operations.

There are some users who will not need dba type of access but normal SQL queries. when a such a normal user tries to access he gets following error message.

> sqlplus user11/pass11

SQL*Plus: Release 9.2.0.6.0 - Production on Mon Dec 4 14:37:06 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

ERROR:
ORA-27140: attach to post/wait facility failed

How do I enable users to make SQL queries but prevent from connecting as SYSDBA or SYSOPER?

Can you please shed some light on these? Sorry about the long reply.
Thanks and Regards
Kumar
Re: Oracle start/shutdown with non sysdba account [message #207162 is a reply to message #207146] Mon, 04 December 2006 06:21 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

hi kumar,

for any oracle user if you want to connect as "sysdba" then user should member of "Dba Group".

------------------------------------------------------
ORA-27140: attach to post/wait facility failed
the above error i am clear understand. anyway read below paragraph

ORA-27140: attach to post/wait facility failed

Cause: The program attempted to initialize the post/wait facility, but the facility could not be attached.

Action: Check for additional errors and contact Oracle Support.
or
bcoz user is not dba group member.

Try to connect like "sql>hr/hr@tns_entry"


-----------------------------------------------------

Re: Oracle start/shutdown with non sysdba account [message #207223 is a reply to message #207146] Mon, 04 December 2006 13:41 Go to previous message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> And scott is not an OS level account. Is that correct?
Yes. SCOTT is the database user.
>> It looks like on OS/UNIX level if I am logged on as DBA group user then it allows me to perform sysdba/sysope
Yes. That is the whole idea right? Only DBAs should be added to the DBA group.

>>ORA-27140: attach to post/wait facility failed
This is to do with filesystem permissions on $ORALCE_HOME/bin
Login as the 'oracle' account (or the account with which oracle binaries are installed.
goto $ORACLE_HOME/bin
chmod 6751 account_used_to_install
Previous Topic: droping the queue tables
Next Topic: password required to login as sysdba
Goto Forum:
  


Current Time: Fri Dec 09 11:30:48 CST 2016

Total time taken to generate the page: 0.08106 seconds