Re: Changing user password from Oracle Forms.

On Wed, 02 Jul 1997 10:35:28 -0600, "R. Fingerson" <rfinger_at_netmail.mnet.uswest.com> wrote:

>Amir, Raza, Khan wrote:
>>
>> Is there any body on this planet to HELLLLLLLLP me.
>>
>> Here is my problem.
>>
>> I am working in D2K, while using Oralce Forms 4.5 I have retrived the user
>> password with GET_APPLICATION_PROPERTY but I am unable to change it.
>> SET_APPLICATION_PROPERTY can not set the password of the user and
>> 'ALTER USER ...' command can not be used because it gives following error at
>> compile time.
>
>You should be able to use the dbms_sql package to do this, and
>build the 'alter user' statement as a character string.
You can certainly do this. Included below is a routine that does so. I hope you find it useful.

CREATE OR REPLACE
FUNCTION Change_Password (vUsername IN varchar2,

                          vPassword IN    varchar2)

RETURN integer

IS

   iCursorID           integer;
   vCommand            varchar2 (80);
   iReturned           integer;

   xMISSING_PARAMETER EXCEPTION;

BEGIN
   IF (vUserName IS NULL OR vPassword IS NULL) THEN

      RAISE xMISSING_PARAMETER;
   END IF;

   vCommand := 'ALTER USER '    || 
               vUsername        ||
               'identified by ' ||
               vPassword;
               

   iCursorID := DBMS_SQL.Open_Cursor;
   DBMS_SQL.Parse (iCursorID,

                   vCommand,
                   DBMS_SQL.v7);

   iReturned := DBMS_SQL.Execute (iCursorID);    DBMS_SQL.Close_Cursor (iCursorID);

   RETURN 1; EXCEPTION
   WHEN OTHERS THEN
        RETURN 0;
END Change_Password;
/

Please not that it does NOT verify that the current user is changing only his password, but if you call it from a menu item or button on a form you can certainly perform that validation there.

By compiling this function as a DBA, you can verify that the function will be able to alter the user's password without granting alter user to all of your users.

This is one of the routines included on the CD for my new book, High Performance Oracle Database Automation (published by The Coriolis Group), which should be hitting the shelves soon.

Jonathan Ingram Received on Wed Jul 09 1997 - 00:00:00 CEST