Entitlements outside Roles Report in Oracle Identity Analytics

As a followup to my previous blog entry on reporting in Oracle Identity Analytics (OIA) I have looked at another probably very common OIA report. This report will list all the entitlements (imported in the Identity Warehouse) that are outside of (not contained in) any role. This report can be very useful during the role mining process to see what entitlements are not contained in any role.

I have setup the SQL query so that it will look at all attributes (entitlements, e.g. 'groups' in AD) that are set as 'minable' in the resource type configuration. Basically the SQL query will find for all of these attributes all of the values (SELECT ... FROM ... WHERE ...) and see if these are contained in any role through the relation role->policy->attribute (... AND NOT EXISTS (SELECT ... FROM ... WHERE...)). The final SQL looks as follows:

 SELECT
     ns1.namespacename   AS NAMESPACENAME,
     att1.name           AS ATTRIBUTENAME,
     av1.attribute_value AS ATTRIBUTEVALUE
 FROM
     attributes att1,
     attributecategories ac1,
     attribute_values av1,
     namespaces ns1
 WHERE
     ns1.namespacekey = ac1.namespacekey
 AND ac1.attributecategorykey = att1.attributecategorykey
 AND av1.attribute_id = att1.attributekey
 AND att1.isminable = '1'
 AND NOT EXISTS
     (
         SELECT
             1
         FROM
             roles rs,
             role_policies rp,
             role_versions rv,
             policies ps,
             policy_versions pv,
             policy_attributes pa,
             policy_attr_hier_nodes pahn,
             policy_attr_hier_nodes pahn2,
             attribute_values av
         WHERE
             rs.statuskey = 1
         AND rv.version_status_id = 1
         AND rs.rolekey = rp.rolekey
         AND rp.role_version_id = rv.id
         AND rp.policykey = ps.policykey
         AND ps.policykey = pa.policy_id
         AND ps.current_version_id = pa.policy_version_id
         AND pa.policy_attr_hier_id = pahn.id
         AND pahn.id= pahn2.root_id
         AND pahn2.attribute_value_id = av.id
         AND av.id = av1.id
     )
 ORDER BY
     NAMESPACENAME,
     ATTRIBUTENAME,
     ATTRIBUTEVALUE

I have taken this SQL as the basis for my iReport design for the report that I have called 'User Entitlements outside Roles'. It will display an ordered list (grouped by namespace) of all attributes that are set as minable and its values (entitlements), not contained in any role. The resulting jrxml file can be found here: UserEntitlementsOutsideRoles.jrxml.


An example of what the final report will look like is shown below. In this example I have ran it against a sample dataset where for two of the resources (Microsoft SQL Server, Windows Active Directory) some attributes have been set as minable (e.g. 'serverRoles', 'groups', etc.). As said before they have been set as minable here for the sake of reporting but these are obviously typically the same attributes taking part in a role mining process and hence more or less automatically the ones we are interested in...

Have fun, René...

Comments:

Post a Comment:
  • HTML Syntax: NOT allowed
About

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.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today