Last password change

posted Jun 17, 2011, 7:53 AM by Sachchida Ojha
The Oracle Database records password change dates and can be shown by querying the SYS.USER$ table, the column PTIME contains the timestamp of the last password change.

SQL> select name, ptime from sys.user$;

To check password controls you can query the DBA_PROFILES and DBA_USERS tables.

SQL> select a.username, a.profile, b.resource_name, b.limit
from dba_users a, dba_profiles b
where b.resource_type = 'PASSWORD'
and a.profile = b.profile;

This will retrieve password controls set for each user

PULINB SOX_PW_PROFILE PASSWORD_LIFE_TIME 90
PULINB SOX_PW_PROFILE PASSWORD_REUSE_MAX 6
PULINB SOX_PW_PROFILE PASSWORD_VERIFY_FUNCTION DEFAULT
PULINB SOX_PW_PROFILE PASSWORD_REUSE_TIME DEFAULT
PULINB SOX_PW_PROFILE PASSWORD_LOCK_TIME 9999
PULINB SOX_PW_PROFILE PASSWORD_GRACE_TIME 10
PULINB SOX_PW_PROFILE FAILED_LOGIN_ATTEMPTS 3

The above Oracle Profile is for an Application, I would propose a DBA_PROFILE for DBA accounts.

SQL> CREATE PROFILE "DBA_PROFILE" LIMIT
PASSWORD_LIFE_TIME 90
FAILED_LOGIN_ATTEMPTS 5;

And then assign every DBA account (not SYS) to this profile;

SQL> ALTER USER username PROFILE DBA_PROFILE;
Comments