I hate annoying "U'v got n more days before your password will expire"

I am sure 75% of you are not so interested in keeping the passwords fresh and sneaking-from-behind-careless, thus it's a very good idea to change all the parameters defining the lifespan of a user password. I am sure u agree with me that the most annoying expiration of any oracle password in this world, the never disputed champion of unlikeability is expiration of the DBSNMP user password.

As far as I could understand from the entirely oracle web community, the general way of overcoming this problem is to make a new profile and to assign any user you want to that profile. From my point of view this is not a perfect approach. First of all, maybe I don't know which the users are, until I won't query the dba tables, and maybe i won't eighter, after. Second, maybe two users can't share the same profile, in some scenarios. My proposal here is to treat equally all the users, and run a script careless of any information from dba_users  and dba_profiles views, a script that will modify all the profiles that are in your database. If the fact having the same passwords related profiles parameters for all the profiles is not a weakness for you, my aproach will be what you were searching for. So, the major advantage is provided by the tiny amount of time involved in the action, also the zero amount of implication in knowing any information about any user or profile in database. Check out the script:

declare
nume_profil  dba_profiles.profile%type;

nume_user dba_users.username%type;
begin

for nume_profil in (select distinct profile from dba_users)

loop

    execute immediate 'ALTER PROFILE '||nume_profil.profile||' limit failed_login_attempts UNLIMITED';

    execute immediate 'ALTER PROFILE '||nume_profil.profile||' limit password_life_time  UNLIMITED';

    execute immediate 'ALTER PROFILE '||nume_profil.profile||' limit password_grace_time unlimited';

    execute immediate 'ALTER PROFILE '||nume_profil.profile||' limit password_lock_time unlimited';

    execute immediate 'ALTER PROFILE '||nume_profil.profile||' limit password_reuse_max unlimited';

    execute immediate 'ALTER PROFILE '||nume_profil.profile||' limit password_reuse_time unlimited'; 

end loop;

/*for nume_user in (select username from DBA_USERS where default_tablespace not in ('SYSTEM','SYSAUX') and instr(username,'$')=0 and instr(account_status,'EXPIRED')<>0) -- avoid system users and the ones whose password is not expired

loop

    execute immediate 'ALTER user '||nume_user.username||' identified by '||nume_user.username;
end loop;*/ 

for nume_user in (select username from DBA_USERS where default_tablespace not in ('SYSTEM','SYSAUX') and instr(username,'$')=0) -- avoid system users
loop
    execute immediate 'ALTER user '||nume_user.username||' account unlock';

end loop;
end;

/  

and BINGO, problem solved effortless and careless. Notice the third loop, which is useful by unlocking any user that is IN THIS MOMENT locked. Unfortunately, it's not a very good ideea to change, also, the EXPIRED state of the account, because you can alter accounts of other people, or critical accounts, used in some applications, etc. Every user of the database should change the password of their managed accounts, following the situation that  after the script will be executed, some accounts will be unlocked for ever but expired. Anyway, if you want to rip the account from the expired state and give it a password identically with the username, you can uncomment the loop above, the only one commented. But, i repeat, 90% it will prove to be a silly thing.

Check out the DISTINCT keyword, it's not dramatically important, but is there in honor of performance.

Don't forget to check once again, and make this a generale rule of your dba way of thinking: Check allways twice!!

select * from DBA_PROFILES order by profile,resource_name;

and carefully observe the results (ALL the profiles should have the appropriate parameters)!

Just for the record, let's enumerate the parameters:

• failed_login_attempts - the number of failed login attempts before locking the account. It defaults to 10 times.

• password_grace_time - the grace period after the password_life_time limit is exceeded. After that, your account will be EXPIRED & LOCKED. It defaults to unlimited.
   
• password_life_time - how long an existing password is valid. 180 days is the default value.
   
• password_lock_time – how long to lock the account after the failed login attempts is met. Don't mention its value and you will end having 1 day of relax, looking for the next login attempt to pe considered. Of course, this was a joke, the problem is not YOU, the dba, but the regular users, and they may not be happy waiting for the database to take them into consideration.
   
• password_reuse_max – the number of times that you can reuse a password. Default = unlimited.
   
• password_reuse_time – a time limit before a previous password can be reused. Default = unlimited.

Well, let's just remember that you've got one more parameter in this category, PASSWORD_VERIFY_FUNCTION, but, don't forget, we're talking here about some kind of security relaxation, so having an enforced function in order to treat the login attempts is not a goal for us right now.