Mail notification about oracle unix account expiry and lock out status
- Purpose of this script - Notify DB admins about oracle account expiry and lock out status- The list of user needs to be declared in the variable IDS section
- Start receiving a mail, from the 14th day prior to the password expiry
- Will receive mail if the account is locked out or any other error ( LK / NL / NP / UN / UP / )
- Modify as per your requirement IDS , MAILID , DAYS
- Schedule the script in cron to run daily by 00:00 AM
- The Result in mail will be any one of the following
oracle - Password NON expiry has set - Against COMPANY password policy - Contact UNIX Support team by raising a WO OR
oracle - Password expire in 3 days - Last change is 25-Nov-2016 - Change the password ASAP OR
oracle - Password expired 2 days ago - Change the password ASAP OR
oracle - Account is locked - Contact UNIX Support team by raising a WO
#!/bin/ksh
#########################
#########################
# About : Notify DATABASE team about oracle account expiry and lock status
# Author : Roselin John (roselinjohn@gmail.com)
# Version : 1.2
# The user list needs to be defined in variable "IDS"
#########################
#########################
#### Variable clasification
# EPOCH - Find the epoch time since the user's password was last changed
# ASTATE - Account status
# CURRENT_EPOCH - Users password age from shadow file in epoch seconds
# AGE - Compute the age of the user's password
# MAX - Max password age per user
# EXPIRE - how many days remaining to expire
DATABASE_ACCOUNTS () {
TOLIST="oracledba@company.com"
CCLIST="unixadmin@company.com"
IDS='oracle oracledba'
OUTFILE=/var/tmp/oraValication
> $OUTFILE
> $OUTFILE.1
EPOCH=`/bin/perl -e 'print int(time/(60*60*24))'`
for USER in $IDS ; do
ASTATE=`passwd -s $USER | awk '{print $2}'`
TFIELD=`passwd -s $USER | awk '{ total = total + NF }; END { print total+0 }'`
CURRENT_EPOCH=`grep $USER /etc/shadow | cut -d: -f3`
if [ "$ASTATE" = "LK" ] ; then
echo "$USER \t - \tAccount is locked - Contcat UNIX Support team by raising a WO" >> $OUTFILE
elif [ "$ASTATE" = "NL" ] ; then
echo "$USER \t - \tThe account is a no login account - Contcat UNIX Support team by raising a WO" >> $OUTFILE
elif [ "$ASTATE" = "NP" ] ; then
echo "$USER \t - \tAccount has no password - Contcat UNIX Support team by raising a WO" >> $OUTFILE
elif [ "$ASTATE" = "UN" ] ; then
echo "$USER \t - \tThe data in the password field is unknown - Contcat UNIX Support team by raising a WO" >> $OUTFILE
elif [ "$ASTATE" = "UP" ] ; then
echo "$USER \t - \tThis account not activated - Contcat UNIX Support team by raising a WO" >> $OUTFILE
elif [ "$ASTATE" = "PS" ] ; then
if [ "$TFIELD" -eq 2 ] ; then
echo "$USER \t - \tPassword NON expiry has set - Against COMPANY passsord policy - Contcat UNIX Support team by raising a WO" >> $OUTFILE
else
FORCED=`passwd -s $USER | awk '{print $3}'`
if [ "$FORCED" = "00/00/00" ] ; then
echo "$USER \t - \tPassowrd change is in force at next login" >> $OUTFILE
else
# Compute the age of the user's password
AGE=`echo $EPOCH - $CURRENT_EPOCH | /bin/bc`
MAX=`grep $USER /etc/shadow | cut -d: -f5`
EXPIRE=`echo $MAX - $AGE | /bin/bc`
#CHANGE=`echo $CURRENT_EPOCH + 1 | /bin/bc`
CHANGE=`echo $CURRENT_EPOCH | /bin/bc`
LSTCNG="`perl -e 'print scalar localtime('$CHANGE' * 24 *3600);'`"
LSTCNGD=`echo $LSTCNG | awk '{print $3"-"$2"-"$5}'`
if [ "$EXPIRE" -le 0 ] ; then
echo "$USER \t - \tPassword expired `echo $EXPIRE| sed 's/\-//g'` days ago - Change the password ASAP" >> $OUTFILE
elif [ "$EXPIRE" -le 14 ] ; then
echo "$USER \t - \tPassword expire in $EXPIRE days - Last change is `echo $LSTCNGD` - Change the password ASAP" >> $OUTFILE
fi
fi
fi
fi
done
if [ -s "$OUTFILE" ] ; then
echo "Validated unix accounts in server `uname -n` are :: $IDS
=========================================================
" >> $OUTFILE.1 ; cat $OUTFILE >> $OUTFILE.1
unix2dos "$OUTFILE.1" "$OUTFILE.ora.dosfile"
mailx -s "`uname -n` - Oracle - UNIX Account Status" -c $CCLIST $TOLIST, < $OUTFILE.ora.dosfile
#cat $OUTFILE.ora.dosfile
rm $OUTFILE.ora.dosfile
fi
rm $OUTFILE.1 $OUTFILE
}
DATABASE_ACCOUNTS
The Result in mail will be any one of the below
oracle - Password NON expiry has set - Against COMPANY password policy - Contact UNIX Support team by raising a WO OR
oracle - Password expire in 3 days - Last change is 25-Nov-2016 - Change the password ASAP OR
oracle - Password expired 2 days ago - Change the password ASAP OR
oracle - Account is locked - Contact UNIX Support team by raising a WO
