| Oracle start/shutdown with non sysdba account [message #206899] |
Sat, 02 December 2006 02:07  |
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  |
 |
Mahesh Rajendran
Messages: 10707
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 #207146 is a reply to message #206899] |
Mon, 04 December 2006 05:27 |
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 #207223 is a reply to message #207146] |
Mon, 04 December 2006 13:41 |
|
Mahesh Rajendran
Messages: 10707
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
