Re: HELP - ORACLE SET PASSWORD

In article <44n4iv$pnr_at_darius.nas.edu>, rturkman_at_nas.edu says...
>
>We have a situation were we need to allow the end-user to modify
>his/her own password from a front-end tool (i.e., SQL*FORMS, etc).
>We have created user accounts in Oracle and supplied our end-users
>with default passwords that they would like to alter.
>
>I have looked at all of the ORACLE provided systems packages,
>stored procedures and functions and could not locate one that could be
>called from the client application to alter an individuals account
>password.
>
>Contacted Oracle support, and they informed me that they do not have
>any pkg that meets my needs.
>
>Has anyone written a stored procedure, C program, PL/SQL script
>that provides such functionality to end-users? I am sure this
>question/requirement has come up before.
>
>Thanks in advance for all your help.
>
>RT - National Academy of Sciences.

Here's how we did it.

GRANT ALTER USER TO SYSTEM; CREATE OR REPLACE PROCEDURE change_password  ( password IN VARCHAR2 DEFAULT 'ORACLE' ) AS   c1 INTEGER;
  rc INTEGER;
  username VARCHAR2(20);
  str1 varchar2(64);
BEGIN
  SELECT USER INTO username FROM SYS.DUAL;   dbms_output.put_line( username );
  dbms_output.put_line( password );
  str1 := 'ALTER USER '||username||' IDENTIFIED BY '||password;   c1 := dbms_sql.open_cursor;
  dbms_sql.parse( c1, str1, dbms_sql.v7);   rc := dbms_sql.execute( c1 );
  dbms_sql.close_cursor( c1 );
END;
/

GRANT EXECUTE ON CHANGE_PASSWORD TO BASIC_USER; Where BASIC_USER is a role that all 'real' people accounts get. After creating the procedure it is just a matter of calling it with the new password that you want. Notice that this method only allows the password of the currently logged in account to be changed. Also, notice that it doesn't validate the old password, which is how SQL*Plus behaves. This method requires the DBMS_SQL package to be installed. E-mail me if you want more details.

-- 
Tony Noble
cz0763%zip02_at_mt.gov
Opinions expressed do not necessarily reflect those of my employer.