Oracle Password Self-Service
A simple 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 useful because not all Oracle users have Oracle client or know how to use
Sqlplus, Toad, etc. Unfortunately, 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] Those users can use this Web page 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.[note3]
/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
----------------------------------------------------------------------------------------------------
That's it. Test by going to the HTML page just created. If you need to improve it, make the program
more secure by sanitizing user input, and make the result page more user-friendly (instead of
displaying "If this is the first line on this page...").
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.)
[note3] The mid-tier doesn't need a real app server. I just happen to have one so I use it. You only
need a barebone Apache web server (or any web server) that supports the good-old CGI technology, and
you install it on the box where Oracle client is installed.