Wednesday

Fusion Middleware and 11g DB Password Expiry

As a few DBA’s have noticed, the 11g database has password expiry enabled.  This is not entirely a bad thing, I am in favor of this move.  However, if your not aware of this change then it can cause you some problems with your Fusion Middleware (FMW) 11g environment.

Developers contacted me with

ORA-28001: the password has expired.  

Originally I didn’t even think of the repository accounts being an issue.  I assumed it was a password policy in Oracle Internet Directory (OID) or WebLogic accounts they created for deploying applications.     After those were verified, then the only thing left was the database.

A quick look at dba_users showed a couple of accounts already expired or in grace status:

USERNAME                       ACCOUNT_STATUS                   LOCK_DATE EXPIRY_DA
------------------------------ -------------------------------- --------- ---------
DCM                            EXPIRED                                    12-MAY-10
ORASSO_PS                      EXPIRED                                    10-MAY-10
DEV_PORTAL                     EXPIRED                                    10-MAY-10
ODSSM                          EXPIRED                                    10-MAY-10
ORASSO                         EXPIRED(GRACE)                             20-MAY-10



You can view the password policy of the database default profile by looking at dba_profiles:

SQL> select * from dba_profiles;


PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT
------------------------------ -------------------------------- -------- ---------------------
DEFAULT                        FAILED_LOGIN_ATTEMPTS            PASSWORD 10
DEFAULT                        PASSWORD_LIFE_TIME               PASSWORD 180
DEFAULT                        PASSWORD_REUSE_TIME              PASSWORD UNLIMITED
DEFAULT                        PASSWORD_REUSE_MAX               PASSWORD UNLIMITED
DEFAULT                        PASSWORD_VERIFY_FUNCTION         PASSWORD NULL
DEFAULT                        PASSWORD_LOCK_TIME               PASSWORD 1
DEFAULT                        PASSWORD_GRACE_TIME              PASSWORD 7


I personally do not like to have password expiry setup for database level application accounts.   In most cases the passwords for these accounts can’t be changed without downtime, so its best to have a policy were once a quarter (or whatever your corporate standards are) to manually change these passwords.

Since individual end users do not have their own database level accounts I modified the default profile.    If this is not the case for your server, you may want to create a new profile for application users so that you can have separate password policies.

The command to alter the default profile is:

SQL> alter profile default limit PASSWORD_LIFE_TIME unlimited FAILED_LOGIN_ATTEMPTS unlimited;




The next task was to re-enable expired accounts.   To do this the password for these accounts need to be changed manually and I would recommend reusing the same password.  One thing I need to do is investigate password changes for FMW accounts and see if there are any dependencies.   NOTE:  If PASSWORD_REUSE_MAX is not set to UNLIMITED you may not be able to reuse the previous password.

Remember back to the Fusion Middleware installation, you were prompted to create passwords for a number of repository accounts.  If any of these accounts have expired either issue the

alter user <username> identified by <password>;


or login as each user and you’ll be prompted for a new password.  As noted above, use the previous password.

You may notice or find out the hard way, that you don’t have the passwords for some of these accounts.    If you take a look at the DBA_USERS query above you’ll notice the ORASSO, ORASSO_PS, and DCM users.    When these accounts are created they are assigned random passwords.   Use ldapsearch, changing the OrclResourceName parameter for each account you need to find the password for:

[oracle@myserver ~]$ ldapsearch -b "orclReferenceName=<SID>.world,cn=IAS Infrastructure Databases,cn=IAS,cn=Products,cn=OracleContext" -D cn=orcladmin -h <OID Server> -p 3060 -q OrclResourceName=ORASSO

Please enter bind password:
OrclResourceName=ORASSO,orclReferenceName=MYDB.world,cn=IAS Infrastructure Databases,cn=IAS,cn=Products,cn=OracleContext
orclflexattribute1=true
orclresourcename=ORASSO
objectclass=orclResourceDescriptor
objectclass=top
orclpasswordattribute=AECR63ZP



The current password is identified by orclpasswordattribute. Now you can reset the password for these accounts as you did with the others above.

3 comments:

Ingo said...

It'd be easier using the encrypted password from sys.user$

Saves you a lot of time having to look up passwords all over the place.

The following worked for me (the date in the query is arbitrary):

select 'alter user ' || d.username || ' identified by values ''' || s.password || ''';' from dba_users d, sys.user$ s where expiry_date > trunc(sysdate,'mm')
and s.name = d.username;

Dave said...

Hey, I didn't even think of using the encrypted value.. Much easier! Tnx!

Sridevi Koduru said...

Regards
Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, Oracle Manufacturing, OAF, ADF, SQL, PL/SQL, D2K at sridevikoduru@oracleappstechnical.com | +91 - 9581017828.