Improve your Web Forms deployment using the Java JDAPI library

Did you (or one of your customers) ever encounter ORA-00942 (table or view does not exist) while working in a (Web) form? No, then you can skip this post.

In my current project we have about twenty database roles which are used to restrict access to forms and database objects. We use Designer as the development tool for forms and the database objects. In order to prevent the ORA-00942 error, you have to keep track of which object privileges are needed for someone who wants to run a form. First of all he must have a role which may start the form, second all necessary database objects must be granted to that role. One way to check this, is to compile the form by a user with only that role granted. This works fine for all code in Program Units or Triggers but it does not warn you when the user has no access to the objects in the Data Blocks of the form, for instance when he may not select an object according to his role but the Data Block property for that object has the Query allowed flag set to true.

Enter JDAPI. JDAPI (Java Design-time API) is a Java 2 API for programmatically loading, creating, manipulating, saving and compiling Oracle Forms applications. I have used JDAPI to create a simple Java program which parses a form and lists the object access privileges. You can find the Java program at http://cvs.sourceforge.net/viewcvs.py/transferware/tw/common/src/java/ListFormsDbAccess.java?rev=1.2&view=log. Sample output of the Java program is:

FORMS_DB_ACCESS_ags1200f = AGS_V_GEBRUIKERSGROEPEN:S,AGS_V_GBP_PVE:SIUD

This means that form ags1200f has two database blocks for objects AGS_V_GEBRUIKERSGROEPEN (Select privilege needed only) and AGS_V_GBP_PVE (with necessary privileges Select, Insert, Update and Delete).

This output can be fed to the SQL*Plus script check_db_access.sql (see http://cvs.sourceforge.net/viewcvs.py/transferware/tw/common/src/sql/check_db_access.sql?rev=1.5&view=log). 

This script must be invoked by the user whose privileges need to be checked. The script performs synonym translation and checks the access using the Oracle dictionary.

The following pseudo code checks whether your application has the correct priviliges with respect to forms:

revoke all roles from a test user

grant all mandatory application roles to the test user

for each non mandatory application role

do

grant the application role to the test user

compile all forms which may be started by the application role

check the database access to the objects found in the form

revoke the application role from the test user

done

You can use automated tools like Ant or Make to make this part of your deployment procedure.

You will never see ORA-00942 again.