Monday Oct 24, 2011

Using OES11g from the Oracle Database

Future versions of the Oracle database may include integration with Oracle Entitlement Server for fine grained authorization configuration.  However with the current versions (11g, 10g for example) any such integration is not available out of the box.

How then might we use OES to protect data in an Oracle database ?

As an example, consider a stored procedure querying and returning values from some database tables.  We can use OES to provide authorization on this data in the following way: we ask OES for an authorization decision and if the decision is allow we interpret any obligations as additional where clauses that we use to constrain the queries.

With OES performance is best if we can make the call from a Java SM client as we can then benefit from local copies of the authorization policies This reduces the overhead of the call to OES to the microsecond level.  So in the case of our stored procedure, if it is being invoked from Java, then it would be best to call OES from Java and pass in any constraints to the stored procedure as parameters.  However this may not always be possible.

In this case we can use the facility of the Oracle database to load java classes and to call them from stored procedures.  As OES offers a webservice XACML interface one could load some Java code that would call out to OES. Another technique would be to use the OES gateway from my previous posting, calling it using a very simple Java class that does a HTTP GET for the appropriate URL.

This set of scripts and SQL demonstrate how to load such a Java class to the Oracle database and configure a database function to allow the Java class to be called from a sample SQL script. If one is using Oracle 10g database then as it has a 1.4 JDK one must compile the class with JDK 1.4.  Oracle Database 11g has a 1.5 JDK so JDK 1.5 can be used in that case.

 The steps to prepare the example are:

  • compile the Java class with the appropriate JDK (use the script)
  • load the java classes to the data base (use the script)
  • run the database preparation script to define a function to interface to the Java class and allow the database user appropriate permissions to execute Java code.  The example assumes the user SYSTEM is being used.
  • run the SQL script to show the usage of the call to OES.  It runs a raw query on the scott.emp table with no constraints and then it calls the Java class which will call the OES Gateway to recover an authorization from OES.  If there are obligations returned the procedure looks for an obligation key of scott.emp and if it finds one it uses the value--for example (sal<2000)-- as a constraint when querying the table.


Monday Oct 17, 2011

OIA: Entitlements outside roles in BI Publisher

My colleague Rene has some great postings explaining the importance of keeping an eye on entitlements that fall outside roles whilst developing an RBAC model and how to achieve that within OIA.

In this posting I just add another example report which will expose entitlements falling outside roles but this time formatted for use with BI Publisher.  This will be useful to customers wishing to use BI Publisher as a reporting tool.

When loaded into BI Publisher the report will generate a listing by Business Unit, by Resource, by user for all the user's entitlements that fall outside the role model.  You can specify a Business Unit and Resource as parameters to the report.  This report will only include attributes flagged as minable to allow it to avoid including attributes that are unimportant from an entitlement  point of view (like Firstname, for example).

 The two BI Publisher files which define this report--the.xdo file which contains the SQL definition and the .rtf formatting file--are available here.

Monday Apr 12, 2010

The Sun Role Manager (Oracle Identity Analytics) webservices interface

Oracle Identity Analytics (lately Sun Role Manager) provides a web service interface to query and update it's data.  Data such as roles, business units, users and audit information can be managed in this way.

This small JDeveloper project consumes the SRM 5.03 WSDL and demonstrates some calls to the web service.

You will need to parameterize src/com/oracle/gte/oia/ with the correct URL and sample role and business unit names for your deployment of SRM.  Recompile and then run
The authentication to SRM is done using the WS-Security UsernameToken profile, see here for a description. To provide the username/password to SRM the class src/com/oracle/gte/oia/util/handlers/ implements a handler that inserts the appropriate security header into outgoing SOAP messages.

Friday Nov 13, 2009

In the mire: cleaning open provisioning tasks in Oracle Identity Manager

Looking at Oracle Identity Manager (OIM) I found I was accumulating open provisioning tasks that had either failed or that I simply no longer wanted.  The problem is the product does not offer a way to delete them.  Furthermore some resources (like the Sun DS Connector) will not allow you to start a new provisioning task for that resource until the old one has completed.  The obvious scheduled task 'Remove Open Tasks', as described here  appears to be more a cosmetic cleanup of some views into open tasks.  In fact looking into the task code, it does a time based 'DELETE from' on the OTI table--this turns out not to resolve the open provisioning task problem.  So, short of reverting to a clean snapshot of the database, what to do ?  I should say that the following is more in the way of an exploration that a recommendation.

The folks on the OTN forum offered some help but that was inconclusive.  Time to roll up the sleeves.

There are 225 tables in the OIM schema. Which ones are involved when a provisioning task is created ? What we need is a way to diff the database before and after an operation.  This will give an idea of the tables involved, the data model OIM is using and may allow us to remove those tasks--however recommended that may be.

For the purpose of reverse engineering table usage for individual application actions (such as in my case initiating a provisioning task) what we want is something that will indicate table data that has changed and do it's best to present those changes in a text form.  The delta will typically be small so we do not require a fancy diff capability.  It would be nice if the tool could work against Oracle, MS SQL Server and MySQL.  'Ah feel a wee tool comin' oooon.

This JDBC based tool, DbDump, does it's best to dump a specified set of tables to a text file. You will need to configure the jdbc properties and copy in the database driver jar files. We can then run a diff tool to compare before and after outputs.  The command line diff tool is adequate though windiff.exe or for example are easier to read.

< 'ADMINSERVER_WLSTORE'::'ID:-1','TYPE:-1','HANDLE:2''RECORD:000000707B7365727665723D41646D696E53657276657221686F73743D3132372E302E302E3121646F6D61696E3D626173655F646F6D61696E2173746F72653D41646D696E5365727665725F4F494D5F4A44424353544F5245217461626C653D41646D696E5365727665725F574C53746F72657D980976C5055875C800000124D3BC92EF'
> 'ADMINSERVER_WLSTORE'::'ID:-1','TYPE:-1','HANDLE:2''RECORD:000000707B7365727665723D41646D696E53657276657221686F73743D3132372E302E302E3121646F6D61696E3D626173655F646F6D61696E2173746F72653D41646D696E5365727665725F4F494D5F4A44424353544F5245217461626C653D41646D696E5365727665725F574C53746F72657D980976C5055875C800000124D3C43CE8'
> 'AUD_JMS'::'AUD_JMS_KEY:187','AUD_CLASS:UserProfileAuditor','IDENTIFIER:86','JMS_VALUE:BLOB','DELAY:1','FAILED:0','CREATE_DATE:2009-11-8','UPDATE_DATE:2009-11-8''PARENT_AUD_JMS_KEY:null'
> 'OBI'::'OBI_KEY:145','OBJ_KEY:11','REQ_KEY:null','ORC_KEY:null','OBI_STATUS:Approved','OBI_DEP_REQUIRED:null','OBI_STAGE_FLAG:2','QUE_KEY:null','USR_KEY:null','OBI_DATA_LEVEL:null','OBI_CREATE:2009-11-8','OBI_CREATEBY:1','OBI_UPDATE:2009-11-8','OBI_UPDATEBY:1','OBI_NOTE:null''OBI_ROWVER:0000000000000000'
> 'OSH'::'OSH_KEY:257','SCH_KEY:241','STA_KEY:4','OSH_ACTION:Engine','OSH_ASSIGN_TYPE:Default task assignment','OSH_ASSIGNED_TO_USR_KEY:1','OSH_ASSIGNED_TO_UGP_KEY:null','OSH_ASSIGNED_BY_USR_KEY:null','OSH_ASSIGN_DATE:2009-11-8','OSH_DATA_LEVEL:null','OSH_CREATE:2009-11-8','OSH_CREATEBY:1','OSH_UPDATE:2009-11-8','OSH_UPDATEBY:1','OSH_NOTE:null''OSH_ROWVER:0000000000000000'
> 'OSI'::'SCH_KEY:241','ORC_KEY:170','MIL_KEY:146','REQ_KEY:null','TLG_KEY:null','RSC_KEY:null','OSI_RECOVERY_FOR:null','OSI_RETRY_FOR:null','OSI_ASSIGNED_TO:null','TOS_KEY:32','PKG_KEY:34','ACT_KEY:1','ORD_KEY:1','ORC_SUPPCODE:00     ','OSI_ASSIGN_TYPE:Default task assignment','OSI_ESCALATE_ON:null','OSI_ASSIGNED_TO_USR_KEY:1','OSI_ASSIGNED_TO_UGP_KEY:null','OSI_RETRY_ON:null','OSI_RETRY_COUNTER:null','OSI_CHILD_TABLE_KEY:null','OSI_CHILD_OLD_VALUE:ecFFyIei7ntqs5tETSu38w==','OSI_ASSIGNED_DATE:2009-11-8','SCH_INT_KEY:null','OSI_LOG_KEY:null','OSI_DATA_LEVEL:null','OSI_CREATE:2009-11-8','OSI_CREATEBY:1','OSI_UPDATE:2009-11-8','OSI_UPDATEBY:1','OSI_NOTE:null''OSI_ROWVER:0000000000000000'
> 'OTI'::'OTI_KEY:190','SCH_KEY:241','SCH_TYPE:null','SCH_STATUS:P','SCH_DATA:null','SCH_PROJ_START:2009-11-8','SCH_PROJ_END:2009-11-8','SCH_ACTUAL_START:2009-11-8','SCH_ACTUAL_END:null','SCH_ACTION:null','SCH_OFFLINED:0','ORC_KEY:170','MIL_KEY:146','OSI_RETRY_FOR:null','OSI_ASSIGNED_TO:null','PKG_KEY:34','REQ_KEY:null','OSI_ASSIGNED_TO_USR_KEY:1','OSI_ASSIGNED_TO_UGP_KEY:null','OSI_ASSIGNED_DATE:2009-11-8','ACT_KEY:1','OSI_ASSIGN_TYPE:Default task assignment','PKG_TYPE:Provisioning','STA_BUCKET:Pending','OBJ_KEY:11','OTI_CREATE:2009-11-8','OTI_UPDATE:2009-11-8','OTI_CREATEBY:1','OTI_UPDATEBY:1','OTI_ROWVER:0000000000000000','OTI_DATA_LEVEL:0''OTI_NOTE:null'
> 'SCH'::'SCH_KEY:241','SCH_TYPE:null','SCH_STATUS:P','SCH_PROJ_START:2009-11-8','SCH_PROJ_END:2009-11-8','SCH_ACTUAL_START:2009-11-8','SCH_ACTUAL_END:null','SCH_DATA:null','SCH_REASON:null','SCH_ACTION:null','SCH_DATA_LEVEL:null','SCH_CREATE:2009-11-8','SCH_CREATEBY:1','SCH_UPDATEBY:1','SCH_UPDATE:2009-11-8','SCH_NOTE:null','SCH_ROWVER:0000000000000000''SCH_OFFLINED:null'

So the tables involved are: OSI, OSH, SCH, OIU, OTI, ORC and OBI.

Using the ORC_KEY and SCH_KEY the relevant rows can be deleted from the above tables in the order listed.  An order is implied as there are contraints on the tables.

select sch_key from OIMUSER.OTI where orc_key=XXX;

So that is be one way to remove the tasks brutally--whether supported or not.

Using the Client API to complete the tasks

Rather than deleting rows one can try to at least complete the tasks manually, making a call to the tcProvisioningOperationsIntf interface (see here for code examples):

tcProvisioningOperationsIntf provIntf =
   (tcProvisioningOperationsIntf) getUtilityFactory().getUtility("Thor.API.Operations.tcProvisioningOperationsIntf");

The tasksArray contains a list of task instance ids.  What are these? The Description field of the open provisioning task in the admin interface contains a number. It turns out that this number is the ORC key. However, in order to call into the Oracle API, as suggested on the OTN forum, we need the task instance id.  This turns out to be the SCH_KEY can be recovered using the query shown above.

To use the API to complete the tasks appropriately actually requires two steps.  The first is to make sure that when the task is completed that the appropriate status mapping takes place.  Do this in the Design Console by going to the Process Definition of the task, for example 'Create User' for your resource.  Then to the 'TaskToObjectStatusMapping' tab.  When a task is completed from the API, the completion code goes to 'MC', for 'Manually Complete'.  So we need to map 'MC' to 'Provisioned' in order to get the task to complete appropriately.  If the task was stuck in the 'System Validation' phase then mapping 'MC' to for example 'Revoked' will cause the provisioning event to be completed, allowing us to launch further provisioning tasks without any issues. 

Of course these manually completed tasks are still visible in the admin interface.  Perhaps the best way to clean them out would be to run the Task Archival maintenance scripts that come with the product as described here.


OIM does not appear to offer any easy way to clean up unwanted open provisioning tasks.  In fact it does not appear to offer any documented way at all to clean them up.  The best one can do in terms of what the product supports is to complete them and possibly then hide these tasks from view or archive them off.  In dev environments and possibly POCs going straight to the tables may be best, but without an official spec of the data model this is always risky.




« April 2014