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

You May Also Like

ERROR: No checkpoint table specified for ADD REPLICAT
Setting up Table replication in oracle goldengate
Shell script to report failed login attempt in oracle
ORA-32773: operation not supported for smallfile tablespace
How to move AUD$ table to another tablespace using DBMS_AUDIT_MGMT
Clone a database using dbca command in oracle 19c -New feature
How to generate ADDM report
ORA-01536: space quota exceeded for tablespace
Shell script to delete old archives using RMAN
Display sql_id of the sql, using set feedback in oracle 18C - New feature

From This Website

Find pending distributed pending transactions in oracle
Oracle database Security Assessment Tool
How to create user in MySQL Database
MySQL Replication and its Types
SET ROWLIMIT in oracle 18C - New feature
ORA-20101: TABLESPACE is not a ASSM (Automatic Segment Space Management) tablespace
Perform Flashback in pluggable database(PDB) in oracle 12.2
Shared Pool Advisory in oracle
LOG_CHECKPOINTS_TO_ALERT parameter in oracle
Apply database proactive bundle patch in RAC using manual process