Re: Change password pl/sql?

From: Kirmo Uusitalo <kirmo.uusitalo.no.spam_at_iki.fi>
Date: Fri, 14 Nov 2003 09:01:09 +0200
Message-ID: <39u8rvg8pa23mrja1oethgmieujhq7cc1k@4ax.com>

Hi Pete,

Thanks for this information. The use of sql*plus password command is out as we need to implement this inside an application.

To me it seems kind of silly that there is no API to calculate the hash from the plaintext password.

The function to change the password could then be implemented as

1) read users current password hash xxyyy
2) change the users password to the given old_password
3) read the current password hash
4) if the read password hashes are not the same, reset the password

back to original i.e. alter user xxx identified by values 'xxyyy' otherwise change the user's password to the given new_password

If all above steps are done in PL/SQL function, then the probability that the running of the procedure stops between steps 2 and 3 is quite low. If this would happen, it would leave the user's password to the possibly wrong supplied old_password which could be a security risk.

Regards,

Kirmo Uusitalo

On Wed, 12 Nov 2003 14:28:00 +0000, Pete Finnigan <plsql_at_petefinnigan.com> wrote:

>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
Received on Fri Nov 14 2003 - 01:01:09 CST