Site icon An Oracle Spin by Alex Lima

How to extend an account whose password is expiring?

One of the ways to extend the password of an expiring account is to change the profile of the user.

— show the current user profile which shows the password expires 180 days
SQL> select * FROM dbA_profiles where profile = ‘APPLICATIONS’;

PROFILE RESOURCE_NAME RESOURCE LIMIT
—————————— ——————————– ——– —————————————-
..
APPLICATIONS FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED
APPLICATIONS PASSWORD_LIFE_TIME PASSWORD 180
..

— show the user profile and expiry date and encrypted password
13:08:03 sys> select expiry_date, password, profile from dba_users where username = ‘SCOTT’;

EXPIRY_DA PASSWORD PROFILE
——— —————————— ——————————
21-DEC-09 8C465A58AE456660 APPLICATIONS

1 row selected.

— show the profile for DEFAULT which is set to not expire
SQL> select * FROM dbA_profiles where profile = ‘DEFAULT’;
PROFILE RESOURCE_NAME RESOURCE LIMIT
—————————— ——————————– ——– —————————————-
..
DEFAULT PASSWORD_LIFE_TIME PASSWORD UNLIMITED

— change the profile
13:08:17 sys@> alter user SCOTT profile default;

User altered.

— change the password
13:08:28 sys@> alter user SCOTT identified by values ‘8C465A58AE456660’;

User altered.

— change the profile for the user back to expire password
13:08:42 sys@> alter user SCOTT profile APPLICATIONS;

User altered.

— check the password expiry date has changed
13:08:51 sys@> select expiry_date, password, profile from dba_users where username = ‘SCOTT’;

EXPIRY_DA PASSWORD PROFILE
——— —————————— ——————————
20-JUN-10 8C465A58AE456660 APPLICATIONS

— shows the password history is kept, note the password changed wasn’t captured in the password history
sys> SELECT name, password_date
FROM sys.user$, sys.user_history$
WHERE user$.user# = user_history$.user#
and name = ‘SCOTT’;

NAME PASSWORD_
—————————— ———
SCOTT 01-MAY-06
SCOTT 26-APR-07
SCOTT 17-APR-08

Exit mobile version