Requirement:

Configure a shell script in crontab, that will send alert to DB support Team, in the case of any invalid login attempts in the database.

 

1. First, enable audit for create session

SQL> audit create session;

Audit succeeded.

2. Final shell script

Below script for any invalid login attempts in last 15 minutes.

cat /export/home/oracle/invalid_log.sh

export ORACLE_HOME=/oracle/app/oracle/product/12.1.0/dbhome_1
export ORACLE_SID=SBIP18DB
export PATH=$ORACLE_HOME/bin:$PATH
logfile=/export/home/oracle/test.log
sqlplus -s "/as sysdba" > /dev/null << EOF 
spool $logfile 
set pagesize 1299
set lines 299 
col username for a15 
col userhost for a13 
col timestamp for a39
col terminal for a23 
SELECT username,userhost,terminal,to_char(timestamp,'DD/MM/YY HH24:MI:SS' ) "TIMESTAMP" , 
CASE 
when returncode=1017 then 'INVALID-attempt' 
when returncode=28000 then 'account locked' 
end "FAILED LOGIN ACTION" 
FROM dba_audit_session where timestamp > sysdate-1/9and returncode in (1017,28000);
spool off
exit
EOF
count=`cat $logfile|wc -l`
#echo $count
if [ $count  -ge 4 ];
 then
  mailx -s "INVALID ATTEMPS IN DB  " [email protected] < $logfile
fi

3. provide proper permission:

chmod 755 invalid_log.sh

4. Configure in crontab:

0,15,30,45 * * * * /export/home/oracle/invalid_log.sh

5. SAMPLE OUTPUT:

USERNAME        USERHOST      TERMINAL                TIMESTAMP                               FAILED LOGIN AC              
--------------- ------------- ----------------------- --------------------------------------- ---------------                       
RAJ             sec60-1       pts/1                   06/06/17 13:04:33                       INVALID-attempt                                   
RAJ             sec60-1       pts/1                   06/06/17 13:04:39                       INVALID-attempt                                   
RAJ             sec60-1       pts/1                   06/06/17 13:05:05                       INVALID-attempt                                   
RAJ             sec60-1       pts/1                   06/06/17 13:11:34                       INVALID-attempt                                   
RAJ             sec60-1       pts/1                   06/06/17 13:11:38                       INVALID-attempt                                   
RAJ             sec60-1       pts/1                   06/06/17 13:11:45                       INVALID-attempt                                   
RAJ             sec60-1       pts/1                   06/06/17 13:11:48                       INVALID-attempt                                   
RAJ             sec60-1       pts/1                   06/06/17 13:11:51                       INVALID-attempt                                   
RAJ             sec60-1       pts/1                   06/06/17 13:11:55                       INVALID-attempt                                   
RAJ             sec60-1       pts/1                   06/06/17 13:12:00                       INVALID-attempt                                   
RAJ             sec60-1       pts/1                   06/06/17 13:12:03                       INVALID-attempt                                   
RAJ             sec60-1       pts/1                   06/06/17 13:12:06                       INVALID-attempt                                   
RAJ             sec60-1       pts/1                   06/06/17 13:14:57                       INVALID-attempt                                   
RAJ             sec60-1       pts/1                   06/06/17 13:15:02                       INVALID-attempt                                   
RAJ             sec60-1       pts/1                   06/06/17 13:15:05                       INVALID-attempt                                   
RAJ             sec60-1       pts/1                   06/06/17 13:15:09                       account locked                                        
RAJ             sec60-1       pts/1                   06/06/17 13:33:09                       account locked                                        

17 rows selected.

SHELL script SHELL SCRIPT