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: 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.


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.


« February 2012