51 lines
1.2 KiB
SQL
51 lines
1.2 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display User Password history
|
|
* Parameters : 1 - DBUserName (Use % as wildcard, Default value '%')
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 26-May-16 Vishal Gupta Created
|
|
*
|
|
*/
|
|
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
UNDEFINE USERNAME
|
|
DEFINE USERNAME="&&1"
|
|
|
|
set term off
|
|
COLUMN _USERNAME NEW_VALUE USERNAME NOPRINT
|
|
SELECT DECODE('&&USERNAME','','%','&&USERNAME') "_USERNAME"
|
|
FROM DUAL;
|
|
set term on
|
|
|
|
|
|
PROMPT ************************************************
|
|
PROMPT * User Password History
|
|
PROMPT *
|
|
PROMPT * Input Parameters
|
|
PROMPT * - User Name = '&&USERNAME'
|
|
PROMPT ************************************************
|
|
PROMPT
|
|
PROMPT
|
|
|
|
SELECT u.username
|
|
, h.password
|
|
, h.password_date
|
|
FROM dba_users u
|
|
JOIN sys.user_history$ h ON h.user# = u.user_id
|
|
WHERE UPPER(u.username) = UPPER('&&USERNAME')
|
|
ORDER BY u.username
|
|
, h.password_date
|
|
;
|
|
|
|
|
|
@@footer |