Re: Change password pl/sql?

Hi Kirmo,

You should look into the $ORACLE_HOME/rdbms/admin/utlpwdmg.sql file. This file implements a password function to control the strength of a new password and also compare it to the old password. You can also use the password command in SQL*Plus, i.e. :

SQL> password
Changing password for VPD
Old password: ***
New password: ***
Retype new password: ***
Password changed
SQL> The old password hashes are stored in sys.user_history$

SQL> connect sys/change_on_install_at_zulia as sysdba Connected.
SQL> desc user_history$

 Name                                      Null?    Type
 ----------------------------------------- -------- -------------------
 USER#                                     NOT NULL NUMBER
 PASSWORD                                           VARCHAR2(30)
 PASSWORD_DATE                                      DATE

SQL> You need to setup and use profiles to use this functionality. The Oracle password algorithm is not public and is protected by Oracle. There is no public API to call a password encryption function to calculate a hash.

If you were still to pursue your function you could do an execute immediate for the user and password passed in, then read the hash from dba_users or sys.user$ compare this hash with the one in sys.user_history$. Reset the password or back to the old value as you see fit based on your functionality.

hth

kind regards

Pete

-- 
Pete Finnigan
email:pete_at_petefinnigan.com
Web site: http://www.petefinnigan.com - Oracle security audit specialists
Book:Oracle security step-by-step Guide - see http://store.sans.org for details.