How to Backup User Passwords in Oracle

Backing up user passwords in Oracle is essential for database administrators. This guide shows how to back up passwords for a single user, multiple users, or all users in the database using SQL queries.

Backup Password for a Single User

col name for a15
col password for a20
col spare4 for a999
set lines 1000 pages 1000
select name, password, spare4 
from user$ 
where name = '&name';

Backup Passwords for Multiple Users

col name for a15
col password for a20
col spare4 for a999
set lines 1000 pages 1000
select name, password, spare4 
from user$ 
where name in ('HR', 'SYS');

Backup Passwords for All Database Users

spool all_db_users_password.sql
col command for a2000
set lines 1000 pages 2000
select 'alter user ' || name || ' identified by values ''' || password || ''';' as command
from user$ 
where name in (select username from dba_users) 
and spare4 is null and password is not null
union all
select 'alter user ' || name || ' identified by values ''' || spare4 || password || ''';' as command
from user$ 
where name in (select username from dba_users) 
and spare4 is not null and password is not null
union all
select 'alter user ' || name || ' identified by values ''' || spare4 || ''';' as command
from user$ 
where name in (select username from dba_users) 
and spare4 is not null and password is null;
spool off;

Also Read:

• Oracle User Management: A Complete Guide
• Oracle SQL Scripts to Check User Password Change, Account Creation, Last Login, Expiry Date, and Password History