Our previous blog about creating custom review reports within RSA IMG (formerly Aveksa) focused on leveraging certain views, namelypv_users, pv_reviews, pv_review_component, and pv_unified_entitlment. (Creating a review).
A few things to consider when writing custom reports, or modifying those that are supplied by RSA:
- What are the questions that need answering in this report?
- Are the views documented in the “Public Database Schema Reference” guide sufficient to answer these questions?
- Will I need to further reverse engineer the RSA IMG relational database to get the level of details, or necessary information to build this report?
Let’s examine each aspect:
1) What are the questions that need answering in this report?
RSA IMG (formerly Aveksa) is a platform used to govern “who has access to what”, as well the “type of access” each person in the enterprise via their account (as well as those that are not associated with any one person).
So it is very easy to surmise that reports would help answer those questions. RSA supplies a few reports, but they may not answer your specific question.
For instance, RSA provides the “All User Access” report, which includes collector names in the results. The following image is a screenshot of such a report, based on a lab system used within IDMWORKS.
The point of this report is to get an accounting (including summary information, if “rolled up” to a higher level) of who has which type of access to what application. The other columns in this report include Violations, Shared, and Collector.
Before scheduling this report, however, you should consider if this report answers your questions for all user access in your enterprise.
For instance, what exactly is a violation in this report?
Access Violations within RSA IMG are specifically associated to rules that are configured to run based on configured criteria. When such rules are triggered, after collections of either identity, or any collector (depending on the type), such results that match the configured criteria are treated as violations.
So ask yourself: are such violations what I consider a violation?
If the answer is “no”, guess what? You’ll be creating another version of this report – tuning it to reflect what your enterprise views as violations.
In fact, IDMWORKS has observed many of these types of requests – termination and violations that are not detected within RSA IMG.The following is a screenshot example of a violation for one of our clients. The report did not include the collector column, but other fields or attributes were included, per their specific needs.
2) Are the views documented in the “Public Database Schema Reference” guide sufficient to answer these questions?
Documentation is essential when needing to either implement a feature, or troubleshoot a problem. And thus, RSA supplies a document in which certain views are exposed, and documented for optimal reporting.
This is a good place to start when needing to create a custom report. IDMWORKS, however, has noticed that some of the views that are documented in this reference guide are not sufficient to use in custom report queries. For example, your enterprise’s understanding of a violation reporting may require to also display the date the entitlement was first detected for that user’s account. Using this example, one would surmise the USER_ALL_ACCESS_ACTIVITY view would suffice. After reviewing the documentation and/or testing a custom report using the following SQL syntax…
…you may find that the date the entitlement was first detected or associated to the account is not included in the RSA-supplied view. Because the collector ID and name are included, you may think of simply joining the results with theENTITLEMENT_COLLECTION view. The syntax would be as follows:
select ual.*, 'DIV1' as DIV1, ec.*
from USER_ALL_ACCESS_ACTIVITY ual
inner join ENTITLEMENT_COLLECTION ec on ual.DC_ID = ec.COLLECTOR_ID
Running such a query will result in an empty set, however. Why so? Although the ENTITLEMENT_COLLECTION view contains “Unique entitlement collector identifier, ” as stated in the Public Database Schema Reference guide, it does not actually correspond to the “DC_ID” in the USER_ALL_ACCESS_ACTIVITY view.
Relying on these public views is ideal, in theory, but may necessitate another consideration to truly answer your enterprise’s needs in terms of reports.
3) Will I need to further reverse engineer the RSA IMG relational database to get the level of details, or necessary information to build this report?
RSA IMG leverages a complex relation model, implemented within Oracle relational database management system. You may be familiar with their appliance that includes a pre-loaded/pre-configured Oracle 11g R2 system. For non-appliance, if you have leveraged a remote database, you would have had to set up RSA IMG database within your own Oracle or Oracle RAC environment.
Using tools such as Oracle SQL Developer and connecting with the avuser schema user, you can browse and begin understanding the structure of tables and views. Because RSA does not provide any documentation or deep guidance on the relational models (either in the form of a data dictionary, or an entity-relationship diagram), many customers are left to their own understanding.
Application developers and hackers, at times, employ reverse engineering concepts to “go backwards” from a packaged application to deconstruct its components to either produce source code, or work with (or against) a loophole in the code logic. The same types of techniques are called for to build SQL query syntax better suited for your enterprise’s needs in terms of reporting.
Obviously reverse engineering can take time, as well as certain expertise.
Continuing with the violations report example, IDMWORKS has developed a custom violations report, than can be tailored for any customer’s needs. It contains SQL query syntax that performs complicated joins between collections tables as well as request tables (presuming ARM module is enabled, and used within the RSA IMG instance).
Further extending the public views mentioned in the preceding blog on creating custom review reports within RSA IMG (https://www.idmworks.com/blog/entry/creating-a-custom-aveksa-review-report), IDMWORKS recommends to start withviews, namely first those that begin with “pv_”, then going on to those that begin with “v_”.
Oracle has a built in feature set that performs some level of SQL optimization, which can be leveraged. In addition, PL/SQL can be used within SQL query syntax because Oracle is the only relational database platform currently (and in the foreseeable future) used by RSA for IMG.
It is worth noting that IDMWORKS employs experts of varying backgrounds, including data scientists and database engineers, with particular focus on business intelligence as well as Big Data and other spectrum of data analytics. Chances are if you have a question about a particular IAM system or process, we have an expert in the field to answer it.