Importing hierarchical entitlement data in OIA - a practical example
By user12582982 on Jul 12, 2011
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.
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"> <User> <User_ID>P123456</User_ID> <Description>Janssen, J.</Description> <Last_Change>4/7/2008</Last_Change> <Locked_Out>0</Locked_Out> <Failed_Logins>0</Failed_Logins> <Enabled>0</Enabled> <Last_Signon_Dat>2008-04-07-15.28.50.000000</Last_Signon_Dat> <Role> <Role_Name>K_GL_INQUIRER</Role_Name> <Perm> <Permission_List>K_GL_INQUIRY</Permission_List> </Perm> <Perm> <Permission_List>K_REPORT_DEC</Permission_List> </Perm> </Role> ...
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"?> <rbacx> <namespace namespaceName="Peoplesoft" namespaceShortName="PSFT" /> <accounts> <account id="P123456"> <name><![CDATA[P123456]]></name> <endPoint>PSFT</endPoint> <domain>Production</domain> <comments /> <description>Janssen, J.</description> <attributes> <attribute name="Last_Change"> <attributeValues> <attributeValue> <value>4/7/2008</value> </attributeValue> </attributeValues> </attribute> ...several lines skipped... <attribute name="Last_Signon_Dat"> <attributeValues> <attributeValue> <value>2008-04-07-15.28.50.000000</value> </attributeValue> </attributeValues> </attribute> <attribute name="ROLE"> <attributeValues> <attributeValue> <value>K_GL_INQUIRER</value> <attributes> <attribute name="PERMISSION_LIST"> <attributeValues> <attributeValue> <value>K_GL_INQUIRY</value> </attributeValue> <attributeValue> <value>K_REPORT_DEC</value> </attributeValue> </attributeValues> </attribute> </attributes> </attributeValue> ...
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é!