Oracle Spin – Flimatech Blog

Sharing Our Database Experience

How to extend an account whose password is expiring?

Posted by Amin Jaffer on December 22, 2009

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

About these ads

One Response to “How to extend an account whose password is expiring?”

  1. […] 11-How to extend password expire time? Amin Jaffer-How to extend and account password whose password is expiring […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 40 other followers

%d bloggers like this: