Tuesday, March 8, 2011

ACCOUNT_STATUS = EXPIRED(GRACE)

Users are identified within the app, so all DB connections are done through 1 schema. We created a profile for this schema where the password will never expire. App support has a manual process to change the password every 60 days, but Oracle must never be allowed to expire the password automatically.

The problem is that I found the ACCOUNT_STATUS for the main schema to be EXPIRED(GRACE) and that the EXPIRY_DATE is 3 days away.

This schemas profile was changed by accident, and rectified within a few hours. But the damage was done the ACCOUNT_STATUS still showed EXPIRED(GRACE).

I found an excellent blog post by Robert Geier (http://blog.contractoracle.com/2009/11/analysis-of-oracle-password-expiry.html) to explain the situation. Here’s a abbreviation of his explanation: password expiry is controlled by the PASSWORD_LIFE_TIME profile limit, is activated at login, and is reset by a password change. Changing the profile will not change aud$.ASTATUS.

SQL> select USERNAME, PROFILE, ACCOUNT_STATUS from dba_users where username = ‘QWERTY’;

USERNAME PROFILE ACCOUNT_STATUS

------------------ ------------------ --------------------------------

QWERTY QWERTY_NONEXPIRY EXPIRED(GRACE)

So even though the profile was changed back to QWERTY_NONEXPIRY the ACCOUNT_STATUS was still EXPIRED(GRACE). The only way to reset the ACCOUNT_STATUS back to OPEN is to change the password, because only a password change will reset aud$.ASTATUS. But a password change will give a ORA-28007 because of the current profile limits for PASSWORD_REUSE_MAX and PASSWORD_REUSE_TIME gets violated.

SQL> select password from sys.user$ where name='QWERTY';

PASSWORD

-----------------------------

87FE2DD49B2882B2

SQL> alter user QWERTY identified by values '87FE2DD49B2882B2';

alter user QWERTY identified by values '87FE2DD49B2882B2'

*

ERROR at line 1:

ORA-28007: the password cannot be reused

So I had to create a new temporary profile with the required settings to allow me to change the password to be the same as the current password:

SQL> create profile tmtmp limit PASSWORD_REUSE_MAX UNLIMITED PASSWORD_REUSE_TIME UNLIMITED;

SQL> alter user QWERTY profile tmtmp;

SQL> alter user QWERTY identified by values '87FE2DD49B2882B2';

User altered.

SQL> alter user QWERTY profile QWERTY_NONEXPIRY;

SQL> select USERNAME, PROFILE, ACCOUNT_STATUS from dba_users where username = 'QWERTY';

USERNAME PROFILE ACCOUNT_STATUS

------------------ ------------------ --------------------------------

QWERTY QWERTY_NONEXPIRY OPEN

5 comments:

Anonymous said...

Neat workaround :-)
Nico Witte

DavidC said...

This works, insomuch as it allows you to reset the password back to what it was. But, strangely, it then doesn't enforce the password_reuse parameters after switching back to a profile that has these two params set - in other words, I can't seem to get an ORA-28007 anymore for this user.

I'm testing on a 11.1.0.7 database.

Derya Oktay said...

Nice workaround, maybe needed in environments where no room for changing passwords, which effects applications servers etc.!

yosabrams0918 said...

After examine a number of of the blog posts in your web site now, and I actually like your way of blogging. I bookmarked it to my bookmark website list and might be checking again soon. Pls take a look at my web page as properly and let me know what you think. gsn casino

gseoa5 said...

Pinwords is a really basic tool, but it’s great if you want to quickly make an image from a quote and add it to your Pinterest account. Pinstamatic actually lets you create a whole bunch of items to add to your Pinterest boards including website snapshots, pins of Twitter profiles and Spotify tracks that you can pin.