set password life time to unlimited in Oracle
While developing Icinga, I also need to test the Oracle implementation in IDOUtils which then leads me to my fancy XE install (which is stripped down to low-mem and is only started on demand)
So, I wasn’t really expecting that I should change my password in 7 days.
sol /etc/icinga # rlwrap sqlplus icinga@XE SQL*Plus: Release 11.2.0.2.0 Production on Mon Mar 11 22:34:42 2013 Copyright (c) 1982, 2011, Oracle. All rights reserved. Enter password: ERROR: ORA-28002: the password will expire within 7 days Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production SQL>
Time for some sysdba magic
sol /etc/icinga # rlwrap sqlplus "sys@XE as sysdba" SQL*Plus: Release 11.2.0.2.0 Production on Mon Mar 11 22:44:46 2013 Copyright (c) 1982, 2011, Oracle. All rights reserved. Enter password: Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production SQL
Get to know which profile the icinga user is using
SQL> SELECT profile FROM dba_users WHERE username='ICINGA'; PROFILE -------------------------------------------------------------------------------- DEFAULT
Now check the password lifetime of the default profile.
SQL> SELECT resource_name,limit FROM dba_profiles WHERE profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';
Alter it to unlimited (note: it’s a local dev oracle xe install!).
SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
Though, this doesn’t fix the icinga user being gracefully expired already.
SQL> SELECT account_status FROM dba_users WHERE username='ICINGA'; ACCOUNT_STATUS -------------------------------------------------------------------------------- EXPIRED(GRACE)
But updating this manually to the exact same password will resolve the issue.
SQL> ALTER USER ICINGA IDENTIFIED BY XXXXX; User altered. SQL> SELECT account_status FROM dba_users WHERE username='ICINGA'; ACCOUNT_STATUS -------------------------------------------------------------------------------- OPEN
Now quit the sysdba session and re-login as icinga user.
sol /etc/icinga # rlwrap sqlplus icinga@XE SQL*Plus: Release 11.2.0.2.0 Production on Mon Mar 11 22:56:38 2013 Copyright (c) 1982, 2011, Oracle. All rights reserved. Enter password: Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production SQL>
Voilà – Oracle beaten again 🙂
(Oh, and make sure to use rlwrap all the time. Native sqlplus just sucks on tab completion and so on).