Technical

Integrating OBIEE11g with Hyperion Shared Services Security - An Example Workaround using the HSS Security API and BISQLGroupProvider

Mark Rittman

• 10 min read

One of our customers in the UK recently asked us to help them upgrade an existing OBIEE 10g system to the 11g release, to take advantage of the enhanced visualisations, scorecards and KPIs, iPhone and iPad clients and so on. What made this upgrade interesting is that their OBIEE 10g system integrated with Hyperion Workspace and Hyperion Shared Services, providing single sign-on between Workspace and OBIEE 10g, and user authentication through the list of users and groups in Shared Services. This integration relied on two OBIEE 10g features introduced with the 10.1.3.4 release; the ability to integrate OBIEE 10g into the Hyperion Workspace portal, passing the logged-in users' credentials to OBIEE 10g's Presentation Server through a single sign-on token, and a BI Server feature called the Hyperion Common Security System Authenticator, as shown in the diagram below:

NewImage

The problem was though that neither of these two features were supported with OBIEE 11g; you can't integrate OBIEE 11g's dashboards and analyses into Hyperion Workspace, you can't perform single sign-on between Workspace and OBIEE 11g's Presentation Server, and most importantly, you can't authenticate against Shared Services using the HSS Custom Authenticator, as it's not supported with OBIEE 11g. Which made things interesting.

So our first thoughts were around the integration with Shared Services; was this in fact needed? The way Shared Services works is a bit like the WebLogic embedded LDAP server that comes with OBIEE 11g, in that whilst you can create users and groups within it, this is really only meant to get you started. When you deploy your OBIEE 11g system for real, it's expected that you'll instead link WebLogic's security services (in Fusion Middleware terminology, Oracle Platform Security Services, strictly speaking part of Fusion Middleware rather then WebLogic) to a corporate directory such as Oracle Internet Directory, or Microsoft Active Directory. In most cases, therefore, customers use Shared Services as a front-end to a directory such as Oracle Internet Directory, which would have made our life easy with this upgrade as we could then have just configured the new OBIEE 11g system to connect to their corporate directory, instead of Shared Services, and the issue would have been solved.

Similarly, the "canonical" solution to the Workspace/Presentation Services integration issue would have been to have an overarching SSO system, such as Oracle Access Manager, that controlled access to both Workspace and Presentation Services, so that the ideal upgraded environment would look something like this (apologies if the SSO bit isn't quite right, but you get the gist):

NewImage

However, for this particular upgrade it wasn't quite as simple as this, because:

  • There wasn't a corporate SSO solution, so a product like Oracle Access Manager wasn't really viable in the short-term
  • User details were held in an Oracle Internet Directory LDAP server, which was a start
  • But more importantly, group membership details which were used for controlling access to an Essbase database, were only held in Shared Services (what's termed Native Authentication)

The SSO issue, in the end, we couldn't get around, but the client could live with this. What was more of an issue though was OBIEE 11g's inability to connect to the Shared Services Native Directory, which was the only store of group membership for the application being upgraded, an Essbase sales analysis database accessed in the past by a set of OBIEE 10g requests and dashboards.

So what to do now? What about the CSS Token Passthrough feature introduced with the 11.1.1.5 release of OBIEE 11g - could that help? What does it actually do?

NewImage

In fact this is a form of single sign-on between OBIEE 11g and the Hyperion/Oracle EPM Suite tools, but going in the other way to how it worked with 10g - OBIEE's BI Server generates the same kind of CSS Token that Workspace was passing to OBIEE 10g's Presentation Server in the previous integration, but this time it's being passed from the BI Server to whatever Hyperion data source the user is querying, as an alternative to the usual shared credentials that are passed via a connection pool setting to the data source. To get this working, you need to follow a set of instructions in the release notes for your OBIEE 11g release, including configuring a file that tells the BI Server how to generate the CSS Token. As such, it's really meant for integration with EPM tools such as Financial Management, and for Essbase applications that want to authenticate via a CSS Token rather than regular user IDs and passwords. So it's not going to help in this situation.

In fact that solution we came up with, at least initially, was as follows:

  • The OID LDAP server would be connected to via WebLogic's security services to authenticate usernames and passwords
  • A Java utility would periodically scan the Shared Services native directory, using the Java-based Shared Services Security API for this purpose, to retrieve membership of groups for these users
  • This utility would then populate two database tables, once for a list of groups and another for a list of which users belonged to these groups
  • Then, as per this blog post from a few months ago, we'd use the BISQLGroupProvider WebLogic authenticator to pass this group membership back to WebLogic, and then on to OPSS which would then map the groups to application roles.
  • Application roles would be set up in Fusion Middleware's policy store to correspond to the set of Shared Services groups we were interested in, and these groups (obtained via the Security API sync) would then be mapped to the application roles as a one-off exercise

The diagram below shows the approach we initially took, along these lines:

NewImage

A major difference between this approach and the one used with OBIEE 10g, where the HSS custom authenticator authenticated against Shared Services "on-demand", was that this new approach sync'd the group membership details in Shared Services with the two tables on a regular (in this case, five minutes) basis; therefore, there is always a chance that for a few minutes at least group membership in Shared Services might be slightly "ahead" of the two tables, at least until the next five minute sync came along. But given the circumstances this seemed not too onerous.

The tables that this approach populated were the standard two tables expected by the BISQLGroupProvider authenticator introduced with OBIEE 11.1.1.5, which allows group membership to be retrieved from database tables and used to assign users to groups in a similar way to the way we used init blocks in earlier versions of OBIEE. The first table (GROUPS) contains the list of groups, whilst the second (GROUPMEMBERS) contains one or more row per group, listing out individual users that are part of that group.

NewImage

The BISQLGroupProvider authenticator, a WebLogic Server authenticator that allows WebLogic to use outside schemes to authenticate or authorise users (described in Doc.ID. 1428008.1 on My Oracle Support), does of course allow you to use different table names and structures, but the default SQL statements that it uses to retrieve group membership details assume these two table structures, so its easier if you can use the default tables if possible.

Reading from Shared Services using the Java HSS Security API worked in two steps. The first step set up the connections to Shared Services and to the database schema that held these two tables, like this: (note that this is only a code snippet, and I have edited out other parts for clarity - also please don't ask me for the full set of code or support to get your own implementation working, just take this as a bit of a tip):


public static void main(String[] args) 
    {
    	epmcssapimain ct = null;
    	try {
	        
          // Initialization - Read from Config Files & Database Usernames/Passwords
          //String configFile = args[0];
          String dbUser = args[0];
          String dbPassword = args[1];
          String dbSID = args[2];
          String dbPort = args[3];
          String dbHost = args[4];
          String epmUser = args[5];
          String epmPassword = args[6];
    	    ct = new epmcssapimain(epmUser,epmPassword);
          
         String jdbcURL = "jdbc:oracle:thin:@"+dbHost+":"+dbPort+":"+dbSID;
          ct.dumpNativeGroupMembers(jdbcURL,dbUser,dbPassword);

    	} catch (Throwable e) {
    		e.printStackTrace();
    		System.out.println("Could not run sample because of error " 
    				+ e.getClass().getName() + ": " + e.getMessage());
    	} finally {
    		if (ct != null)
    			ct.shutdown();
    	}
    }

The second part was then called from this first bit of code through the "ct.dumpNativeGroupMembers" invocation, and actually made the connection to Shared Services and wrote the entries it found to the two database tables:


protected void dumpNativeGroupMembers (String URL,String USER, String PASSWORD) {
        try{
          DriverManager.registerDriver(new OracleDriver());
          Connection conn = DriverManager.getConnection(URL,USER,PASSWORD);
          conn.setAutoCommit(false);
          CSSGroupIF[] cssGroupIF = cssAPI.getGroups(context, principal, "*");
          CSSUserIF cssuser;
          CSSGroupIF cssgroup;
          String []user = null;
          String []group = null;
          Statement stmt = conn.createStatement();
          stmt.executeUpdate("DELETE GROUPS");
          stmt.executeUpdate("DELETE GROUPMEMBERS");
            for (int i=0; i <cssGroupIF.length; i++){
                try{
                System.out.println ("EPM Groups + Name + Identity: "+ "+ "+ cssGroupIF[i].getName()+" + "+cssGroupIF[i].getIdentity());
                System.out.println("INSERT INTO GROUPS(G_NAME) VALUES ('"+cssGroupIF[i].getName()+"')");
                stmt.executeUpdate("INSERT INTO GROUPS(G_NAME) VALUES ('"+cssGroupIF[i].getName()+"')");
                user = cssGroupIF[i].getUsersList(context, principal, true);            
                for (int j=0; j < user.length; j++) {
                    try{
                  cssuser = cssAPI.getUserByIdentity(context, principal, user[j]);   
                  System.out.println("Users in "+cssGroupIF[i].getName()+" Group: "+cssuser.getLoginName());
                  System.out.println("INSERT INTO GROUPMEMBERS(G_NAME,G_MEMBER) VALUES ('"+cssGroupIF[i].getName()+"','"+cssuser.getLoginName()+"')");
                  stmt.executeUpdate("INSERT INTO GROUPMEMBERS(G_NAME,G_MEMBER) VALUES ('"+cssGroupIF[i].getName()+"','"+cssuser.getLoginName()+"')");
                    }catch(Exception ex){System.out.println(ex.getMessage());}
                }
              group = cssGroupIF[i].getGroupList(principal, true);
              for (int k=0; k < group.length; k++) {
                  try{
                cssgroup = cssAPI.getGroupByIdentity(context, principal, group[k]);
                System.out.println("Groups in "+cssGroupIF[i].getName()+" Group: "+cssgroup.getName());
                System.out.println("INSERT INTO GROUPMEMBERS(G_NAME,G_MEMBER) VALUES ('"+cssGroupIF[i].getName()+"','"+cssgroup.getName()+"')");
                stmt.executeUpdate("INSERT INTO GROUPMEMBERS(G_NAME,G_MEMBER) VALUES ('"+cssGroupIF[i].getName()+"','"+cssgroup.getName()+"')");
                }catch(Exception ex){System.out.println(ex.getMessage());} 
              }
                }catch(Exception ex){System.out.println(ex.getMessage());}
                //rs.close();
            }   
            conn.commit();
        }catch(Exception ex){
          System.out.println(ex.getMessage());
        }
      }

Running this code, plus all of the headers and other stuff around it, on a regular five-minute basis meant that the two database tables contained the group membership otherwise only held in Shared Services. In fact, the approach using BISQLGroupProvider wasn't the first approach we thought of; our first idea was to use methods within Fusion Middleware's JMX MBeans to directly write to Fusion Middleware's policy store to add group members to application roles (the way that OBIEE ultimately assigns users to its own groups) but our feeling was that direct manipulation of the policy store like this might not be too reliable, so we went for the database tables approach.

Those of you that know OBIEE 11g security well have probably spotted the remaining weak link in this approach - whilst this will assign users to groups well, if a new group comes along there probably won't be a corresponding application role for the Fusion Middleware policy store to map them to. In practice, for this implementation the groups were fixed so there was no practical issue, but for a full implementation, you'd need to find a way to create (or replace/delete) application roles in the policy store to match any new groups that the code above encounters, as said previously through JMX MBean interfaces to the policy store in what would hopefully be a reliable way.

So where did this leave us in the end? Well, as said before, the proper, long-term approach for this would be to move group membership out of Shared Services' Native Directory and into Oracle Internet Directory, which would then remove the need for this additional sync process. The other, longer-term (and speculative) wish would be that Oracle unify OBIEE 11g and their EPM Suite security so that it all uses the same, single OPSS framework, and make it possible to install OBIEE and EPM Suite in the same domain, which would mean that SSO would be simpler and they'd all share the same users, groups and application roles.

And of course, in one final twist specific to this implementation, the client's OID version turned out to be older than the minimum version supported by OBIEE 11g and Fusion Middleware 11g, so we ended up using init blocks to authenticate against OID and init blocks again to read the database tables, as you can't use WLS authenticators for one part of the process but use init blocks for the other - but that was specific to this one client and wouldn't normally be the case.

Anyway, I thought one or two readers might be interested in the story, and here's the key takeaways:

  • OBIEE 11g doesn't support the HSS Custom Authenticator that 10g used to connect to Shared Services, so you'll need to find another way to get users and groups from Shared Services
  • In practice, Shared Services often acts only as a front-end to a corporate directory such as OID, so in those cases you'd just connect WebLogic's security setup to that instead, and you'll be OK
  • There's no real replacement for the point-to-point SSO between EPM Workspace and OBIEE's Presentation Services, so you'll need to consider a wider SSO system such as Oracle Access Manager instead
  • If you need to still get group membership, for example, from Shared Services, then a sync using the Shared Services Security API and database tables may be a workable solution, using the new BISQLGroupProvider authenticator available from OBIEE 11.1.1.5 onwards
  • Watch out for minimum versions of Oracle and third-party directory tools supported by FMW11g - our client's version was too old and therefore we had to use init blocks rather than WLS authenticators

Finally - in terms of follow-up comments - happy to answer general ones but we can't really get into the Java code or specifics for your setup, take this as some pointers and partner experiences only. Thanks also to Adam Seed (consultant on the ground) and of course Venkat (the Java code) for your assistance with this one.

Sign up for more like this.

Enter your email
Subscribe