Adverse SQR performance as a result of the Oracle setting _unnest_subquery=FALSE

As I mentioned in an earlier post that the performance tuning recommendation from Oracle for PeopleSoft customers requires setting the init.ora parameter _unnest_subquery to FALSE.

While this is probably a panacea to many a performance issues seen in the system, it is not the case in some occasions. Just like an adverse reaction of a drug that is meant to cure one disease ends up you having another disease; in the case of my customer's PeopleSoft Campus Solution system it caused the custom attendance calculation process to see a sudden and huge spike in the time take to complete (from ~40 odd minutes to 7.5 hours).

Diagnosis gone wrong:

The issue was dealt by many a consultants and system admins over the course of a year without any tangible difference in performance and then I happened to join the team and thus all and sundry were eager to have their hands off this issue. And so I got to know that all cliched solutions were tried out by this time.

As I expected, the issue was misdiagnosed or rather no proper diagnosis was done. The very mention of performance issue and the guys went about lining up the usual suspects (cliched performance fixes) - 

• Gathering stats on the tables
• Flushing shared pool
• altering session parameters
• rebuilding indexes
• putting hints and doing trial and error

When nothing worked, perhaps they just clung on to hope that the problem would disappear just as it miraculously appeared out of nowhere.

What I did differently:

The first thing I did was to do a real-time monitoring of the SQL's as they were being executed by the SQR while the process took long time to run. I used the below SQL to do that - 

 SELECT S.username,   
     s.sid,   
     s.inst_id,   
     s.serial#,   
     s.osuser,   
     t.sql_id,   
     sql_text   
 FROM  v$sqltext_with_newlines t,   
     gv$session s   
 WHERE t.address = s.sql_address   
     AND t.hash_value = s.sql_hash_value   
     AND s.status = 'ACTIVE'   
     AND s.username <> 'SYSTEM'   
     AND osuser <> :1   
 ORDER BY s.sid,   
      t.piece   

From this I noticed a couple of SQL's that were always seen running and running long. Both SQL's had an "exists" clause and when I extracted the explain plan I found that the plan for both the SQL's were significantly costlier as shown below.
SQL1 (Before):

 SELECT Count(*)   
 FROM  ps_attend_tmplt C   
 WHERE C.institution = :1   
     AND C.strm = :2   
     AND C.class_attend_dt <= :3   
     AND EXISTS (SELECT 'x'   
           FROM  ps_stdnt_enrl_l_vw C1   
           WHERE C1.emplid = :4   
              AND C1.crse_id = :5   
              AND C1.strm = :2   
              AND C1.class_nbr = C.class_nbr   
              AND C1.stdnt_enrl_status = 'E')   

SQL2 (Before):

 SELECT Count(*)   
 FROM  ps_attend_tmplt A   
 WHERE A.institution = :1   
     AND A.strm = :2   
     AND EXISTS (SELECT 'x'   
           FROM  ps_stdnt_enrl_l_vw A1   
           WHERE A.institution = A1.institution   
              AND a.strm = a1.strm   
              AND a.class_nbr = a1.class_nbr   
              AND A1.emplid = :3   
              AND A1.crse_id = :4   
              AND A1.strm = :2   
              AND A1.class_nbr = A.class_nbr   
              AND A1.stdnt_enrl_status = 'E')   

Note: I did get a much better plan when the session parameter _unnest_subquery is TRUE. However there was no going back on that one. It had to remain FALSE. So the only option was to rewrite the SQL's to perform better without altering the program logic.

Now that it was clear that the plan was too costly I rewrote the SQL's to do a equi-join to get the row count rather than using exists clause as shown below. I was probably lucky that the SQL with an equi-join would suffice in this situation. A more straightforward and easier solution was to use nested begin-select statement within the SQR program, which I thought of using if equi-join is not an option.

With the revised SQL, the plan prepared by Oracle was way better than before.

SQL1 (after):

 SELECT Count(1)   
 FROM  ps_attend_tmplt C,   
     ps_stdnt_enrl_l_vw C1   
 WHERE C.institution = :1   
     AND C.strm = :2   
     AND C.class_attend_dt <= :3   
     AND C.institution = C1.institution   
     AND C.strm = C1.strm   
     AND C.class_nbr = C1.class_nbr   
     AND C1.emplid = :4   
     AND C1.crse_id = :5   
     AND C1.strm = :2   
     AND C1.class_nbr = C.class_nbr   
     AND C1.stdnt_enrl_status = 'E'   

SQL2 (After):

 SELECT Count(*)   
 FROM  ps_attend_tmplt A,   
     ps_stdnt_enrl_l_vw A1   
 WHERE A.institution = :1   
     AND A.strm = :2   
     AND A.institution = A1.institution   
     AND A.strm = A1.strm   
     AND A.class_nbr = A1.class_nbr   
     AND A1.emplid = :3   
     AND A1.crse_id = :4   
     AND A1.strm = :2   
     AND A1.class_nbr = A.class_nbr   
     AND A1.stdnt_enrl_status = 'E'   

Thus I was able to bring back the run time performance from 7.5 hours+ to the usual 30 to 40 minutes. What did you do in a similar situation or is there a better way out? Leave a comment below. Thanks!!

Mystery of _unnest_subquery=FALSE resolved

Continuing from where I left off in my previous post, we promptly referred this issue with the COBOL process SFPGLINT to Oracle as we had not done any customization. In the meanwhile we switched back the parameter "_unnest_subquery" to TRUE as an interim workaround. Oracle on their part started the investigation of the case with the usual suspicion around whether we are on a "supported version" of Oracle DB, PeopleTools, Weblogic etc...

Going down this path uncovered the horror that the production database (on version 11.2.0.2) was not up to date on the mandatory oracle patches for PeopleTools 8.52.We were missing patch numbers - 12704078, 13504791, 16065596 

What were these mandatory Oracle patches?
You can get them here ->[Required Interim Patches for the Oracle Database with PeopleSoft(1100831.1)]
Based on your database version and PeopleTools version you need to be up to date and not have missed any of them.

How did Oracle find out what we missed? 
Oracle requested for the init.ora file and the output of this command (Need to login as SYS/SYSADM) - 

 $ORACLE_HOME/OPatch/opatch lsinventory > /tmp/opls.txt   

This text file will contain the oracle patch numbers installed in the database. You can then compare if you have all those mandatory patches (relevant to you) from the PeopleTools patch spreadsheet listed in this text file. If you can't find it, then it is highly likely you are missing it.

Eventually after these missing patches were applied in production we then switched back the database parameter flag _unnest_subquery to FALSE and ran the accounting entry generation process SFPGLINT and it worked fine. Thus the issue was resolved without having to do any further investigation.

That a SQL query would stop fetching rows with a database parameter set to False when the database is missing mandatory patches was beyond me. Murphy's law in full force. This issue highlighted the importance of not missing on mandatory patches, a lesson I won't forget soon.

Note: While applying the missing patches you we realized each patch has certain pre-requisite patch and then there is possibility of the mandatory patch mentioned being overridden by another recent patch which was not applicable to our DB version. If you end up in a similar situation you need to log a case with oracle requesting for a merge-patch for your DB version.

The curious case of _unnest_subquery=FALSE in Oracle PeopleSoft

At the customer site where I'm currently working I came across a very odd issue recently. The Student Financials "Generate Accounting Entries" COBOL process - SFPGLINT stopped working and failed to generate any rows and this continued for several days. The main SQL of the COBOL process failed to return any rows, despite data being present for the criteria specified in the SQL.

That it was happening only in production and not any of the non-production instances only added to the mystery surrounding this issue. As a last resort, I imported the production data into the non-production instance and the exact same SQL was able to produce rows. This led me to believe that there is some difference at the database settings that is causing this strange behavior.

Using this sql (Note: You can also get this from the init.ora file) - 

 select * from v$parameter;  

I compared and narrowed down to the differences between the two databases (PROD and non-PROD). One of the difference happened to be this mysterious parameter - _unnest_subquery . While it was TRUE in all non-production databases it was set to FALSE in Production.

At this point I had very little knowledge of what this parameter was and why it was set to FALSE. So I went ahead and altered my SQLTools session where I was replicating the issue, using the command -

 alter session set "_unnest_subquery" = FALSE ;  

After altering the session this command run, I re-ran the same SQL and to my surprise the SQL failed to return any rows. 

I knew how the problem can be fixed now (by modifying the initialization parameter _unnest_subquery to TRUE), but it was deeply disturbing to know that Oracle database would produce different results for the same SQL because of an initialization parameter.

A google search of this parameter revealed that this parameter was all too familiar for PeopleSoft customers elsewhere. This blog post gave me insights into what this parameter does. At my customer place, it turned out that a decision was taken to change this parameter "_unnest_subquery" to FALSE in Production (a few weeks prior to this COBOL process issue being noticed) in order to resolve other performance issues seen in online pages and batch programs, as explained in the blog post.

Continuing to explore more on the google search results revealed that this recommendation to set this parameter to FALSE for PeopleSoft came from none other than Oracle who ships the Oracle databases with the parameter set to TRUE as default. The recommendation is found in the Oracle support site [Required Interim Patches for the Oracle Database with PeopleSoft(1100831.1)]. The recommendation is found for all database versions from 9i all the way to the more recent 12g (See below). 

Although this parameter should ideally have a performance impact, it still didn't explain as to why there is difference in the result rather than performance. So at this point we went ahead and logged a case with Oracle. I will continue on this topic in a later post as to where this leads to.

Trimming spaces and adding spaces on a BI Publisher report

Quite often in BI Publisher, there is a need to trim unnecessary spaces and add spaces on occasions. 

Think of printing an address. PS_ADDRESSES table has so many fields but when printing the address, some of the fields may not necessarily have values, leaving the address formatting  messed up with a lot of white spaces. This is what I'm talking about - 

The RTF template shown above generates the output as shown here - 

Removing unwanted space
In order to solve this issue, I created one BI Publisher field "mail address" with the code written as shown below.

As you can see now, all the extra white space for fields that didn't have a value is now gone (I'll ignore the border issue for now). Though this fixed the extra space issue, it introduced a new problem. I had given a deliberate space between CITY and POSTAL field in the formula but was ignored by BI Publisher in the output. 

Adding Space
The way you can add a space is quite simple, enclose the space within quotes and embed it between the BI publisher tag as shown below -

 <?if:C.ADDRESS1 != ' '?><?C.ADDRESS1?><?end if?>  
 <?if:C.ADDRESS2 != ' '?><?C.ADDRESS2?><?end if?>  
 <?if:C.ADDRESS3 != ' '?><?C.ADDRESS3?><?end if?>  
 <?if:C.ADDRESS4 != ' '?><?C.ADDRESS4?><?end if?>   
 <?C.CITY?><?' '>?<?C.POSTAL?>  

This gives the proper output I was looking for - 

Printing Labels in PeopleSoft across down using BI Publisher (formerly XML Publisher)

The Problem:
Recently I was tasked to build the report in PeopleSoft to generate the list of external examiners (yes, it is obviously an education institute) which will eventually be printed on to commercially available Labels. The labels would be pasted on to the question paper stack assigned to the designated exam hall for distribution.
As the de-facto choice of technology to accomplish something of this nature I went with XML Publisher a.k.a BI Publisher. What I thought was a simple report eventually turned out to be anything but simple to print the data across and then down....

The difficulty:
My Data source was a PS Query which had already done the necessary sorting with the query criteria. 
In BI Publisher, the data within XML tags are printed one below the other using a For-loop in the form of a table. This in itself presented a challenge as the data would get printed vertically down in a single column. But I wanted multiple column output. Without thinking too much my mind immediately jumped into the idea of breaking the page into 4 columns which is an option in MS Word. When I printed the sample data I ended up with an output like this - 

1   4   7   10

2   5   8   11

3   6   9   12

What I was actually looking to accomplish was to print the data in an "across down" manner. The same way we write things on a piece of paper (left to right, then down),

1    2    3    4 

5    6    7    8 

9   10   11   12

The next best option:

As I had developed cross tab/pivot reports previously, which is primarily used when the number of columns and rows are dynamic. In my situation, I had a fixed number of rows which had to be printed in a columnar fashion. After messing with the PS Query a bit and the RTF template, I then played around with the H tag (horizontal-break-table) of BI Publisher cross tab reports and got the output I wanted albeit with a catch. Each box (or row) of data elements got printed on separate pages. Unfortunately the horizontal-break-table causes a page-break and not a line-break and this works only with PDF output.

The solution:

At this point I had tried so many things that I had given up on BI Publisher and a tad disappointed at how much difficult this was proving to be as opposed to a simple MS Word mail merge. And just when I was about to settle for the closest solution I got using the 4 column output I discussed above, I found this wonderful blog from Vetriselvan where he had shown how to accomplish the output I was trying to get.

My sample template appears this way -

and the sample output is just the way I wanted - 

There is also a caveat with this solution as well. The 4 embedded tables grow vertically down printing 1 data element and skipping the other 3 in a round robin fashion, thus making it appear to print across and then down. As long as the data is even, this works fine. However if any of the data element is larger than the rest, the rows will be of uneven size and the output would look like this (Element-28 occupies more height than the rest causing the alignment to go haywire) -

This is not a big issue as most of the commercial labels have pre-defined height and width and the tables embedded in the RTF template have to adhere to those size restrictions. This coupled with choosing the right font and font-size addresses any such issues. 

Feel free to drop a comment if you have a question or accomplished using a different approach.

Implementing Captcha on PeopleSoft login page using JCaptcha - Part-2

Continuing from JCaptcha Part-1 we move on to Step-3 of the JCaptcha implementation on the PeopleSoft login page.

You will have to repeat the steps henceforth for each domain (in case of multiple domains)

Step-3:

• Before customizing the files signin.html, text.properties and error.properties, make a backup for restoration purposes. The files can be found here -
  

   <%PS_HOME%>webserv\<Domain>\applications\peoplesoft\PORTAL.war\WEB-INF\psftdocs\<Domain>  
  

  Open the signin.html file in a text editor and add the following code after the <HEAD> tag
  

   <!-- Added by V.Rao to prevent captcha image caching --><META HTTP-EQUIV="CACHE-CONTROL" CONTENT="NO-CACHE">  
  

• In the JavaScript section add the Ajax code below
  

   <!-- added by V.Rao [Begin] Ajax function to refresh the captcha image -->  
   <script>  
   function chitra()  
   {  
   var xmlhttp;  
   if (window.XMLHttpRequest)  
    {// code for IE7+, Firefox, Chrome, Opera, Safari  
    xmlhttp=new XMLHttpRequest();  
    }  
   else  
    {// code for IE6, IE5  
    xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");  
    }  
   xmlhttp.onreadystatechange=function()  
    {  
     if (xmlhttp.readyState==4 && xmlhttp.status==200)  
     {  
     document.getElementById("sampige").src="/chitra_durga.jpg?r="+new Date().getTime();  
     }  
    }  
   xmlhttp.open("GET","/chitra_durga.jpg",true);  
   xmlhttp.send();  
   }  
   </script>  
   <!-- added by V.Rao [End] Ajax function to refresh the captcha image -->  
  

• After this, search for the form tag element and modify/replace the action attribute to call the custom Captcha validation JSP as opposed to the PeopleSoft delivered psp servlet as shown below
  

   <!-- Commented by V.Rao <form action="?cmd=login&languageCd=<%=languageCd%>" method="post" id="login" name="login" autocomplete="off" onSubmit="signin(document.login)"> -->  
   <!-- Added by V.Rao --><form aaction="<%=psCtxPath%><%=psHome%>/Rao99.jsp" method="post" id="login" name="login" autocomplete="off" onSubmit="<%=psCtxPath%><%=psHome%>/Rao99.jsp">   
  

• The next piece of customization on the signin html file is to bring in the captcha elements i.e captcha image, refresh icon to refresh the captcha image and a text box to capture the verification code entered by the user. The sample code from my signin page is shown below. Place this code below the "pwd" element.
  

   <!-- Code added by V.Rao [Begin] Captcha elements-->  
   <tr>  
   <td height="35"><img src="/chitra_durga.jpg" alt="<%=20000%>" id='sampige' width="175" height="65" />&nbsp;</td>  
   <td height="35" align='middle'>  
    <img src="<%=psCtxPath%><%=psHome%>/images/PT_REFRESH_ICN.gif" alt="Refresh" width="16" height="16" onclick="chitra()" />  
    &nbsp;</td>  
   <td height="35"><input type = "text" name="jcaptcha" placeholder="<%=20000%>" class="pslogineditbox" tabindex="4"></td>  
   </tr>  
   <!-- Code added by V.Rao [End] -->  
  

  You would have noticed that I have used scriptlet with the number 20000 in a couple of tags. This is the message number that serves as the alternate text for the captcha image as well as the default message shown in the text box provided for the user to key in the captcha verification code. This message text is defined in the text.properties file. The Ajax function chitra() that is triggered upon clicking the refresh icon in turn triggers the Java servlet "chitra_durga.jpg". The servlet generates and streams a fresh captcha image to the client browser.
• The last piece of customization on the signin html file is to modify the submit button action. Search for the input tag with type "Submit" and modify the code as given here
  

   <!-- Commented by V.Rao <td height="35"><input name="Submit" type="submit" class="psloginbutton" tabindex="4" value="<%=137%>" onclick="submitAction(document.login)"></td> -->  
   <!-- Added by V.Rao --> <td height="35"><input name="Submit" type="submit" class="psloginbutton" tabindex="4" value="<%=137%>" onclick="<%=psCtxPath%><%=psHome%>/Rao99.jsp"></td>  
  

• Open the text.properties file and add the code below at the very bottom
  

   # V.Rao [1-Sep-2014] Using 20000 and above for custom messages  
   20000=Enter the verification code appearing in the image  
  

• I also need to display a custom error message if the captcha validation fails. To do this open the error.properties file and add the code below at the very bottom
  

   # V.Rao [1-Sep-2014] Using 20000 and above for custom messages  
   20001=Verification code is required.  
   20002=Invalid verification code. Please try again.  
   20003=Verification failed.  
  

Step-4
In the HTML for the captcha elements, I had used the PeopleSoft delivered refresh icon - PT_REFRESH_ICN. Unfortunately this image is not available by default on the images folder in the web server. It is fetched from the database and cached in the webserver when a PeopleSoft page references the image. Therefore I decided to always have it available on the webserver. I did that by copying the refresh icon to the image folder located at 

 <%PS_HOME%>\webserv\<Domain>\applications\peoplesoft\PORTAL.war\<domain>\images  

Step-5
Create the JSP file that will intercept the login request and do the validation of the captcha entered by the user. The JSP does one of two things, forwards the login request to the psp servlet if captcha validation is successful or else re-load the login page with the custom captcha error message shown (error code = 20001). The code in my JSP file i.e Rao99.jsp is listed below.

 /**  
  * @author Venkateshwara Rao  
  * @Date  7-Sep-2014  
  */  
 <%@page import="com.octo.captcha.module.servlet.image.SimpleImageCaptchaServlet" %>  
 <%   
   String userCaptchaResponse = request.getParameter("jcaptcha");  
   userCaptchaResponse = userCaptchaResponse.trim();   
   Boolean captchapassed = false;  
   if( userCaptchaResponse.equals("") || userCaptchaResponse == null )  
   {  
 %>  
    <jsp:forward page="/psp/hcm92dmo/?cmd=login&errorCode=20001" >  
    </jsp:forward>  
 <%  
   }  
   else  
   {    // Captcha text is not blank or null  
        try  
        {   
             captchapassed = SimpleImageCaptchaServlet.validateResponse(request, userCaptchaResponse);  
        }  
        catch(Exception e)  
        {  
 %>  
     <jsp:forward page="/psp/hcm92dmo/?cmd=login&errorCode=20003" >  
     </jsp:forward>  
 <%  
        }  
        System.out.println("captcha passed = " + captchapassed ); /* Logging to weblogic console for debugging purpose */  
        if(captchapassed)  
        {  // On captcha validation success redirect to the psp servlet with login command  
 %>  
     <jsp:forward page="/psp/hcm92dmo/?cmd=login" >  
     </jsp:forward>  
 <%  
        }  
        else  
        {// On captcha validation failure redirect to the signon page with error code set to custom# - 20001  
 %>  
     <jsp:forward page="/psp/hcm92dmo/?cmd=login&errorCode=20002" >  
     </jsp:forward>  
 <%  
         }  
   }  
 %>  

The JSP file should be placed at this location on the web server

 <%PS_HOME%>\webserv\<Domain>\applications\peoplesoft\PORTAL.war\<Domain>  

All the modifications done above require a webserver bounce with cache clear before we can proceed to test.

Results
After the webserver bounce, access the PeopleSoft login page and you will see the login page with the Captcha image and the text below the password field  as shown below

Click on the refresh icon to receive a new captcha text/challenge as shown below

If the captcha verification code entered by the user is incorrect the login page is shown again with the custom captcha specific error message (this is the message I added to the error.properties file) as shown below

If the user managed to enter the correct captcha validation code but the PeopleSoft login credentials were incorrect then the login page is displayed back with the usual login id password incorrect message with a fresh captcha challenge as shown below

If the user manages to enter all the three parameters correctly then the servlet forwarding brings them to the homepage as shown below

The captcha validation success is also confirmed in the weblogic logs (with the system.out message I wrote in the JSP code) as shown below

After-thought 

Although it looks simple enough in retrospect, it took me a while to piece together this solution and many a things learnt along the way (things to do and not to do). If only Oracle would incorporate this feature there wouldn't be the need to put in this customized solution. At the end of the day, what I achieved here is more of a workaround than anything because the most logical place for the captcha validation call is within the psp/psc servlet code which validates the PeopleSoft user id and password. This is something customers cannot modify.

Working on this, took me back to my Java/J2EE developer days and made me realize App Designer is such a wonderful tool that simplifies so much of the development and code deployment aspects. With all the talk of JDeveloper replacing PeopleTools/AppDesigner makes me feel we will be going back to cavemen times. It is unlikely however powerful JDeveloper is, for it to replace the Application Designer's simplicity and ease of use.

Note: This solution/source code provided in this post is by no means a production ready one. It was a proof of concept done by me and you will have to make necessary changes at your end before deploying.

Implementing Captcha on PeopleSoft login page using JCaptcha - Part-1

In my previous post I discussed the steps to take for deploying the sample JCaptcha code within a PeopleSoft webserver and test it. In this post, I will be going through the steps to take in order to implement Captcha on the PeopleSoft login page using JCaptcha.  As this will be a long solution I'm breaking it into two parts.

Before I proceed, if you had followed the steps to deploy the sample war file and test that, you will have to undo all of the steps and bring your PeopleSoft instance to its original state.

A normal page is rendered by PeopleSoft servlet as shown below -

In essence, my captcha implementation solution approach intercepts the login request and validates the captcha challenge response before forwarding the request to PeopleSoft psp servlet as illustrated in diagram below 

At a high level, the implementation steps include - 

1. Copying the necessary JCaptcha jar file libraries and classes
2. Modify the PORTAL.war deployment descriptor file (web.xml)
3. Customizing the signin.html, text.properties and error.properties
4. Place the refresh image in the webserver image folder
5. Create a JSP file for the captcha validation

Step1:

From the JCaptcha 2.0 war file, extract the 6 jar files highlighted in the screenshot below and place them in the lib directory of the PeopleSoft Webserver - 

In your case the lib directory will be at this location - 

 <%PS_HOME%>\webserv\<domain>\applications\peoplesoft\PORTAL.war\WEB-INF\lib  

We need only the servlet class file (SimpleImageCaptchaServlet.class) from the 7th jar file of the war file. This is the class that renders the Captcha image.
Copy/Extract the class file to this location -

 <%PS_HOME%>\webserv\<domain>\applications\peoplesoft\PORTAL.war\WEB-INF\classes\com\octo\captcha\module\servlet\image  
 in my case  
 C:\psoft\pt85302\webserv\hcm92dmo\applications\peoplesoft\PORTAL.war\WEB-INF\classes\com\octo\captcha\module\servlet\image  

Note: You will have to create these folders (octo\captcha\module\servlet\image) as it won't exist.

Step2:
Now navigate to the WEB-INF folder and open the deployment descriptor file - web.xml and add the code below -

    <servlet>  
        <servlet-name>jcaptcha</servlet-name>  
        <servlet-class>com.octo.captcha.module.servlet.image.SimpleImageCaptchaServlet</servlet-class>  
    </servlet>  
   <servlet-mapping>  
       <servlet-name>jcaptcha</servlet-name>  
       <url-pattern>/chitra_durga.jpg/*</url-pattern>  
   </servlet-mapping>  

You may change the servlet mapping as per your wish.

Verification: If you have successfully executed these steps you can restart the webserver and do a quick test by accessing the servlet that renders the captcha image as shown below.

The rest of the solution will be covered in Part-2. For comments, feedback or questions drop a note.

Regards,
Rao

Test Drive JCaptcha API in PeopleSoft

The background
In Singapore where I have been working, most customers engage auditors to audit the PeopleSoft implementations and upgrades. One of the common finding off-late is the vulnerability of the application to coordinated attacks using scripts, bots etc (especially in the case of internet facing applications such as Candidate Gateway). 

The favourite remedy to this apparent drawback has been to think of implementing Captcha. It is my personal opinion that Captcha in itself does not offer a comprehensive solution to the problem. There are several blogs and articles on the net that have shown that Captcha can be broken (e.g. Using AI to beat CAPTCHA and post comment spam). 

At best it is a preventive measure that can give Organizations more time to react to an attack on the Web/IT infrastructure before the damage is extensive. This is akin to a moat surrounding the castle in ancient times which acted as a barrier to slow down an approaching army, giving the castle inhabitants time to react before the wall was breached. Nevertheless, until a better solution is found we will settle for a Captcha solution. 

Options

With this as the background, I set out to do a Google search to see if anyone has attempted and/or succeeded in this endeavor. However the search yielded very few links. Oracle support site also has no solutions on this topic (as of this writing). The most common and easy to implement solution is the re-captcha API from Google. This is good enough for internet web pages, blogs etc. There are few blogs (implementing-captcha-using-recaptcha-in.html) that discuss how to achieve this.

The drawback of using recaptcha is that the authentication of the response to the captcha challenge is done by google, and not within PeopleSoft application. The captcha challenge is sent by Google and so does the validation of the user response. This necessitates us to open the web/app connectivity to internet traffic so as to reach Google servers for obtaining the captcha validation result. This is virtual no no for Organizations that keep the application servers secure and out of reach by putting them behind firewalls, DMZ's and what not. 

Thus the only viable option was to have a home-grown (so as to speak) solution that will generate a Captcha challenge from PeopleSoft and does the authentication within PeopleSoft. I found this blog (captcha-implementation-of-peoplesoft.html) that succeeded in doing that.

The chosen one
However rather than reinvent the wheel from scratch, I was looking for something I could re-use and implement. Thus I discovered the JCaptcha Open source API and decided to test the waters with this one. The sourceforge page has the JCaptcha version 1.0 API that produces a very basic captcha image (in black and white) as-is unless of course you decide to modify the source code. 

Then I discovered the JCaptcha Version 2.0 alpha API here that produced a more colourful Captcha image. The wiki also has a nice write up on testing their API. 

Test Drive

The steps to manually deploy the JCaptcha sample application onto a PeopleSoft web server is described below -

• Download the war file (jcaptcha-integration-simple-servlet-sample.war) to a local folder on your PC/server.
• Copy the war file to this location  -
  

   <%PS_HOME%>\webserv\<domain>\applications\peoplesoft  

  In my case I have a windows VM. So the location is
  

   C:\psoft\pt85302\webserv\hcm92dmo\applications\peoplesoft  
  

• Modify the application.xml by adding the code below. I chose the name "Tungabhadra" for the context-root. You can name it anything you like.
  

    <module>  
     <web>  
      <web-uri>jcaptcha-integration-simple-servlet-sample.war</web-uri>  
      <context-root>/tungabhadra</context-root>  
     </web>  
    </module>  
  

  The application.xml can be found at 
  

   <%PS_HOME%>\webserv\<domain>\applications\peoplesoft\META-INF  

  For me the location is 
  

   C:\psoft\pt85302\webserv\hcm92dmo\applications\peoplesoft\META-INF  

• Restart the webserver
• Access the sample captcha page that we just deployed into PeopleSoft webserver by accessing the URL -
  

   http://<webserver_host_name>:<web_http_listener_port>/tungabhadra/index.jsp  
  

  Note: This is the same listener port which your PeopleSoft application listens to.
  
• The version 2.0 captcha page as seen on the browser -
  
  
  

In case you were wondering what the version 1.0 captcha looks like it renders an image like this - 

Verdict
The JCaptcha v2.0 Open Source API is easy to use and implement within PeopleSoft and it does a pretty good job of producing a nice modern looking random captcha image even with the basic DefaultGimpyEngine WordGenegration classes. The nice thing about this solution is that the image is created on the fly (using a word to image java classes) and directly streamed to the HTTP output and rendered on the client browser. The captcha authentication classes are deployed within the PeopleSoft application hosted within your own infrastructure which is exactly what I set out looking for. 

Having tested the waters now it is time to do an actual implementation on the PeopleSoft application (I will be attempting this on the signin page).  I will post the solution once I have it ready.