Re: Need procedure to change password

Lee Levy wrote:
>
> In article <4i7v8m$igp_at_gaia.cc.gatech.edu>, badri (badri_at_cc.gatech.edu)
> says...
> >
> >Hi:
> >
> > I need the equivalent of a setuid script on UNIX. I want
> >an operator to maintain passwords of the hundreds of users we are
> >going to have soon. This operator would execute a procedure
> >that would take a username and passwd as arguments and alter that
> >user. The operator would obviously not be able to change the password
> >of sys. The id for that operator would only have execute procedure on
 this
> >procedure and nothing else.
> >
> > I created a procedure, but PL/SQL does not like ALTER inside
> >a procedure. Looks like I cannot issue such commands from a procedure.
> >How do I get this done?
> >
> >Haaaaaaalp,
> >
> >-Badri
> Badri,

Try the foll script. You might need to iron out a few syntax errors.

create or replace procedure Change_password ( P_username varchar2,

   P_password varchar2 )
as

   L_cid integer;
BEGIN
   L_cid := sys.dbms_sql.open_cursor;
   sys.dbms_sql.parse( L_cid, 'alter user ' || L_username || ' identified by ' || L_password, sys.dbms_sql.v7 );

   sys.dbms_sql.close(L_cid );
END Change_password;

Grant execute on this to public, to facilitate users to change their own passwords.

-- 
Cheers

Sridhar Subramaniam
Avion Consulting Services
Sydney - Australia
Email : ssubrama_at_nibucorp.ccdn.otc.com.au / avion_at_ozemail.com.au

Disclaimer : All opinions are truly and just mine.