User EXPIRED(GRACE) – How to Never Expire!

Ok, this is a common issue, once it’s like the default behavior of oracle for DEFAULT profile, used as DEFAULT to new users. But it’s really easy to solve. 🙂

Before expiring you should be received some:

ORA-28002: the password will expire within x days

Ok then, but what if you are facing errors to reuse the same password, don’t know the password and yet, how to never let it happen again?

Just create a new profile and adjust those limits. Here it goes:

SQL> col username for a35
col account_status for a20
col profile for a20
select username, account_status, created, lock_date, EXPIRY_DATE,temporary_tablespace, profile, default_tablespace from dba_users where username like upper('%&username%')
order by username asc;
SQL> SQL> SQL> 2 3 4
Enter value for username: TESTUSER
old 2: where username like upper('%&username%')
new 2: where username like upper('%TESTUSER%')

USERNAME ACCOUNT_STATUS CREATED LOCK_DATE
----------------------------------- -------------------- --------- ---------
EXPIRY_DA TEMPORARY_TABLESPACE PROFILE
--------- ------------------------------ --------------------
DEFAULT_TABLESPACE
------------------------------
TESTUSER EXPIRED(GRACE) 30-OCT-15
06-OCT-16 TEMP DEFAULT
USERS

SQL> password
Changing password for TESTUSER
Old password:
New password:
Retype new password:
ERROR:
ORA-28003: password verification for the specified password failed
ORA-20011: Password should differ from the \
old password by at least 3 characters

Password unchanged

SQL> create profile TESTUSER limit PASSWORD_LIFE_TIME unlimited;

Profile created.

SQL> ALTER PROFILE TESTUSER limit PASSWORD_LIFE_TIME UNLIMITED;
ALTER PROFILE TESTUSER limit PASSWORD_GRACE_TIME UNLIMITED;
ALTER PROFILE TESTUSER limit PASSWORD_REUSE_TIME UNLIMITED;
ALTER PROFILE TESTUSER limit PASSWORD_REUSE_MAX UNLIMITED;
ALTER PROFILE TESTUSER limit FAILED_LOGIN_ATTEMPTS UNLIMITED;
ALTER PROFILE TESTUSER limit PASSWORD_LOCK_TIME UNLIMITED;
Profile altered.

SQL>
Profile altered.

SQL>
Profile altered.

SQL>
Profile altered.

SQL>
Profile altered.

SQL>

Profile altered.

SQL> alter user TESTUSER profile TESTUSER;

User altered.

SQL> select password from sys.user$ where name='TESTUSER';

PASSWORD
------------------------------
402008CF7D4974F3

SQL> ALTER user TESTUSER identified by values '402008CF7D4974F3';

User altered.

SQL> col username for a35
col account_status for a20
col profile for a20
select username, account_status, created, lock_date, EXPIRY_DATE,temporary_tablespace, profile, default_t ablespace from dba_users
where username like upper('%&username%')
order by username asc
/SQL> SQL> SQL> 2 3 4
Enter value for username: TESTUSER
old 2: where username like upper('%&username%')
new 2: where username like upper('%TESTUSER%')

USERNAME ACCOUNT_STATUS CREATED LOCK_DATE
----------------------------------- -------------------- --------- ---------
EXPIRY_DA TEMPORARY_TABLESPACE PROFILE
--------- ------------------------------ --------------------
DEFAULT_TABLESPACE
------------------------------
TESTUSER OPEN 30-OCT-15
01-APR-17 TEMP DEFAULT
USERS
Advertisements

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