Friday, November 13, 2009

Last password change date

Our databases recently went through an audit and we were asked to supply a list of all database users and the date when the last password change occurred. My first reaction was that Oracle does not keep that information, but a little research proofed that I was wrong. The information is kept in sys.user$.ptime
The following query will show when last a password was changed:

select du.username, du.profile, du.account_status, u.ptime last_pwd_change
from dba_users du, sys.user$ u
where du.username = u.name
order by 2, 4
/

No comments: