{"id":4283,"date":"2025-06-17T09:04:27","date_gmt":"2025-06-17T09:04:27","guid":{"rendered":"https:\/\/w3buddy.com\/?post_type=cposts&#038;p=4283"},"modified":"2025-06-17T09:04:28","modified_gmt":"2025-06-17T09:04:28","slug":"monitor-failed-login-attempts","status":"publish","type":"cposts","link":"https:\/\/w3buddy.com\/blog\/notes\/shell-scripts\/monitor-failed-login-attempts\/","title":{"rendered":"Monitor Failed Login Attempts"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">This script reports failed login attempts (like <code>ORA-1017<\/code> and <code>ORA-28000<\/code>) by querying the <code>DBA_AUDIT_SESSION<\/code> 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.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">\ud83d\udcdc Script: <code>invalid_log.sh<\/code><\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>################################################################################\n# Title   : Monitoring Failed Login Attempts in Oracle DB\n# Script  : invalid_log.sh\n# Purpose : Checks for invalid login attempts in the last 15 minutes and\n#           sends an email alert if any are found.\n#\n# Features:\n#   - Detects invalid credentials and locked accounts\n#   - Lightweight and cron-compatible\n#   - Helpful in detecting brute-force or suspicious login activity\n#\n# Author  : W3Buddy\n# Version : 1.0\n################################################################################\n\n#!\/bin\/bash\n\n# --- Environment Variables ---\nexport ORACLE_HOME=\/oracle\/app\/oracle\/product\/12.1.0\/dbhome_1\nexport ORACLE_SID=SBIP18DB\nexport PATH=$ORACLE_HOME\/bin:$PATH\n\n# --- Log File ---\nlogfile=\/export\/home\/oracle\/invalid_login_alert.log\n\n# --- SQL Execution ---\nsqlplus -s \"\/as sysdba\" > \/dev\/null &lt;&lt;EOF\nspool $logfile\nSET PAGESIZE 1299\nSET LINESIZE 299\nCOL username FOR a15\nCOL userhost FOR a13\nCOL terminal FOR a23\nCOL timestamp FOR a39\n\nSELECT\n  username,\n  userhost,\n  terminal,\n  TO_CHAR(timestamp, 'DD\/MM\/YY HH24:MI:SS') AS \"TIMESTAMP\",\n  CASE\n    WHEN returncode = 1017 THEN 'INVALID LOGIN'\n    WHEN returncode = 28000 THEN 'ACCOUNT LOCKED'\n  END AS \"FAILED LOGIN ACTION\"\nFROM\n  dba_audit_session\nWHERE\n  timestamp > SYSDATE - 1\/96\n  AND returncode IN (1017, 28000);\nspool off\nexit\nEOF\n\n# --- Email Alert ---\nif &#91; $(grep -c \"FAILED LOGIN ACTION\" $logfile) -gt 0 ]; then\n  mailx -s \"\ud83d\udea8 Oracle DB Invalid Login Attempts Detected\" info.w3buddy@gmail.com &lt; $logfile\nfi<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">\u2699\ufe0f Setup Instructions<\/h2>\n\n\n\n<pre class=\"wp-block-code\"><code># ----------------------------------------------\n# 1. Enable auditing (run once in SQL*Plus)\n# ----------------------------------------------\nSQL> audit create session;\n\n# ----------------------------------------------\n# 2. Create the script file\n# ----------------------------------------------\ncd \/export\/home\/oracle\nvi invalid_log.sh\n\n# (Paste the full script above into the file and save)\n\n# ----------------------------------------------\n# 3. Make the script executable\n# ----------------------------------------------\nchmod +x invalid_log.sh\n\n# ----------------------------------------------\n# 4. Test the script manually\n# ----------------------------------------------\n.\/invalid_log.sh\n\n# Review the output:\ncat \/export\/home\/oracle\/invalid_login_alert.log\n\n# ----------------------------------------------\n# 5. Schedule with cron (every 15 minutes)\n# ----------------------------------------------\ncrontab -e\n\n# Add this line:\n0,15,30,45 * * * * \/export\/home\/oracle\/invalid_log.sh >> \/tmp\/invalid_login_cron.log 2>&amp;1\n\n# ----------------------------------------------\n# 6. Confirm the cron entry\n# ----------------------------------------------\ncrontab -l<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>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. \ud83d\udcdc Script: invalid_log.sh \u2699\ufe0f Setup Instructions<\/p>\n","protected":false},"template":"","meta":{"googlesitekit_rrm_CAowu461DA:productID":""},"categories":[952,990],"class_list":["post-4283","cposts","type-cposts","status-publish","hentry","category-notes","category-shell-scripts"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts\/4283","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/cposts"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=4283"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=4283"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}