Oracle Password Self-Service A crude Perl CGI program to allow Oracle users to change password from a web page without using Oracle client, even if their password has expired. Installing this program for your company is important because not all Oracle users have Oracle client or know how to use Sqlplus, Toad, etc. Surprisingly, SQL Developer, the free but sophisticated Oracle client tool, does not support password reset if you happen to be using the lighter-weight JDBC thin client version.[note1] They can use this Web site as well. Here's how to set up this CGI program and its Web front-end. Download the Pro*C code near the bottom of this document Handling Expired Passwords from within Forms (V4.5/5.0) (Doc ID 52718.1) or the code from Using OCIPasswordChange() to Change Password (Doc ID 99457.1) and compile it to your OS.[note2] Put the compiled executable PASS in the CGI directory, e.g. /u01/app/oracle/middleware/mwname/config/OHS/ohs1/cgi-bin (mwname is your own middleware name) Make sure it works on command line, e.g. on Linux/UNIX: export ORACLE_HOME=/u01/app/oracle/middleware/mwname export LD_LIBRARY_PATH=/u01/app/oracle/middleware/mwname/lib ./PASS scott tiger scottnewpasswd orcldb and check to see if scottnewpasswd is the new password now in orcldb. In this same directory, create file let's call it doit (change paths as needed, especially mwname). chmod 755 on it. ---------------------------------------------------------------------------------------------------- #!/u01/app/oracle/middleware/mwname/perl/bin/perl -w use CGI qw(:standard); $username = param('username'); $passwd = param('passwd'); $newpasswd = param('newpasswd'); $conn = param('db'); print "Content-type: text/html\n\n"; #Change paths as needed $ENV{'ORACLE_HOME'}="/u01/app/oracle/middleware/mwname"; $ENV{'LD_LIBRARY_PATH'}="/u01/app/oracle/middleware/mwname/lib"; system("/u01/app/oracle/middleware/mwname/config/OHS/ohs1/cgi-bin/PASS $username \'$passwd\' \'$newpasswd\' $conn"); print " Oracle Password Self Service If this is the first line on this page (i.e. there's no error shown above), you have successfully changed your password. If there's an error and it's correctable, please go back one screen and retry. "; ---------------------------------------------------------------------------------------------------- In any directory your web server can expose, e.g. DocumentRoot (e.g. htdocs), create a web interface file with these lines ---------------------------------------------------------------------------------------------------- Oracle Password Self-Service Oracle Password Self-Service
Username:
Current password
New password
New password again
Connect identifier
---------------------------------------------------------------------------------------------------- That's it. Test by going to the HTML page just created. written in 2014-09 [2015-02 Update] If the password contains "@", it doesn't work well. Even Sqlplus needs special treatment, e.g. escape of quotation marks: On Windows: sqlplus yong/\"bobbie!@\"@orcl On Linux/UNIX: sqlplus yong/\"bobbie\!@\"@orcl ("!" also needs escape) (If you launch Sqlplus without password argument, on either Windows or Linux, you enter yong/"bobbie!@"@orcl at the "Connected to:" prompt without escaping quotes.) So, if there's "@" in the password, quotes must be escaped. Without "@", quotes must not be, instead of becoming optional. It complicates the code here. I didn't fully test, but for now, advise users that they should avoid having "@" as part of the password. __________ [note1] This is not surprising if you know that password reset before logon is only doable with OCI or the JDBC "thick" client version, which relies on Oracle client. See http://www.thatjeffsmith.com/archive/2012/11/resetting-your-oracle-user-password-with-sql-developer/ for more details. [note2] Follow standard procedure to compile a Pro*C program. Save the code from Doc 52718.1 to a file named PASS.pc in the same directory where demo_proc.mk and env_precomp.mk are, normally $ORACLE_HOME/precomp/lib. Run make -f demo_proc.mk build EXE=PASS OBJS=PASS.o Check by: ./PASS scott tiger scottnewpasswd orcldb #suppose you could logon as sqlplus scott/tiger@orcldb Or save the code from Doc 99457.1 to PASS.c (not PASS.pc) and run the same make command. Check by: ./PASS scott tiger scottnewpasswd orcldb (or ./PASS scott tiger scottnewpasswd) If demo_proc.mk doesn't exist, use OUI to install demo, or just google one, e.g. from https://gcc.gnu.org/ml/gcc-help/2003-02/msg00287/demo_proc.mk If you need to run make again, remember to rm PASS.o first. Because sqlca.sqlerrm.sqlerrmc limits error message to 70 characters only, if you need a longer error message, replace for ( i = 0; i < sqlca.sqlerrm.sqlerrml; i++ ) { error_str[i] = sqlca.sqlerrm.sqlerrmc[i]; } error_str[i] = '\0'; printf( "\nFailed with following Oracle error while %s:\n\n%s", action_str, error_str ); in PASS.pc with size_t buf_len, msg_len; /* add this line near the top inside main() */ ... buf_len = sizeof (error_str); sqlglm(error_str, &buf_len, &msg_len); printf( "\nOracle error\n
%.*s\n", msg_len, error_str); (The lines containing action_str are useless and can be commented out.)