Print Friendly, PDF & Email
How can I change a password for a user in Oracle? How to set the same password to the expired user account without knowing the original password?

QUESTIONS
How do I reset a password for a user in Oracle?
How do I restore a user password if the account has expired?
How do I change the password for a user in Oracle?
How do I recover a lost password on Oracle?
How to connect to Oracle when you forgot your password.
How to unlock an account and reset a password.

REQUESTS
1 An administrator wants to reset the user’s expired account password to the same value.
2 A user has forgotten his password and therefore he asked an administrator to set a new one.

SOLUTION


1. An administrator wants to reset the user’s expired account password to the same value.

-- To list all expired accounts

SET LINES 300
SET PAGES 999
COL USERNAME FOR A10 
COL ACCOUNT_STATUS FOR A17
COL LOCK_DATE FOR A10 
COL EXPIRY_DATE FOR A11 
COL PROFILE  FOR A8
COL RESOURCE_TYPE FOR A13
COL RESOURCE_NAME FOR A19 
COL LIMIT FOR A10 

SELECT U.USERNAME, 
       U.ACCOUNT_STATUS,
       U.LOCK_DATE,
       U.EXPIRY_DATE, 
       U.PROFILE,
       P.RESOURCE_NAME,
       P.RESOURCE_TYPE,
       P.LIMIT
FROM DBA_USERS U,
     DBA_PROFILES P
WHERE
     U.ACCOUNT_STATUS LIKE '%EXPIRED%' AND
     U.PROFILE = P.PROFILE AND 
     P.RESOURCE_NAME = 'PASSWORD_LIFE_TIME';
USERNAME   ACCOUNT_STATUS    LOCK_DATE  EXPIRY_DATE PROFILE  RESOURCE_NAME       RESOURCE_TYPE LIMIT
---------- ----------------- ---------- ----------- -------- ------------------- ------------- ----------
USRDATA    EXPIRED(GRACE)               22-NOV-17   DEFAULT  PASSWORD_LIFE_TIME  PASSWORD      180

Generate a sql command to reset a user’s password to the same value. For diffrent database releases diffrent commands

-- A query to generate a password reset command in 11gR2

COL "PASSWORD RESET" FOR A65 
SELECT 'ALTER USER ' || P.NAME || ' IDENTIFIED BY VALUES ' || q'[']' || P.PASSWORD || q'[';]' PASSWORD_RESET
FROM USER$ P,
     DBA_USERS U
WHERE 
     P.NAME = U.USERNAME AND 
     U.ACCOUNT_STATUS LIKE '%EXPIRED%';
     
PASSWORD_RESET
--------------------------------------------------------------------------------
ALTER USER USRDATA IDENTIFIED BY VALUES '5AECD6C05248729F';
-- A query to generate a password reset command in 12c+

SET LINES 200 
SET PAGES 999
COL PASSWORD_RESET FOR A200 WORD_WRAPPED

SELECT 'ALTER USER ' || P.NAME || ' IDENTIFIED BY VALUES ' || q'[']' || P.SPARE4 || q'[';]' PASSWORD_RESET
FROM USER$ P,
     DBA_USERS U
WHERE 
     P.NAME = U.USERNAME AND 
     U.ACCOUNT_STATUS LIKE '%EXPIRED%';
     

PASSWORD_RESET
--------------------------------------------------------------------------------------------------------------
ALTER USER USRDATA IDENTIFIED BY VALUES 'S:5052C43F5D3FFA43C5775153A8C...0240EACB73048F94F3737541F2D9812CFB';

Also you can get current hash of user password through the DBMS_METADATA.GET_DDL function

SET PAGES 0
SET LINES 3000
SET LONG 2000000000
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SET TRIMSPOOL ON 
SET SERVEROUTPUT ON SIZE 1000000
COLUMN RESET_PASSWORD FORMAT a2500 WORD_WRAPPED

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'PRETTY', FALSE);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', FALSE);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', FALSE);

SELECT DBMS_METADATA.GET_DDL('USER','&USERNAME') RESET_PASSWORD FROM DUAL;
-- Query result 

Enter value for username: USRDATA
old   1: SELECT DBMS_METADATA.GET_DDL('USER','&USERNAME') RESET_PASSWORD FROM DUAL
new   1: SELECT DBMS_METADATA.GET_DDL('USER','USRDATA') RESET_PASSWORD FROM DUAL

CREATE USER "USRDATA" IDENTIFIED BY VALUES 'S:5052C43F...248729F' DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP";

2 A user has forgotten his password and therefore he asked an administrator to set a new one.

Just set a new password for the user’s account through the ALTER USER … IDENTIFIED BY … command. For instance

ALTER USER SMITH IDENTIFIED BY SMITH;

CONNECT SMITH/SMITH
Connected.

SHO USER
USER is "SMITH"

 

Version  : 17:39 19.12.2017
Updated  : 17:54 25.05.2021
Platform : Oracle Linux Server 7.3
Database : Oracle database 10G-12C