Tuesday Feb 28, 2012

Importing hierarchical entitlement data in OIA - part 3

Just as I explained in my previous blog entry I will discuss importing account and entitlement information from Oracle Health Insurance (OHI) Back Office (BO) into Oracle Identity Analytics (OIA). The OHI BO application is used by healthcare insurers/payers and supports the administrative processing of member data and claims, as well as the product data (including the brands and available distribution channels) and healthcare procurement data required for this type of processing.

More important I will use the <attributeValue> / <attributeValueRef> element pair in the XML data to be imported into OIA so entitlement data can be defined once and referred to many times from the account data. In this particular case this is very useful since OHI Roles are referred to many times from several accounts.

OHI BO's entitlement data consist of OHI application Roles that contain zero or more so-called 'Moduleautorisaties' (Module Authorizations). This means we deal with hierarchical entitlement data. Similar to what I described in my previous blog entry I have used Talend ELT tool to extract the entitlement data (via SQL) from the OHI database and write these to XML files to be imported in OIA.

For more information about this ELT job design please contact me. Anyhow, in the last step I write the output into XML as can be seen from the first lines below (several elements are expanded '-' and other elements are collapsed '+'):

 <?xml version="1.0" encoding="UTF-8"?>
    <namespace namespaceShortName="OHI" namespaceName="Oracle Health Insurance"/>
      +<attributeValue id="Role_BHCC_0001">
      +<attributeValue id="Role_BHPA_0001">
      +<attributeValue id="Role_BHTP_0001">
      +<attributeValue id="Role_DOORSTART">
      +<attributeValue id="Role_DVWZVNTST_ROL">
      +<attributeValue id="Role_GBTP_OVIZA">
      +<attributeValue id="Role_GBTP_TUSSENP1">
      +<attributeValue id="Role_GEBRUIKER_ROL">
      +<attributeValue id="Role_INMAUT001">
      +<attributeValue id="Role_INMAUT002">
      +<attributeValue id="Role_MANAGER_ROL">
      +<attributeValue id="Role_OPENZORG_ROL">
      +<attributeValue id="Role_OPL MERK OPL">
      +<attributeValue id="Role_SCRIPTS">
      +<attributeValue id="Role_XYZ">
      +<account id="AADOULI">
      -<account id="ABRUIJN">
            -<attribute name="Role">
                   <attributeValueRef id="Role_DOORSTART"/>
                   <attributeValueRef id="Role_INMAUT001"/>
                   <attributeValueRef id="Role_MANAGER_ROL"/>
      +<account id="ALAKERVE">
      +<account id="ASIEGERS">
      +<account id="BGLAZEMA">
      +<account id="BLAAK">
      +<account id="BLAGEMAA">

As can be seen from these lines all the OHI application Roles are defined in the first <attributeValues> element section. These Roles are then referred to in the <accounts> element section below that, completely compliant with the schema file accounts.xsd as shipped with OIA.

Finally, all the OHI account and entitlement data is then imported into OIA (just as the accompanying glossary data) and correlated to the global users. An example of how the data can be examined within OIA can be seen in the picture below:

Have fun, René!

Thursday Feb 16, 2012

Importing hierarchical entitlement data in OIA - part 2

In my last blog entry I talked about importing hierarchical entitlement data into Oracle Identity Analytics (OIA). Today I want to discuss another example regarding Microsoft Windows shared files and folders permissions and show how easy these data can be transformed and imported in OIA for either attestation and / or auditing purposes.

All of the data is represented in two input files. One file containing an AD users export and the other one the file and folder permissions:

File #1 containing AD users adusers.csv is as follows:
(metadata: DN|CN|memberOf|sAMAccountName|displayName|sn|givenName):

CN=Rene Klomp,CN=Users,DC=domain,DC=com|Rene Klomp|CN=datagroup,CN=Users, DC=domain,DC=com;CN=homegroup,CN=Users,DC=domain,DC=com|renek|Rene Klomp|Klomp|Rene
CN=John Doe,CN=Users,DC=domain,DC=com|John Doe|CN=datagroup,CN=Users, DC=domain,DC=com;CN=homegroup,CN=Users,DC=domain,DC=com|johnd|John Doe|Doe|John

File #2 containing files and folders permissions shares.txt is as follows:
(metadata: share;group;permission):

home;homegroup;FULL CONTROL
SYSVOL;Authenticated Users;FULL CONTROL
data;datagroup;FULL CONTROL

This time I have used the tool 'Talend Open Studio for Data Integration' to join these two input datasets and transform the data into the right XML format for importing it into OIA. In Talend you design a Job which is made out of several components and a flow related to the data going through these various components. The Job I designed for these particular datasets is rather straightforward and easy to understand as can be seen in the screenshot below (by right-clicking on the image you should be able to examine it in the original size).

Within Talend Open Studio I start with two tFileInputDelimited components, each reading one of the two files. The 1st file adusers.csv has a memberOf attribute which is a multivalued attribute. It can contain a list of groups each separated by a ';'. Therefore the next step after reading this file is normalizing the data for the memberOf column using the tNormalize component. Next thing we need to do is joining both datasets. For this I have used the tMap component.

As you can see it is pretty straightforward to connect the input stream / attributes to the output stream / attributes and do the join based on a simple expression (just as a trivial example - group in input file shares.txt needs to be in memberOf in file adusers.csv).

Now that both sets are joined we can transform the data and write to XML.For that I have used the tAdvancedFileOutputXML component which writes the output in an intermediate XML file (in this case: out.xml). Again, pretty straightforward to define the structure and looping and grouping of elements as you can see in the picture below. The schema is still rather arbitrary but I will use XSLT to transform this into the right schema for OIA in the next step.

For that last step in the ELT transformation process I use the tXSLT component and an appropriate XSL Transformation file (in this case: AD_01_accounts.xsl). It picks up the file that was written in the step before, transforms according to the transformation defined in the XSL file and finally writes the output to our final AD_01_accounts.xml file.

If this whole process ends succesfully there is one more step that I have added. This is using the tXSDValidator component in Talend to check the result against a predefined schema. In this case I obviously use the accounts.xsd schema file as shipped with OIA (in this case version As you can see in the first picture this validation process also ends successfully and ends with outputting '[job AD] File is Valid'.

Now we are ready to import this XML file into OIA - of course we have to configure a namespace for this particular resource first. This whole exercise took me less than 20 minutes to setup and finish!

All the files mentioned above can also be downloaded in this single package: data.zip. If you open the files individually in a browser by clicking on one of the links above, be sure to look at the source or save the file and open in an XML or other editor... otherwise the browser might just show you some blank page or a page with little information.

Have fun, René!

PS. I have formatted the final AD_01_accounts.xml document using XmlPad so it is easier to read than the default output which is not using any formatting at all - this is obviously just a visual thing for this blog and not needed for importing.

Tuesday Jul 12, 2011

Importing hierarchical entitlement data in OIA - a practical example

Oracle Identity Analytics (OIA) provides organizations with the ability to engineer and manage roles and automate critical identity-based controls and processes. In order to do so, we need to feed OIA with the relevant data such as typical HR data, e.g. business structures, users, etc., and account information from all the managed systems in scope, e.g. application user profiles containing common attributes like userid, firstname, lastname, employeeid but more interesting the authorization data or entitlements (which could be application roles, groups, permissions, privileges, responsibilities or whatever it is called within the system).

All of this data is stored within OIA in the so-called Identity Warehouse. This Identity Warehouse is a central repository that contains all of the important entitlement data for the organization (and of course also the other data as mentioned above). This data is imported from the organization's databases on a regular, scheduled basis. The Oracle Identity Analytics software has an import engine that supports complex entitlement feeds. The engine accepts either CSV flat files or XML files, and includes Extract, Transform, and Load (ETL) processing capabilities. Flat files require a schema for the import process. XML files are recommended for accounts with multi-value attributes or n-level hierarchies. Another way of feeding the Identity Warehouse is through an integration with a provisioning solution like Oracle Identity Manager (OIM). Such an integration will give OIA direct access to all of the entitlement data in the systems managed by OIM.

In this blog entry I want to focus on feeding the Identity Warehouse with multi-value attributes or n-level hierarchies through the import engine via XML files. As an example I will use some sample data that is coming from a Peoplesoft Financials system. This system is relatively simple in the sense that its authorizations are modeled 2-levels deep. Users are assigned to Roles and Roles are assigned to Permission Lists. Again, this is just a simple 2-level hierarchical example but could also be applied to any system having n-level (n > 2) hierarchical data.

The data that I want to import lives in 3 tables: a User table, a User - Role relationship table, and a Role - Permission relationship table. The Entity Relationship Diagram is as shown in the adjacent diagram.

Examples of table data for two user entries can be downloaded via the following files: user.csv, userrole.csv, and roleperm.csv.

The data in these tables needs to be joined and transformed to the right XML format. For that I have used an ETL tool called CloverETL. CloverETL is a Java based ETL framework which can be used to transform structured data, and to some degree free-form data. Of course, any other data integration platform like e.g. Oracle Data Integrator can be used for this purpose. Basically, we are reading the data, transforming it, and finally writing it to our final XML file. The graph in the screenshot below shows three so-called UniversalDataReaders (in green), reading each of the input tables. These are joined and written to XML through an XMLWriter component (in blue). This way, the data is joined and written to XML format.

However, the XML data coming out of this XMLWriter is not in the right format that we need to import it in Oracle Identity Analytics. This XML output is having specific tags based on the field names from the input tables, like:

<?xml version="1.0" encoding="UTF-8"?>
<rbacx component="XML_WRITER0" graph="PSFT" created="Mon May 09 01:34:05 CEST 2011">
    <Description>Janssen, J.</Description>

Therefore we use an XSLTransformer component to transform this XML to the more generic format that we need and finally we write this XML data to a file ready for import through a UniversalDataWriter component. This XML format is for the most part using the more specific tags <attributes>, <attribute>, <attributeValues>, <attributeValue>, and <value>.

<?xml version="1.0" encoding="UTF-8"?>
  <namespace namespaceName="Peoplesoft" namespaceShortName="PSFT" />
    <account id="P123456">
      <comments />
      <description>Janssen, J.</description>
        <attribute name="Last_Change">
	...several lines skipped...
        <attribute name="Last_Signon_Dat">
        <attribute name="ROLE">
                <attribute name="PERMISSION_LIST">

The XSL transformation file that I have used to transform the XML can be downloaded via this link: PSFT_01_accounts.xsl.

After defining a Peoplesoft resource type / namespace within Oracle Identity Analytics according to the screenshot shown here, we are now able to import the resulting XML file. After the hierarchical XML data has been successfully imported, the entitlement data can be used in attestation processes, for role mining, defining audit policies, etc. As an example how the account data is represented within OIA, we can take a look at the actual data for a user, e.g. P123456 within the identity warehouse. This results in the following two screenshots. When looking into the general details for this user's Peoplesoft account, we see in the left screenshot all the attributes as defined in this resource type's namespace. When clicking on the attribute named 'Role Name', we can see the list of roles for this account and also that this Role Name attribute also has 'sub attributes'. When double-clicking now one of the roles, we drill-down on its sub attributes. And obviously we find that the Role Name attribute has a sub attribute named 'Permission List'.

 Have fun, René!


This blog covers exciting things I encounter about Oracle's software and related; that is Identity & Access Management, SOA, Security, Desktop, etc. The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.


« July 2016