Saturday, August 18, 2007

Mike Shaw's 10 Tips for protecting your 'APPS' password

10 Tips for protecting your 'APPS' password - is a very nice post by Oracle Corp - Mike Shaw. Out of the 10 tips in the post by M.Shaw, #7 & #10 are my favourites.

#7. Ensure no processes are running with APPS username/password in command line - This is very important as Apps DBAs are always tempted to use sqlplus apps/apps-password at unix command prompt. Any other users who are not supposed to know the apps password but have unix access can easily find out 'APPS' password by simply firing the command 'ps -ef | grep -i apps'. So, Apps DBAs - Watch out !! :)

#10. Allow only specific IP addresses to access RDBMS via SQLNET - In large enterprises the IP traffic is controlled using firewall. It is always a good idea to allow traffic from a combination of Midlle tier IP address + DB Port #.

Over a period of time the implemented protection has to be maintained. A typical Apps DBA's work life is so busy with Patches, User Calls, Upcoming Project dead lines etc., by the time he realizes that there are so many things to do , the clock ticks 07:00PM. It is time to go home. So, taking time out to proactively go and check the protection is little not practical. This is where monitoring comes into picture.

What needs to be monitored ?

It is always said that you need to be paranoid to monitor something that needs to be protected. The following are some tips I can think of to protect the 'APPS' password not to fall in wrong hands. Even if it falls, how to catch it fast.

#1. The most obvious one is to ensure $APACHE_TOP/Apache/modplsql/ & $ORACLE_HOME/reports60/server/CGIcmd.dat has 700 permission. Let a monitoring script check at regular intervals for expected permissions and send e-mail/SMS alerts.

#2. There is always one place that the 'APPS' password gets recorded (Even best of the best security guides will fail to document this place) which is .sh_history or .bash_history depending on the default shell type of applmgr unix user account. It is always a good practice to clear the history upon logout as the way to stop Oracle Apps is to use apps/apps-password.

bash shell (bash)- "~/.bash_logout" - this file called by bash shell upon logout. Place "rm $HOME/.bash_history" to clear the history upon logout.

korn shell (ksh) - In korn shell I think there is no file that automatically gets called during the logout process. So, alias 'exit' to 'alias exit='rm $HOME/.sh_history; exit'.

#3. Recently I learnt that, sql sessions to 'APPS' can be monitored as well. I think this is the best way to check the 'APPS' database sessions. So, have the following script in place to check 'APPS' Database sessions for unauthorized access.

Script Courtesy : My fellow Apps DBAs.

select s.sid "SID" , s.serial# "SERIAL#", s.username dbuser, s.osuser,s.machine "MACHINE", s.terminal "TERMINAL", to_char(s.logon_time,'DD-MON-YYYY HH24:MI:SS') logged_in,s.program , s.module from v$session s where s.username = 'APPS' and s.module in ('TOAD.exe','PL/SQLDeveloper','SQL*Plus') and lower(s.osuser) not like '%osusername' and lower(s.osuser) not in ('oracle user','applmgr user');


Anonymous said...

Great article, Madhu. Keep up the good work.

Steven Chan

Madhu Sudhan said...

Thanks Steven.

Anonymous said...

Good Article Madhu. I got to see this, when i was searching for how else, i can get the apps password.
Guess who i am?

Madhu Sudhan said...

How could I possibly know you if you comment as Anonymous. :)

Don't forget to leave your signature next time. :)