prevent ORA-28001?

Question

Using Oracle 11g, is there a way to prevent my user's passwords from needing to be updated (ORA-28001)?

Answer 1

The expiration of passwords in Oracle is controlled by the profile that is assigned to the user. You can see which profile is assigned to each user by querying the DBA_USERS table

SELECT username, profile
  FROM dba_users

You appear to be hitting the PASSWORD_LIFE_TIME limit. Prior to 11g, the DEFAULT profile had this set to UNLIMITED. To improve security, 11.1 started forcing periodic password changes. You can revert to the old behavior either by creating a new profile and assigning that new profile to your existing users or by updating the existing profile that is already assigned to your users. Since it sounds like you want to change this behavior for everyone, it's probably easier to just modify the DEFAULT profile.

ALTER PROFILE default 
 LIMIT password_life_time UNLIMITED

Of course, if the first query indicates that your users are assigned a profile other than DEFAULT, you'd want to alter that profile instead.

If you're interested in what other behaviors a profile controls, the CREATE PROFILE documentation is quite useful.

Answer 2

I have gotten burned so many times that I set them all with:

set pages 60
select * from dba_profiles where profile='DEFAULT';
alter profile default limit FAILED_LOGIN_ATTEMPTS UNLIMITED;
alter profile default limit PASSWORD_LIFE_TIME UNLIMITED;
alter profile default limit PASSWORD_GRACE_TIME UNLIMITED;
alter profile default limit PASSWORD_LOCK_TIME UNLIMITED;
select * from dba_profiles where profile='DEFAULT';