The following solution shows how you can query two tables in a DB2 database for user IDs that have a password older than 90 days and are in the “LOCKED” group. We will need to join the tables but a simple JOIN won’t do as we get may rows for the same user. This is due to group membership being in a different table named USER_GROUPS and being used not only for LOCKED status, but for membership to other groups. The query below takes care of removing additional results for the same user if they are members of more than one non-LOCKED group.
As a bonus, I have added the EXPORT command so that the result is exported to a comma delimited file (CSV) and included a shell script to automatically schedule and email the results.
NOTE: This was tested in DB2 9.5 and AIX. There might be a better way to do this SQL query in DB2 10+ but this method should work for both.
Database Tables:
Expected Result:
password_check.sql:
connect to db2srv1;
set schema authdb;
EXPORT TO /tmp/password_check.tmp OF DEL MODIFIED BY NOCHARDEL
with tmp_table1 as (SELECT authdb.users.user_id, authdb.users.user_full_name, authdb.users.password_date, 'YES' as LOCKED
FROM authdb.users INNER JOIN authdb.user_groups ON authdb.users.user_id=authdb.user_groups.user_id
WHERE authdb.users.password_date < VARCHAR_FORMAT(CURRENT DATE - 90 DAYS, 'YYYYMMDD')
AND authdb.user_groups.group_name = 'LOCKED'),
tmp_table2 as (SELECT authdb.users.user_id, authdb.users.user_full_name, authdb.users.password_date, '' as LOCKED
FROM authdb.users LEFT OUTER JOIN authdb.user_groups ON authdb.users.user_id=authdb.user_groups.user_id
WHERE authdb.users.password_date < VARCHAR_FORMAT(CURRENT DATE - 90 DAYS, 'YYYYMMDD')
AND authdb.user_groups.group_name <> 'LOCKED' GROUP BY authdb.users.user_id, authdb.users.user_full_name, authdb.users.password_date)
SELECT tmp_table2.user_id, tmp_table2.user_full_name, tmp_table2.password_date, tmp_table1.LOCKED
FROM tmp_table2 LEFT OUTER JOIN tmp_table1 ON tmp_table2.user_id=tmp_table1.user_id;
quit;
Got some inspiration for the SQL above from this StackOverflow post.
Setup the shell script below too run the above SQL code on a schedule and receive the CSV file plus command output via email…
password_check.ksh:
#!/usr/bin/ksh
###################################################################
# Author: VeneDude
# Description:
# This script will capture data from shell script and email results
#
# Sample crontab for root:
# 0 7 * * 2 /home/user/scripts/password_check.ksh >/dev/null 2>&1
###################################################################
xemails="my@email.com"
outfile=/tmp/password_check.txt
echo "" > $outfile
cp -f /home/user/scripts/password_check.sql /tmp/password_check.sql
chown db2srv1 /tmp/password_check.sql
chown db2srv1 $outfile
echo "** START OF DB2 REPORT **" >> $outfile
echo "* Accounts with passwords older than 90 days *" >> $outfile
su - db2srv1 -c "db2 -tf /tmp/password_check.sql >> ${outfile}"
echo "** END OF DB2 REPORT **" >> $outfile
# Just adding column headers to the final CSV file
echo "USER_NAME,USER_FULL_NAME,PASSWORD_DATE,LOCKED" > /tmp/password_check.csv
cat /tmp/password_check.tmp >> /tmp/password_check.csv
# send logs and report via email
#mail -s "Password Age Report" $xemails < $outfile
(cat $outfile; uuencode /tmp/password_check.csv password_check.csv) | mail -s "Password Age Report" $xemails