Re: Need procedure to change password

Badri,

This will Work:

1. Create a procedure as follows in the account of system or a dba person, who has authority to change passwords.

 Create or Replace PROCEDURE sp_Password_change(u_name in varchar2,                                            n_password in varchar2)
 AS
   password_cursor              integer;
   rc                           integer;
 BEGIN
      password_cursor :=dbms_sql.open_cursor;
      dbms_sql.parse(password_cursor,
         ' alter user '||u_name||' identified by '||n_password,
           dbms_sql.v7);
      rc :=dbms_sql.execute(password_cursor);
      dbms_sql.close_cursor(password_cursor);

2) Grant execute on this procedure to the user who will execute  it. If you want to restrict the ability to change any one users  passwords, place that username or list of usernames, or in a  special table ( you will then need to select from this table in a loop), in a IF statement i.e:    IF n_name = 'SYS' THEN

      do the parsee ...
   ELSE
      EXIT;
   END_IF; Hope this helps.

--
Vikram Goel                                 Motorola email: vgoel_at_pts.mot.com
Oracle DBA - Consultant
Aerotek Inc.                                My email:  vgoel_at_emi.net

Motorola Info:
Mail Stop 39, Room S1014
1500 Gateway Blvd,
Boynton Beach, FL 33426 
 
In article <4i7v8m$igp_at_gaia.cc.gatech.edu>, badri_at_cc.gatech.edu (badri) writes:


>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