Re: alter user via dbms_sql

From: PaulCinVT <paulcinvt_at_aol.com>
Date: 27 Sep 1999 18:21:34 GMT
Message-ID: <19990927142134.08458.00000854@ngol05.aol.com>

/*

Changing Passwords for Oracle Users
*/
/*

 The stored procedure script below allows you to change/set password for Oracle users.

 Features of this script:

 This stored procedure can be called from various client environments like  PowerBuilder, Visual Basic, Microsoft Excel, Oracle Forms, Oracle Reports or SQL*Plus.

 It makes use of the DBMS_SQL package.
 The stored procedure needs to be owned by the SYS account  but can be accessed by all users using a public synonym.

 It could be particularly useful when users accessing the instance are on  different client environments and don't necessarily have access to SQL*Plus. */

REM ------------------------------------------------------------

REM This stored procedure sets/changes password for Oracle users REM Accepts username and new password.
REM Compares the username with the 'signed-on' username. REM Usage
REM For changing the password for USER user1 to password1, REM chng_pwd(user1,password1)
REM This script needs to be run from SYS account

REM ------------------------------------------------------------

create or replace procedure chng_pwd(uname varchar2, new_pwd varchar2) as
  cursor1 integer;
  rows_processed integer;
begin
  if UPPER(user) = UPPER(uname) then
    cursor1 := dbms_sql.open_cursor;
    dbms_sql.parse(cursor1,'alter user '||uname||     ' identified by '||new_pwd,dbms_sql.v7);     rows_processed := dbms_sql.execute(cursor1);     dbms_sql.close_cursor(cursor1);
  end if;
exception
  when others then
   dbms_sql.close_cursor(cursor1);
end chng_pwd;
/
create public synonym chng_pwd for sys.chng_pwd; grant execute on chng_pwd to public;
exit;

Paul in VT Received on Mon Sep 27 1999 - 13:21:34 CDT