Sunday Mar 13, 2011

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

Wednesday Jan 12, 2011

Soll Ist (Roles vs Actuals) Report in Oracle Identity Analytics

Oracle Identity Analytics (OIA; formerly Sun Role Manager) provides enterprises with the ability to engineer and manage roles and automate critical identity-based controls. Once roles are defined, certified, and assigned, the solution continues to deliver value throughout the user access lifecycle by providing a complete view of access-related data, automating certification, providing evidence of compliance, and enabling streamlined access changes.

During a Proof-of-Concept I have been looking into how to extend OIA (11gR1 BP01) with a so-called 'Soll Ist' or 'Roles vs Actuals' report. Such a report should detail the discrepancies between the entitlements that a user should have based on its roles within the organization compared to the actual entitlements that this user has on the various IT systems and applications. This shows both a practical example of how to add custom reports to OIA in general as well as a starter to adapting your own 'Soll Ist'-like reports.

Getting and Staying in Control

Oracle Identity Analytics (OIA) revolves around the core ‘Identity Warehouse’ within which we build a consolidated view of employee identities across the organization. In order to do this we first build an organizational or business unit structure against which we can begin to map a reporting hierarchy for the attestation process. This can be done in a number of ways but is typically achieved via the organizations HR system or via an existing IAM solution. Upon creating the outline structure of the organization, OIA then defines ‘namespaces’ or attributes within directories and applications that we are interested in reviewing from a compliance and role mining perspective. Once created, we can then import employee identity data from these namespaces showing who currently has access to what ('Actuals').

Having constructed the Identity Warehouse, OIA will provide a single point of reference for employee identities across the business showing a comprehensive breakdown by business unit and individual of all entitlements that exist. Using the OIA web client we can then begin the attestation process to cleanse this information to ensure accuracy and compliance to audit standards. Each ‘Business Unit Manager’ or responsible individual will be issued with an email from OIA asking them to log into the web client to perform the attestation task. This process may run through multiple iterations until the organization deems the identity data to be clean and representative of a correct operational state of the business.

At this stage, OIA is capable of producing reports for management and audit purposes as well as building rules and policies around access rights and entitlement privileges such as Segregation of Duties and associated preventative / detective controls. Dashboard views of compliance activities are also available for high level management and tracking of the process.

Having reached a position at which identity data is clean and operational rules defined, the business can then look to begin the process of role mining and definition. Using a hybrid approach to role mining, OIA provides the organization with the capability to analyze the physical elements of employee roles along with their IT entitlements or permissions to give the most comprehensive view of an ‘Enterprise Role’ possible. OIA generates suggested Roles via the use of mathematical algorithms that can then be presented back to the business and adopted as appropriate.

Having our roles defined, a Roles vs Actuals report could be another helpful tool in staying in control, next to the other already present functionality for automating access governance processes like periodic attestation or certification.

Oracle Identity Analytics Reports

Many out-of-the-box reports can be generated in Oracle Identity Analytics. Reports are valuable tools that auditors and end-user managers can use to evaluate, analyze, and review access controls in the organization.

Reports are broadly classified as follows:

  • Business structure reports: Out-of-the-box reports that run on selected business structures.
  • System reports: Out-of-the-box reports that are run on all users, roles, or policies in Oracle Identity Analytics.
  • Identity Audit reports: Open-audit exception reports based on audit policy scans.
  • Custom reports: Reports customized according to the requirements of your organization.
In order to create our aforementioned Soll Ist report we will need to define a custom report.
Custom Reports

The following steps are involved in creating and running custom reports:

  1. Creating a reports template using JasperReports / iReport. JasperReports is an open source Java reporting tool that can write to screen, to a printer, or to various file formats, including PDF, HTML, Microsoft Excel, RTF, ODT, comma-separated value (CSV), and XML. It reads its instructions from an XML or .jasper file.
  2. Using the Oracle Identity Analytics user interface, upload the reports template to Oracle Identity Analytics.
  3. Running or scheduling the report as needed.

First we need to define the SQL query to give us exactly what we want: the excessive actual entitlements in the various accounts for the user that are not reflected in the roles that are assigned to this person. Basically is what we want a combination of the 2 reports: UserEntitlements.jrxml - giving Actuals or 'Ist' and UserRoleBasedAccess.jrxml - giving Role based access or 'Soll'. For these exceptions we want to display the following fields: namespacename, firstname, lastname, username, rolename, accountname, endpointname, attributevalue, and attributename. The query that gives us the excessive IST entitlement information should look something like this:

SELECT <fields> FROM <IST tables> WHERE <IST condition> AND NOT EXISTS

( SELECT 1 FROM <SOLL tables> WHERE <SOLL condition> )

ORDER BY <order fields>

It would of course be possible to make any variation to this report. In this example report we want to find all the exceptions concerning the 'groups' attribute in Active Directory. Now that we have our SQL query we need to build a template which defines look and feel, what will be printed where, etc.

The heart of the JasperReports interface is iReport the visual report designer specifically designed for JasperReports. iReport gives administrators and report designers total control over the contents as well as the look and feel of every report. As a starting point for this example I have taken the existing UserEntitlements.jrxml template and have modified it for the Soll Ist Report. This means editing the title and SQL query as a bare minimum.

iReport

After we save our report, we can upload it to our OIA server via Reports -> Custom Reports -> New Custom Report.

To test the new Soll Ist report I have imported an example business structure, example set of globalusers and for one user an account on AD (via AD_01_accounts.csv). This user (Bernardine Mooney - bm20245) is working in the Marketing organization and its AD account has a multivalued group membership that shows 7 groups. This is our 'Ist':

At the same time I have defined a Role called Marketing and a Policy called Marketing_AD that is contained inside the Marketing role. The Marketing_AD policy defines only 5 group memberships as a 'Soll' state:

Marketing_AD policy

When we now run the Soll Ist report we should expect to see the 2 excessive groups that are present in the account for this user to show up in the report since they are not reflected in the roles assigned to this user. And this is exactly the output that we get as you can see in the picture below.


If you are interested in the files that I have used (SollIst.jrxml, SQL query, etc.) please give me a ping.

Have fun, Rene!

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