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/wdbsvr.app & $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 adstapll.sh 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');

vi for Apps DBAs

I intend to start of my first Blog with "vi for DBAs". I used to receive queries ( not sql queries :) ) from fellow DBAs on how to search and replace in "vi". A collection of complex (which I think are) search and replace commands of vi that I came across are summarized below. These commands are useful for DBAs in their day-to-day Administration.

#1. Once upon a time a user sent me an excel sheet with 100 tables in it. Requesting me to grant INSSERT,UPDATE & DELETE privileges for two different users. Just imagine the complexity if you are not familiar with vi. I opened up "vi" (my favourite editor), snipped & pasted all the 100 tables into a file. Now my requirement is to generate a grant sql script with the following sql query.

SQL> select 'grant insert on' table_name 'to xyz user' from dba_tables where table_name in ('TABLE1','TABLE2',.....)

Just to high-light every table in the above sql need to be quoted using ' ' and separated by a ",". To achieve this for all the tables that are already copied into to a file I used the following command.

:1,$ s/.*/'&',/g

Explanation:

".*" - indicates any text/pattern in the line. "&" - indicates the text that is already in the line. So, if the line has TABLE1 it will be replaced by 'TABLE1', (notice the quote and comma).

#2. How to search and replace a text containing lots of "/"s.

Assume the search string is "/prod1/applmgr/prod1appl" and replace string is "/test1/applmgr/test1appl". If you use the syntax :1,$ s//prod1/applmgr/prod1appl/test1/applmgr/test1appl/g. Obviously "vi" is going to get confused and errors out. Because it will not know which "/" is part of the search string and which "/"is a delimiter that is part of the search-replace syntax.

So, use the syntax mentioned below which will make you smile.

:1,$s#/prod1/applmgr/prod1appl#/test1/applmgr/test1appl#g.
"#" is part of the search-replace syntax. In other words you can use any character as a delimiter as long as the character that you are using as delimeter is not part of the search/replace string. See, how flexible the "vi" is....

#3. How to do a copy and paste across files ?

Assume, we have two files a.txt and b.txt. Our goal is to copy the text from a.txt and paste it onto b.txt. In "vi" you can define buffers and even name them. Surprising isn't it !!. Yes you can. Where each buffer can be named after numbers 1-9 or alphabets a-i. So,follow the procedure mentioned below to copy and paste across files.

#1. First step in this process of search and replace is to define a buffer and put some content into it.
#2. vi a.txt and "a2yy (Remember #1. Buffer Name can be a-i or 1-9). So, in this case I have chosen buffer name to be "a". 2yy indicates 2 lines to be copied/yanked. #3. Now open b.txt by using :e b.txt - once b.txt is opened, go to the line you would like to have the lines pasted and type "ap. This will recall the contents of buffer "a" and the contents will be pasted.

Tip: Must be wondering how to remember the syntax ? It is easy. All you need to do is use it couple of times.

Enjoy !! & Happy vi'ing.