Usually having a database profile should be in place that specifies that a database account become locked after a designated number of failed login attempts.
For example, set the DEFAULT profile FAILED_LOGIN_ATTEMPTS to 5. Sometimes, however, a rogue user or developer will attempt to guess the production account password and, after five attempts, lock the production account.
When this happens, an alert is needed to know about it as soon as possible so that it can be investigated for either a security incident or the issue for the user and then unlock the account.
Schema-only accounts help out with this issue, as the application account is not used for logging in and cannot lock users out.
The following shell script checks the LOCK_DATE value in DBA_USERS for a list of production database accounts:
#!/bin/bash
if [ $# -ne 1 ]; then echo “Usage: $0 SID” exit
# source oracle OS variables . /etc/oraset $1
crit_var=$(sqlplus -s <<EOF / as sysdba
SET HEAD OFF FEED OFF SELECT count(*)
FROM dba_users
WHERE lock_date IS NOT NULL
This shell script is called from a scheduling tool, such as cron. For example, this cron entry instructs the job to run every 10 minutes (this entry should actually be a single line of code but has been placed on two lines to fit on the page):
0,10,20,30,40,50 * * * * /home/oracle/bin/lock.bsh DWREP 1>/home/oracle/bin/log/lock.log 2>&1
In this way, an email notification goes out when one of the production database accounts becomes locked. If the risk level is acceptable, as part of this script, there should be a step to unlock the account after a set amount of time, and it is recorded that there were the failed login attempts.
Checking for Too Many Processes
On some database servers, you may have many background SQL*Plus jobs. These batch jobs may perform tasks such as copying data from remote databases and large daily update jobs.
In these environments, it is useful to know if, at any given time, there are an abnormal number of shell scripts or SQL*Plus processes running on the database server. An abnormal number of jobs could be an indication that something is broken or hung.
The next shell script has two checks in it: one to determine the number of shell
scripts that are named with the extension of bsh and one to determine the number of processes that contain the string of sqlplus:
#!/bin/bash #
if [ $# -ne 0 ]; then echo “Usage: $0” exit 1
The prior shell script, named proc_count.bsh, is run once an hour from a cron job (this entry should actually be a single line of code but is placed on two lines to fit on the page):
33 * * * * /home/oracle/bin/proc_count.bsh 1>/home/oracle/bin/log/proc_count.log 2>&1
Leave a Reply