Oracle expired user - notes

Logged into a database and the user was expired and was now in the grace period.

The user was using the default profile

SQL> select username, account_status, expiry_date from dba_users where expiry_date > '24-SEP-18' order by 3
  2  /

USERNAME                       ACCOUNT_STATUS        EXPIRY_DATE
--------------------------------- -------------------------------- ---------
NXT_MAIN                        EXPIRED(GRACE)            02-OCT-18

Change date as appropriate.

The default password expiry profile was set to 180 so rather than having to reset the users every 180 days checked with the team and made the password_life_time unlimited.

alter profile <name> limit password_life_time unlimited;

i.e. 

SQL> alter profile default  limit password_life_time unlimited;

Profile altered.

All the users which had an expiry date now have a null expiry date but I was still left the expired(grace) user.

Followed the notes from here :

https://stackoverflow.com/questions/5521766/change-oracle-user-account-status-from-expiregrace-to-open

Now

USERNAME                           ACCOUNT_STATUS                   EXPIRY_DA
---------------------------------- -------------------------------- ---------

NXT_MAIN                            OPEN                                       DEFAULT

If you cause a database user's password to expire with PASSWORD EXPIRE, then the user (or the DBA) must change the password before attempting to log into the database following the expiration.

However, you can change the status to OPEN by resetting the user's password to the same value.

+++++++++++++++++++

Depending on your version and setup, one of these queries will probably get you the password hash:

select spare4 from sys.user$ where name='[user name]';

select password from dba_users where username = '[user name]';

Then use this to reset it:

alter user [user name] identified by values '[result from above query]';

++++++++++++++++++++

select
'alter user ' || su.name || ' identified by values'
   || ' ''' || spare4 || ';'    || su.password || ''';'
from sys.user$ su 
join dba_users du on ACCOUNT_STATUS like 'EXPIRED%' and su.name = du.username;

Once you run the output from the above you can change the limit_password_life (see above) to stop this happening but not a good idea on prod systems.

 

SQL> alter profile default  limit password_life_time unlimited;

+++++++++++++++++++

Logging in as user about to expire:

UDE-28002: operation generated ORACLE error 28002

ORA-28002: the password will expire within 7 days

Run:

set line 200;

set pages 10000;

select 'alter user '||du.username||' identified by values '''||su.password||''' /* current status: '||du.account_status||' will expire on: '||to_char(du.expiry_date)||' */ ; '

from dba_users du

inner join sys.user$ su

on su.name = du.username

where du.expiry_date <= trunc(sysdate+90)

and du.oracle_maintained = 'N'

and su.password is not null

order by du.expiry_date desc;

>>> alter user ISM_OWNER identified by values 'F6126D13AC78F315' /* current status: EXPIRED(GRACE) will expire on: 11-MAR-17 */ ;

SQL> alter user ISM_OWNER identified by values 'F6126D13AC78F315';

User altered.

As if by magic :

DB03 /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/audit (prod1) > sqlplus ISM_OWNER/........@PDB_BERLIN

SQL*Plus: Release 12.1.0.2.0 Production on Fri Mar 4 14:32:45 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Fri Mar 04 2016 12:33:05 +00:00 ✔

++++++++++++++++++++

Another method :

SQL> col DBMS_METADATA.GET_DDL('USER','SCOTT') format a300
SQL> l
  1* select dbms_metadata.get_ddl('USER','SCOTT') from dual
SQL> /

DBMS_METADATA.GET_DDL('USER','SCOTT')
-------------------------------------------------------------------------------------------------------------------------

   CREATE USER "SCOTT" IDENTIFIED BY VALUES 'S:94E9BF704054D73B078C8261C3CEA00F8123754F16B65E28A486DEC02120;T:F40A3032D1182065B3B03AC2A674571B69900B73BF55DA99B0FDB6E54E96A41DB3CD56E7C6457A979D6CD142D9F3BFAB0AB15CD9CA388CA02D6F331EB2810D3E98874649EFE142D6EE4EC51FEFCD47FD'
      DEFAULT TABLESPACE "OMS_MAIN_T"
      TEMPORARY TABLESPACE "TEMP"
      PASSWORD EXPIRE

Change CREATE USER to ALTER USER - i.e.

ALTER USER "SCOTT" IDENTIFIED BY VALUES 'S:94E9BF704054D73B078C8261C3CEA00F8123754F16B65E28A486DEC02120;T:F40A3032D1182065B3B03AC2A674571B69900B73BF55DA99B0FDB6E54E96A41DB3CD56E7C6457A979D6CD142D9F3BFAB0AB15CD9CA388CA02D6F331EB2810D3E98874649EFE142D6EE4EC51FEFCD47FD'

If you really need to reuse the old password, you should remove the limitations first. 

Suppose the profile is DEFAULT.

SQL> alter profile default limit password_reuse_time unlimited password_reuse_max unlimited;

Profile altered.