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


Related Topics

Shell script to monitor lag in standby datbase using dgmgrl
Shell script to monitor asm diskgroup usage
Shell script to report failed login attempt in oracle
Shell script to delete old archives using RMAN
Alert log rotation script in oracle
Shell script for monitoring Alert log
shell script for file system alert
Tablespace monitoring shell script
Shell script for monitoring blocking sessions
Shell script to monitor goldengate process

You May Also Like

Oracle database Security Assessment Tool
Flashback primary database in dataguard environment
ENABLE_PARALLEL_DML hint in oracle 12c
SGA TARGET ADVISORY IN ORACLE
How to install trace file analyzer( TFACTL)
How to move controlfile to a new location in oracle
how to send mail using utl_mail in oracle 11g
How to install oracle client in silent mode using response file
OUTBOUND_DBLINK_PROTOCOLS in oracle 12.2
How to enable flash recovery area in oracle database

From This Website

How to find cpu and memory information of oracle database server
resumable_timeout parameter in oracle database
Shell script to monitor lag in standby datbase using dgmgrl
Find pending distributed pending transactions in oracle
Exception in thread -main" java.lang.OutOfMemoryError: GC overhead limit exceeded with bsu.sh
shell script for file system alert
How to change the case (Lower to Upper and Vice Versa) in R
Apply database proactive bundle patch in RAC using manual process
How to install postgres database on mac os/linux
Tablespace monitoring shell script