 |
|
How to retrieve oracle passwords
Oracle Tips by Burleson Consulting |
Question: I need to
sign-on as an end-user for troubleshooting, but they don't want
their password changed. Can I save their existing password, do
my work, and put back their old password?
Answer: You can retrieve an
existing password as an encrypted value and you can also copy encrypted
passwords between databases.
The dbms_metadata procedure allows you to copy
user accounts from one instance to another with the same password, grants,
roles, etc, without using the exp/imp tools.
You wont be able to see the password (sorry hackers), but you can copy it in
its encoded form. This is extremely useful for DBAs that are copying their
production database to development, or migrating a database from one instance to
another.
Here we use the DBMS_METADATA package to pull the user password information.
Once the encrypted password is captured, it can be re-inserted into the user ID
after you are done testing:
select
dbms_metadata.get_ddl('USER', username) || '/' usercreate
from
dba_users;
USERCREATE
--------------------------------------------------------------
CREATE USER "SYS" IDENTIFIED BY VALUES 'F894844C34402B67'
DEFAULT TABLESPACE "SYSTEM" TEMPORARY TABLESPACE "TEMP"/
 |
If you like Oracle tuning, see the book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning tips and
scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
