Password Expiration and Profiles

Don’t you just love managing your 3,247 accounts and passwords in cyberspace? Imagine being a DBA who manages several hundred databases and requires a password for each of those. And of course said passwords expire after X days, right?

Password rules, which cover things like

are defined by Profiles. You can view profile information by querying SYS.DBA_PROFILES.

I was approached for help from a follower on Twitter. She needed help figuring out how to change the lifetime of her users passwords. She also mentioned that she was managing an 11g database.

Oracle made a pretty significant change for the DEFAULT profile, in particular the password lifetime parameter for 11g. All passwords now expire after 180 days. You could of course change the setting for the DEFAULT profile back to something very generous (like NEVER), but in today’s IT environment, I’m not sure that would be a wise move.

As a test, I changed the default setting to 365
ALTER PROFILE "DEFAULT" LIMIT
...
PASSWORD_LIFE_TIME 365
...
;

I then queried my users

SELECT USERNAME, EXPIRY_DATE, CREATE
FROM DBA_USERS
WHERE PROFILE = 'DEFAULT'
ORDER BY CREATED DESC;

So even though I changed the default password expiration period, I still have users that are not up to date.

Here’s where it starts to make sense.

I found this excellent writeup on the EXPIRY_DATE column which goes into detail how the figure is DERIVED.

Here’s the Calculus…pay attention to the SYS.USER$.PTIME field (denoted by the u alias)

I also noticed that when I had my PASSWORD_GRACE_TIME set to UNLIMITED, that it appeared that my password was not going to exire, denoted by a NULL value in the USERS view. When I set this to a period of 2 days and re-logged in, the field was updated immediately.

Clear as mud?