Resetting the password for oracle 11g or 12c users with same password.

I used to get a request from users to reset the password but they do not know the password since the password has been hard coded in their application and password has been expired due to password grace period.

SQL> set lines 120
set pages 120
column username format a20
column account_status format a20
column profile format a15

select username,account_status,profile,EXPIRY_DATE from dba_users where username like ‘TVIJAY’ order by 1;

USERNAME ACCOUNT_STATUS PROFILE EXPIRY_DA
——————– ——————– ————— ———
TVIJAY EXPIRED(GRACE) DEFAULT 19-DEC-17

SQL>

The above situation happened because PASSWORD_LIFE_TIME has been set to 180 days. For every 180 days user has to reset the password, however in some cases user can not change the password due to various reason like he has to hard code password in applications in many places etc…So in those cases we need to retain old password.  At times users do not know the password also.

SET LINES 120
SET PAGES 120
COLUMN LIMIT FORMAT A30
COLUMN RESOURCE_NAME FORMAT A30
Column PROFILE format a20
SELECT * FROM DBA_PROFILES
where PROFILE in (‘DEFAULT’);

PROFILE RESOURCE_NAME RESOURCE LIMIT COM
——————– —————————— ——– —————————— —
DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED NO
DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED NO
DEFAULT CPU_PER_SESSION KERNEL UNLIMITED NO
DEFAULT CPU_PER_CALL KERNEL UNLIMITED NO
DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED NO
DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED NO
DEFAULT IDLE_TIME KERNEL UNLIMITED NO
DEFAULT CONNECT_TIME KERNEL UNLIMITED NO
DEFAULT PRIVATE_SGA KERNEL UNLIMITED NO
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10 NO
DEFAULT PASSWORD_LIFE_TIME PASSWORD 180 NO
DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED NO
DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED NO
DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL NO
DEFAULT PASSWORD_LOCK_TIME PASSWORD 1 NO
DEFAULT PASSWORD_GRACE_TIME PASSWORD 7 NO

16 rows selected.

SQL>

For this situation, like user does not know the password but we need to reset to old password,

For this situation, we can use below method.

Collect the current password in the encrypted format.

SQL> select password from sys.user$ where name=’TVIJAY’;

PASSWORD
——————————
03BE7DDA8399B6A7

SQL>

Reset the password

alter user TVIJAY identified by values ’03BE7DDA8399B6A7′;

Now check the status of the user.

set lines 120
set pages 120
column username format a20
column account_status format a20
column profile format a15
select username,account_status,profile,EXPIRY_DATE from dba_users where username like ‘TVIJAY’ order by 1;

USERNAME ACCOUNT_STATUS PROFILE EXPIRY_DA
——————– ——————– ————— ———
TVIJAY OPEN DEFAULT 21-JUN-17

SQL>

There are situations to change the password life also. In those situations, you can use below method.

Get the current value.

SQL> SET LINES 120
SET PAGES 120
SELECT * FROM DBA_PROFILES where PROFILE in (‘DEFAULT’) AND RESOURCE_NAME IN (‘PASSWORD_LIFE_TIME’) ORDER BY PROFILE ;SQL> SQL>

PROFILE RESOURCE_NAME RESOURCE LIMIT COM
——————– —————————— ——– —————————— —
DEFAULT PASSWORD_LIFE_TIME PASSWORD 180 NO

SQL>

change to your desired value

SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME 270;

Profile altered.

SQL> SET LINES 120
SET PAGES 120
SELECT * FROM DBA_PROFILES where PROFILE in (‘DEFAULT’) AND RESOURCE_NAME IN (‘PASSWORD_LIFE_TIME’) ORDER BY PROFILE ;SQL> SQL>

PROFILE RESOURCE_NAME RESOURCE LIMIT COM
——————– —————————— ——– —————————— —
DEFAULT PASSWORD_LIFE_TIME PASSWORD 270 NO

SQL> set lines 120
set pages 120
column username format a20
column account_status format a20
column profile format a15
select username,account_status,profile,EXPIRY_DATE from dba_users where username like ‘TVIJAY’ order by 1;SQL> SQL> SQL> SQL> SQL>

USERNAME ACCOUNT_STATUS PROFILE EXPIRY_DA
——————– ——————– ————— ———
TVIJAY OPEN DEFAULT 19-SEP-17

SQL>