Re: Change user password with stored procedure ?

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Thu, 19 Nov 1998 13:24:16 GMT
Message-ID: <3654199f.2908582@inet16.us.oracle.com>

On Thu, 19 Nov 1998 12:59:35 +0100, "Juergen Schneider" <js_at_connectus.de> wrote:

>Is it possible to that the user changes his password with a stored procedure
>?
>
>I tried the following in a procedure (with sUsername, sNewPassword as
>parameters for the procedure)
>
>ALTER USER sUserName IDENTIFIED BY sNewPassword;
>
>but always got a compile error. Is there another way that a user can change
>his password.
>
>I use VB-Client and Oracle8.
>
>Thanks for your help,

Yup, you can use Dynamic SQL.

create or replace
procedure change_password(
  sUsername in varchar2,
  sNewPassword in varchar2 ) is
--
  l_status number;
  l_cursor number;
begin
  l_cursor := dbms_sql.open_cursor;
  dbms_sql.parse( l_cursor,

                  'alter user ' || sUsername ||
                  ' identified by ' || sNewPassword,
                  dbms_sql.native );

  l_status := dbms_sql.execute( l_cursor );   dbms_sql.close_cursor( l_cursor );
end change_password;
/

NOTE: *** This will only work if the owner of the procedure has the privilege 'alter user' granted directly to them, NOT through a role. Roles are not enabled during stored procedure execution.

hope this helps.

chris.

>
>// Juergen
>

--
Chirstopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Nov 19 1998 - 07:24:16 CST