Thursday, February 28, 2008

Password expiring Alert

Following script will help you to get an alert if users have password longer than certain days. Lets say you set an alert for password to expire in 180 days and you want to sent an alert after 150 days:

. .env

cd /tmp
sqlplus -S /nolog <<>
connect apps/$APPSPASS
set feedback off
set pages 100
set lines 100
col "username" format a15
col "account_status" format a15
col "profile" format a15
col "LAST_PASSWD_DATE" format a16
col "CHANGE BY" format a16
spool /tmp/userpassword.log

select /*+ RULE */ dd.username,dd.account_status,dd.profile,
dd.created "CREATION_DATE", u.ptime "LAST_PASSWD_DATE",u.ptime+180 "CHANGE BY"
from dba_users dd, sys.user$ u
where dd.USERNAME = u.NAME
and u.ptime <>
and dd.account_status = 'OPEN'
and dd.USERNAME != 'APPLSYSPUB' -- Exception as this account should have the default password
order by dd.created,u.username
spool off
if [ -s /tmp/userpassword.log ] ; then
cat /tmp/userpassword.log mailx -s "Users with Password older than 150 days"

Keep Bugging Users :-)

Keep Troubleshooting !!!

1 comment:

Sudhakar said...


Thanks for the code. However, APPS itself has the alert functionality right? (System->Options) why to use unix mailing functionality?