August 31, 2011

Query groups application security

The standard Maximo 'Security Groups' application provides an easy way to view and modify users access to applications. However it is not always easy to have an overview of what groups are granted to access a specific application or what applications can be accessed by a specific group.
With the following SQL query you can get a report of read/write permissions granted to all the groups.

SELECT
  maxapps.app,
  maxapps.description,
  maxgroup.groupname,
  maxgroup.description,
  (SELECT COUNT(*) FROM applicationauth WHERE groupname=maxgroup.groupname AND app=maxapps.app AND optionname='READ') appread,
  (SELECT COUNT(*) FROM applicationauth WHERE groupname=maxgroup.groupname AND app=maxapps.app AND optionname='SAVE') appsave,
  (SELECT COUNT(*) FROM applicationauth WHERE groupname=maxgroup.groupname AND app=maxapps.app AND optionname='INSERT') appins,
  (SELECT COUNT(*) FROM applicationauth WHERE groupname=maxgroup.groupname AND app=maxapps.app AND optionname='DELETE') appdel
FROM maxapps, maxgroup
ORDER BY maxapps.app, maxgroup.groupname;

If you are using the Oracle's than it is possible to list all sigoptions using CURSOR statement.

SELECT
  maxapps.app,
  maxapps.description,
  maxgroup.groupname,
  maxgroup.description,
  CURSOR (SELECT optionname FROM applicationauth WHERE groupname=maxgroup.groupname AND app=maxapps.app) AS auth
FROM maxapps, maxgroup
ORDER BY maxapps.app, maxgroup.groupname;

It is possible to narrow down the results of the query to understand what groups have access to a specific application adding a where clause in the previous query. For example here is how to list the permissions to ASSET application:

SELECT
  maxapps.app,
  maxapps.description,
  maxgroup.groupname,
  maxgroup.description,
  (SELECT COUNT(*) FROM applicationauth WHERE groupname=maxgroup.groupname AND app=maxapps.app AND optionname='READ') appread,
  (SELECT COUNT(*) FROM applicationauth WHERE groupname=maxgroup.groupname AND app=maxapps.app AND optionname='SAVE') appsave,
  (SELECT COUNT(*) FROM applicationauth WHERE groupname=maxgroup.groupname AND app=maxapps.app AND optionname='INSERT') appins,
  (SELECT COUNT(*) FROM applicationauth WHERE groupname=maxgroup.groupname AND app=maxapps.app AND optionname='DELETE') appdel
FROM maxapps, maxgroup
WHERE maxapps.app='ASSET'
ORDER BY maxapps.app, maxgroup.groupname;

August 8, 2011

Call Java method on action menu or toolbar button click

This entry is part of the Maximo Java Development series.

In this post I will describe all the steps needed to execute custom Java code upon the selection of an entry in the action menu or the click of a button in the toolbar.

Add a sigoption

First you need to create a new sigoption. Open the Application Designer and select the application you want to modify. Select 'Add/Modify Signature Options' and create a new signature RUNJAVA.

Add the menu entry

The second step is to create the custom entry in the action menu (or a new button).
Select 'Add/Modify Select Action Menu' and create the new entry RUNJAVA of type OPTION. Beware that the name must be the same as the sigoption.

Grant sigoption permissions and test the dummy action

Before going on I recommend the test that the new menu entry is visible.
Go in the Security Groups application, select the group you want to be able to see and execute the custom code. Go in the Applications tab and select your application. Now you should see the RUNJAVA sigoption on the bottom of the page. Click on the checkbox to grant access to the new menu entry.

Disconnect and login with a user belonging to the chosen group, go in the application and verify that you see the new menu entry.
Now it's time to attach your Java code to it.

Write your Java code

The most suitable place to put your Java code is in the 'App Bean Class' which is the custom Java class that manages the application.
To determine which Java bean handles the application, open Application Designer and select the application, from the Action menu choose 'Toggle Show All Controls' and display the properties of the 'presentation' section. The 'App Bean Class' defines the Java class that you need to customize. Here is the WOTRACK application App Bean Class definition.

You should extend this class and create a new method with the same name of the sig option in the Java bean class (RUNJAVA in our example).
Don't forget to change the App Bean Class property setting your own custom class.
Here is an example of how the custom class should look like.

package cust.psdi.webclient.beans.workorder;

public class CustWorkorderAppBean extends psdi.webclient.beans.workorder.WorkorderAppBean 
{
  public int RUNJAVA() throws MXException, RemoteException
  {
    // put your custom code here
    return EVENT_HANDLED;
  }    
}

Implement your custom logic in the RUNJAVA method and copy the class file under [SMPDIR]\maximo\applications\maximo\maximouiweb\webmodule\WEB-INF\classes folder. Rebuild and redeploy Maximo EAR file.

August 4, 2011

How to execute an Oracle stored procedure from Java code

This is a working Java method that can be used to execute an Oracle stored procedure. It can be linked to standard Mbo methods, application beans, actions, toolbar buttons, etc.

public int callOracleProcedure(String procName, int arg1) throws MXException, RemoteException
{
  int ret;

  logger.info("Calling " + procName + "(" + arg1 + ")");

  MXServer mxServer = MXServer.getMXServer();
  UserInfo userInfo = mxServer.getSystemUserInfo();
  Connection dbConnection = mxServer.getDBManager().getConnection(userInfo.getConnectionKey());

  try
  {
    CallableStatement cs = dbConnection.prepareCall("{call " + procName + "(?)}");
    cs.setInt(1, arg1);
    ret = cs.executeUpdate();
  }
  catch (SQLException e)
  {
    logger.error("SQL Error", e);
    throw new MXApplicationException("Error in procedure " + procName, e.getMessage());
  }
  finally
  {
    mxServer.getDBManager().freeConnection(userInfo.getConnectionKey());
  }

  return ret;
}