Monitor Failed Login Attempts

Add W3Buddy as a preferred source on Google

हि En

🔊 Listen

This script reports failed login attempts (like ORA-1017 and ORA-28000) by querying the DBA_AUDIT_SESSION view. It scans for invalid logins in the last 15 minutes and sends an alert email to the DBA team, enabling quick security checks and response.

📜 Script: invalid_log.sh

################################################################################
# Title   : Monitoring Failed Login Attempts in Oracle DB
# Script  : invalid_log.sh
# Purpose : Checks for invalid login attempts in the last 15 minutes and
#           sends an email alert if any are found.
#
# Features:
#   - Detects invalid credentials and locked accounts
#   - Lightweight and cron-compatible
#   - Helpful in detecting brute-force or suspicious login activity
#
# Author  : W3Buddy
# Version : 1.0
################################################################################

#!/bin/bash

# --- Environment Variables ---
export ORACLE_HOME=/oracle/app/oracle/product/12.1.0/dbhome_1
export ORACLE_SID=SBIP18DB
export PATH=$ORACLE_HOME/bin:$PATH

# --- Log File ---
logfile=/export/home/oracle/invalid_login_alert.log

# --- SQL Execution ---
sqlplus -s "/as sysdba" > /dev/null <<EOF
spool $logfile
SET PAGESIZE 1299
SET LINESIZE 299
COL username FOR a15
COL userhost FOR a13
COL terminal FOR a23
COL timestamp FOR a39

SELECT
  username,
  userhost,
  terminal,
  TO_CHAR(timestamp, 'DD/MM/YY HH24:MI:SS') AS "TIMESTAMP",
  CASE
    WHEN returncode = 1017 THEN 'INVALID LOGIN'
    WHEN returncode = 28000 THEN 'ACCOUNT LOCKED'
  END AS "FAILED LOGIN ACTION"
FROM
  dba_audit_session
WHERE
  timestamp > SYSDATE - 1/96
  AND returncode IN (1017, 28000);
spool off
exit
EOF

# --- Email Alert ---
if [ $(grep -c "FAILED LOGIN ACTION" $logfile) -gt 0 ]; then
  mailx -s "🚨 Oracle DB Invalid Login Attempts Detected" info.w3buddy@gmail.com < $logfile
fi

⚙️ Setup Instructions

# ----------------------------------------------
# 1. Enable auditing (run once in SQL*Plus)
# ----------------------------------------------
SQL> audit create session;

# ----------------------------------------------
# 2. Create the script file
# ----------------------------------------------
cd /export/home/oracle
vi invalid_log.sh

# (Paste the full script above into the file and save)

# ----------------------------------------------
# 3. Make the script executable
# ----------------------------------------------
chmod +x invalid_log.sh

# ----------------------------------------------
# 4. Test the script manually
# ----------------------------------------------
./invalid_log.sh

# Review the output:
cat /export/home/oracle/invalid_login_alert.log

# ----------------------------------------------
# 5. Schedule with cron (every 15 minutes)
# ----------------------------------------------
crontab -e

# Add this line:
0,15,30,45 * * * * /export/home/oracle/invalid_log.sh >> /tmp/invalid_login_cron.log 2>&1

# ----------------------------------------------
# 6. Confirm the cron entry
# ----------------------------------------------
crontab -l