Durham Oracle

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

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.

AWR reports and their ilk, such as ASH reports are only available in an Enterprise Edition, simply because one has to purchase the Oracle Diagnostic and Tuning Packs, commonly misunderstood as only relating to OEM.

If these packs, or more appropriately, the licenses for these packs have not been purchased, then AWRs etc should never be ran on a database.

If there is no license agreement, use the following piece of SQL to see if AWR et al have been ran on a database.

col name format A31
col detected format 9999
col samples format 9999
col used format A5
col interval format 9999999

SELECT name,
detected_usages detected,
total_samples samples,
currently_used used,
to_char(last_sample_date,'MMDDYYYY:HH24:MI') last_sample,
sample_interval interval
FROM dba_feature_usage_statistics
WHERE name = 'Automatic Workload Repository' OR name like 'SQL%';

If there are results for this on a Standard Edition or Enterprise Edition database where there is no licence agreement, then you could be in trouble.

For databases where there is no licence agreement then you should switch off the AWR capabilities just to protect yourself.

To check this run the following SQL

SQL> show parameter control_management_pack_access

If this is set to DIAGNOSTIC+TUNING then we should turn it off and use the following

SQL> ALTER SYSTEM SET control_management_pack_access=NONE;

Durham Oracle

edb360 - reporting tool

Oracle expired user - notes

AWR - Oracle Diag & Tuning Pack