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 " support@dbaclass.com < $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.
