Remember your dreams

Remember your dreams
Remember your dreams

Wednesday, February 5, 2020

Oracle - Check for failed login attempts

A great security measure is to monitor who is trying to unsuccessfully log into your database. Whether it's an innocent developer who forgot their password, someone trying to use an account that is not their's, or worse - some outside intrusion hacker or snooper just trying to get in. You should be aware when it happens so you can understand why and put an end to it. I like to run this scripts through cron several days a week. A quick look at it ensures me there are no outsiders or alerts me to such.

Script consists of a cron script and an sql script on the host server.
failed_logins.sh

#!/bin/tcsh -f
source ~oracle/.cshrc
setenv VDATE `date '+%m/%d/%y %A %X'`
setenv MAILGRP "email@company.com"
setenv scrpt [script location]

sqlplus -SILENT user/password@ORCL < ${scrpt}/failed_logins.sql

cat failed_logons.out | /bin/mailx -s "${ORACLE_SID} - `uname -n` : Logins attempted Report for $VDATE" \
$MAILGRP
exit

SQLPLUS script
failed_logins.sql

set termout off
spool failed_logons.out
set linesize 232 tab off trimspool on
set pagesize 105
set pause off
set echo on
set feedb off
set heading off

column "EVENT_TIMESTAMP" format a40
column "OS_USERNAME" format a15
column "USERHOST" format a15
column "DBUSERNAME" format a10
column "CLIENT_PROGRAM_NAME" format a35
column "ACTION_NAME" format a10
column "UNIFIED_AUDIT_POLICIES" format a20

select 'Login Report for '||TO_CHAR(sysdate,'DAY DD-MON-YYYY HH24:MI:SS') "Title" from dual
/

select 'EVENT_TIMESTAMP------------------OS_USERNAME--USERHOST---DBUSERNAME--CLIENT_PROGRAM_NAME----ACTION_NAME--POLICY' from dual;
                                                             
SELECT TO_CHAR(event_timestamp,'YYYY-MM-DD HH24:MI:SS') EVENT_TIMESTAMP,
os_username OS_USERNAME,
userhost USERHOST,
dbusername DBUSERNAME,
client_program_name CLIENT_PROGRAM_NAME,
action_name ACTION_NAME,
unified_audit_policies UNIFIED_AUDIT_POLICIES
FROM unified_audit_trail
where action_name = 'LOGON'
and unified_audit_policies = 'ORA_LOGON_FAILURES'
and trunc(event_timestamp) > trunc(sysdate)-10
ORDER BY event_timestamp desc
/
spool off

No comments:

Post a Comment

Proactive Oracle DBA

This is a series of posts. I am working this to share some of the many scripts I schedule to automatically run to alert me of any current o...