HOW TO RESET AN ORACLE ACCOUNT PASSWORD
Accounts
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?
GOAL
How do I reset a password for a user in Oracle?
How do I restore a user password if the account has expired?
SOLUTION
1. To reset the user's expired 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. There are different commands for different database releases
-- A query to generate a password reset command for all expired accounts 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 for all expired accounts in 12c and higher
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';
Another way to get the current hash value of the user's password is to use 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);
-- Get user DDL
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 doesn't know its account's password, and therefore he/she wants to set a new one.
Set a new password for the user's account with the ALTER USER … IDENTIFIED BY … statement. For instance
ALTER USER SMITH IDENTIFIED BY SMITH;
CONNECT SMITH/SMITH
Connected.
SHO USER
USER is "SMITH"
Written At
25 MAY 202118:00
Red Hat Release
7.x x64
Database Release
11.2.0.4 - 19.13.0.0.0
Tags In
Share
- Accounts
- Auditing
- AWR
- Bash Scripts
- Datapump
- Default Category
- Demos
- Directory Objects
- Environment Variables
- Initialization Parameters
- Iptables
- Java Program
- Memory Usage
- Metadata API
- Networker
- NLS Settings
- Optimizer Statistics
- ORA-00942
- ORA-01031
- ORA-01720
- ORA-28001
- ORA-31671
- Oracle Database
- Oracle Enterprise Manager
- Performance Tunning
- Postfix
- Privilegies
- Processes
- Queries
- Red Hat Enterprise Linux
- Redo Logs
- Session Tracing
- Sessions
- SQL Trace
- SQLPlus
- Statspack
- Tablespaces
- UTL_FILE
- UTL_FILE_DIR
- Wait Events
- Yum