Oracle Forms (using jacob) and Java 7 update 40 gives a warning that can't be ignored

This week alone, I heard of two customers that were having problems with Oracle Forms using WebUtil and jacob. Especially jacob proves to be a bit of a problem.

Let's backup up for a bit: if you're not familiar with webutil and jacob, first of all, look at oracle support ID 1093985.1 which tells you how to install and configure a lot of things. If this still doesn't ring any bells, you will probably not use webutil/jacob, so you can skip this entire post...

Anyway. After configuring and ( most importantly) signing your jacob.jar, everything worked fine. That is, until Java 7 update 40. Before that, you always had the option to "accept and always ignore" the message about the certificate, which was self-signed and therefore, not trusted.

Then with Java 7 update 40, A new security risk profile was implemented and an unsigned jar file or a jar file signed by an unknown publisher will get you the warning:
"Running applications by UNKNOWN publishers will be blocked in a future release because it is potentially unsafe and a security risk". See this page for details.

You can tick a check-box "I accept ..." and run anyway, but this message will come up EVERY time you start the Forms application. Obviously, application users don't want to tick the check-box and press that Run button every time they start their application. They want their application to start without any interruptions.

So, the way to do this seems rather straightforward. Just make jacob.jar use a trusted certificate. When signing the jar file, almost everyone used the self-signed certificate. This was rather easy to do and there was no problem at all.

First solution: instead of using a self-signed certificate, just use a real certificate issued by a CA. Drawbacks: this costs (some) money and the certificate will eventually expire. So this is not what we will be focusing on. There is an easier way.

Second solution: use the self-signed certificate as trusted. This is done by essentially:

• sign the jacob.jar with the provided signing batch file (sign_webutil.bat / sign_webutil.sh)
• See note 1076945.1
• reuse the created keystore to extract the certificate
• use keytool to extract this information
• import the certificate in your browser as a trusted certificate
• I placed the resulting crt file on my laptop and double-clicked. Just follow the wizard for importing the certificate

Details of these steps:

I modified the sign_webutil.bat (Yes I know, I used Windows...) to suit my needs (Names, Passwords, Locations, etc). After that, I issued the following commands:

set CLASSPATH=C:\Oracle\Middleware\as_fr\jdk\bin

set PATH=C:\Oracle\Middleware\as_fr\forms\java; C:\Oracle\Middleware\as_fr\forms\webutil

sign_webutil.bat C:\Oracle\Middleware\as_fr\forms\java\jacob.jar

A keystore ".keystore" was created in my home directory, so I extracted the certificate using keytool:

keytool -export -alias %JAR_KEY% -file %KEYSTORE%.crt -keystore %KEYSTORE% -storepass %JAR_KEY_PASSWORD%

The parameters all come from the orignal sign_webutil.bat and I mainly copied that file and modified it a bit for ease of use with the export option.

The resulting file ".keystore.crt" was used to import into the client keystore as a trusted certificate (just by double clicking it and following the wizard).

After these steps, the jar file was signed with a certificate that is now trusted. Trusted by my laptop that is, so everyone using this application must also import this certificate. Could be a problem, but most customers I see use Oracle Forms mainly internally, so distributing the certificate will not be much of a problem.

On first access of the application, a new message appears. Somewhat like this:

There is another checkbox this time, saying "Do not show this again for apps from the publisher and location above". Effectively, this gives us the same functionality as before: the "accept and always ignore" we always had. After this, no more messages will be displayed...

Using sqlplus input parameters to call specific scripts IF - THEN - ELSE style

Quite a while ago, I described using "NVL" for SQL*plus commandline parameters. Using this technique, you can create scripts that can cope with commandline parameters that are not always supplied on the commandline. So you don't always know whether a parameter is supplied or not and you don't want the script to go asking questions like "Enter the value for 4:" if you didn't supply a value for the fourth parameter.

A modified script made sure that all parameters were properly initialized and could be used in further statements without any prompts from sqlplus:

  COLUMN inputpar01 NEW_VALUE 1 NOPRINT
  COLUMN inputpar02 NEW_VALUE 2 NOPRINT
  COLUMN inputpar03 NEW_VALUE 3 NOPRINT
  COLUMN inputpar04 NEW_VALUE 4 NOPRINT
  select 1 inputpar01
       , 2 inputpar02
       , 3 inputpar03
       , 4 inputpar04
    from dual
   where 1=2;
  --
  PROMPT connecting as &1
  CONNECT &1/&2@&3
  SELECT username from user_users;
  PROMPT value for parameter 4 = &4

Recently, I had a question if the user could be prompted to enter the value for the fourth parameter if this had been omitted from the command line. So, you supply parameters 1, 2 and 3 and leave out parameter 4, which the script actually needs (for whatever reasons).
This can be accomplished using extra scripts ("helper" scripts).

I create the first helper script, that asks for a parameter and I call it "ask_parameter.sql". The contents are very simple:

  accept &1 prompt "What is the value for parameter &2 : "

A second script "dummy_script.sql" is created and has no content. This script will be used when a value is actually provided for the fourth parameter.

After these two scripts are created, we modify the original script to:

  COLUMN inputpar01 NEW_VALUE 1 NOPRINT
  COLUMN inputpar02 NEW_VALUE 2 NOPRINT
  COLUMN inputpar03 NEW_VALUE 3 NOPRINT
  COLUMN inputpar04 NEW_VALUE 4 NOPRINT
  select 1 inputpar01
       , 2 inputpar02
       , 3 inputpar03
       , 4 inputpar04
    from dual
   where 1=2;
  --
  PROMPT connecting as &1
  CONNECT &1/&2@&3
  SELECT username from user_users;
  PROMPT value for parameter 4 = &4
  --
  -- Check and set the fourth parameter 
  -- (NEWLY ADDED FUNCTIONALITY)
  --
  COLUMN ask_parameter NEW_VALUE ask_command NOPRINT;
  --
  SELECT nvl2( '&4'
             , 'dummy_script.sql'
             , 'ask_parameter.sql 4 my_name_for_par4'
             ) ask_parameter
  FROM dual;
  --
  set verify on
  set feedback on
  set termout on
  --
  -- Run the script determined in the previous step
  --
  @@&ask_command.
  --
  PROMPT value for parameter 4 = &4

The new part does the following:

• define a new parameter "ask_command" that will contain the name of the script to call
• based on the value of parameter 4, either select the value "dummy_script.sql" if it already contains a value, or select the value "ask_parameter.sql" (along with two new parameters for that script) if it contains no value
• run the script selected in the previous step. This is why we need the dummy script: if you just leave this NULL, sqlplus will error on the @@ command.
• if this is "ask_parameter.sql", then within that script &1 and &2 represent the parameters for that script and not the parameters for the main script. However, if you set "4" using the accept, it will set the fourth parameter for the main script!

In this case, when you call the main script using "testuser testpassword testdatabase"as parameters, you will be prompted:

  What is the value for parameter my_name_for_par4 :

This sets the value for parameter 4 of the main script, after which you can use it for any purpose (like using it as a parameter for another script).

So by using (generic) helper scripts, you can check, replace or prompt for any parameters. Just make sure that the scripts are in the same directory, or add a (relative) path to the helper scripts, should you decide to place them in a subdirectory (which is recommended for simplicity).