April 20, 1999 Changing Database Password in a Web Server Application This Code of the Week entry comes from Debu Panda, a database administrator for Oracle Corporation in Bellevue Washington. This submission helps users of Web Aplication using the PL/SQL cartridge in Oracle Web Server to change the password for users using Basic Oracle Authentication Scheme. The first script should be compiled as SYS and a public synonym named CHANGE_PASSWORD be created and users be granted execute privilege on this procedure. The second procedure should be compiled as the DAD owner and a public SYNONYM be created for it and all users should be granted execute privilege on this procedure. From the browser the procedure should be invoked as follows: http://://plsql/frmpass e.g. http://abc.com:8888/test/plsql/frm If user enters the password and confirms it and then press button the database passsword for the user will be changed.
/* This procedure changes the databasec password for the current user. Needs to be compiled as SYS */ CREATE or REPLACE procedure change_password (p_password in varchar2,p_confirm in varchar2, p_submit in varchar2) aS l_cursor integer; l_user varchar2(30); BEGIN select user into l_user from dual; dbms_output.put_line(l_user); if (p_password p_confirm) then raise_application_error(-20001,'Your Password is Incorrect'); end if; l_cursor := dbms_sql.open_cursor; dbms_sql.parse(l_cursor,'ALTER USER '||l_user||' IDENTIFIED BY '|| p_password,dbms_sql.v7); -- get encrypted version of new password dbms_sql.close_cursor(l_cursor); -- return success code www_boost.title_chgpass(l_user); END; /
/* This procedure be compiled as the DAD owner. */ create or replace procedure frmpass as l_param varchar2(30); l_user varchar2(30); begin select user into l_user from dual; htp.htmlopen; htp.headopen; htp.header(1,'Change Password for User: ' ||l_user || ' Web Database'); htp.headclose; l_param:= 'change_password'; dbms_output.put_line(l_param); htp.formopen(l_param,'POST'); htp.print('New Password:'); htp.formpassword(cname=>'p_password', csize=>10); htp.br; htp.print('Confirm Password:'); htp.formpassword(cname=>'p_confirm', csize=>10); htp.formsubmit('p_submit','Change Password'); htp.formreset('Clear'); htp.br; htp.formclose; htp.br; htp.htmlclose; end; /
for Test