AnsweredAssumed Answered

Out-of-the-box reports and public schema

Question asked by Kevin Perkins on Nov 8, 2019
Latest reply on Nov 15, 2019 by Sean Miller

Like • Show 1 Like1
Comment • 8

Looking at the out of the box reports, some of them use views that aren't listed in the public database schema reference. For example, when creating a report, if I choose:
Type: Review

Template: Review Delegation History

The FROM clause for the report is auto-populated with V_REVIEW_DELEGATION_HISTORY.

We've gotten numerous warnings from RSA support about staying away from querying things that aren't in the public schema and since last year those warnings have gotten stronger. So I'm confused. On the one hand, it seems like if an out-of-the-box report references some schema object, then it should be fair game for everyone else. On the other hand, the public database schema reference emphasizes that the public schema consists of a specific set of objects visible to the ACMDB user, and this is not one of them.

Who's right? Should I be treating these out-of-the-box report queries as magic or can I add conditions, joins, or whatever else suits my needs?

Sean Miller

Nov 15, 2019 12:14 PM

Correct Answer

Queries should only be written against the views that are prefixed with PV_ (public view). There are other views but they are similar to internal tables where they may change. We encourage everyone to just use the public views as those we do not intend to change. We may add columns to these views but we wont remove columns breaking existing queries.

The default schema for the reports is based on the IGL public views.

See the reply in context

No one else had this question

Outcomes

8 Replies

Kristian Nordman Nov 11, 2019 4:55 AM
Mark Correct
Correct Answer
Hi!

First off, the disclaimer: I am not part of RSA, these are my views and approaches that I have found to be acceptable in my work with customers.

As you said, RSA discourages the use of internal tables in reports and the like with the reason of sudden unadvertised changes. This resolves a question about responsibility. Since RSA has said "don't use them, we cannot guarantee their shape and/or contents in future releases" (paraphrasing) the responsibility of keeping the queries functioning falls on the developer. RSA may still work with you and try to resolve any problems which may arise form the query but that is entirely on a case-to-case basis.

A different aspect of custom queries and reporting is performance. A poorly written query will have drastic effects on the database. Possibly causing performance drops.

On the other hand, some of the data I've been looking for has not been sufficiently detailed in the available public views and I've had to query internal tables and views directly to get to what I need for a specific customer scenario.

In my experience, read what you need from whatever table or view which contains the data, and make sure to test it with each release and patch as the internal tables may change without warning.
2 people found this helpful
Like • Show 0 Likes0
Actions
Jamie Pryer Nov 11, 2019 9:21 AM
Mark Correct
Correct Answer
Hey,
great question and sorry for the confusion, ive never noticed this myself!

The best way to approach this, is to use this SQL query, where you replace "xxx" with a table you need, eg. "delegation"
```
select * from user_views
where lower(view_name) like '%xxx%'
order by view_name asc
```
The advice, as always, is to NOT use the private tables, these tables could change when you upgrade/migrate and then potentially break things in your deployment which is not good.

if you find there is data you need, which you cant find, then please raise an "idea" on this, so we can setup the view for you.
3 people found this helpful
Like • Show 0 Likes0
Actions
- Kevin Perkins @ Jamie Pryer on Nov 13, 2019 3:32 PM
  Mark Correct
  Correct Answer
  Thanks Jamie,
  
  I see you're running that query in the AVUSER schema.
  
  Are all of views in that schema considered part of the "public schema"? If so, any idea why the public database schema reference says that there are "public views" accessed through the ACMDB schema?
  
  Excerpt from the schema reference:
  Chapter 1: About the Public Database Schema
  ...
  The RSA Identity Governance and Lifecycle public views are accessed from a separately configured Oracle user account, ACMDB, installed on the same database instance with the base RSA Identity Governance and Lifecycle database.
  Like • Show 0 Likes0
  Actions
  - Frank Schubert @ Kevin Perkins on Nov 14, 2019 4:39 AM
    Mark Correct
    Correct Answer
    The public schema is made up of all the PV_ views. The AVUSER of course also has access to them. So does the AVDWUSER. that is the one that is used by the reporting engine. As a best practise, I make sure to always prefix the database objects with the schema they reside in, in this case AVUSER, as this is the one owning those views.
    ACMDB is another database user. That is used when an *external* solution wants to access the public views. For example, you might have a business intelligence solution that needs that data and then you connect not via AVUSER or AVDWUSER (the former being a no-no as it has read/write privs anyways) but ACMDB.
    
    Also: I found it handy to create your own user e.g. IGL_AUX. Here you can define your own views, tables and materialized views for whatever reason. You then of course need to make sure to give IGL_AUX the right GRANTs to access the objects it needs.
    If the objects in IGL_AUX need to get accessed in the IGL reports, you in turn need to give AVDWUSER the appropriate GRANTs.
    Adding your own objects in AVUSER is not only not recommended but will cause problems during patches/upgrades.
    2 people found this helpful
    Like • Show 1 Like1
    Actions
    - Kevin Perkins @ Frank Schubert on Nov 14, 2019 4:46 PM
      Mark Correct
      Correct Answer
      Thanks Frank,
      
      That makes a lot of sense, and I like the idea of creating our own user.
      
      I'm left with four conflicting possibilities for what constitutes the public schema. Each item of the following list effectively contains the next item:
      All views in the AVUSER schema (Jamie's answer)
      The contents of the AVDWUSER schema (the default schema for reports in IGL)
      The contents of the ACMDB schema plus any views used by out-of-the-box reports (implied by answer to a case I opened)
      The contents of the ACMDB schema (what the available documentation)
      
      Jamie Pryer: I'd really like to get an answer from on high on this. And I'd like it to be here so that others can benefit from it in the future.
      
      Thanks,
      Kevin
      Like • Show 0 Likes0
      Actions
      - Sean Miller @ Kevin Perkins on Nov 15, 2019 12:14 PM
        Unmark Correct
        Correct Answer
        Queries should only be written against the views that are prefixed with PV_ (public view). There are other views but they are similar to internal tables where they may change. We encourage everyone to just use the public views as those we do not intend to change. We may add columns to these views but we wont remove columns breaking existing queries.
        
        The default schema for the reports is based on the IGL public views.
        1 person found this helpful
        Like • Show 0 Likes0
        Actions
        Kevin Perkins @ Sean Miller on Nov 15, 2019 1:16 PM
        Mark Correct
        Correct Answer
        Thanks Sean,
        
        Maybe you can fill in the final piece of the puzzle: are the out-of-the-box reports "special" then? They make use of V_ views. In the event that those views change, are their definitions somehow updated as part of an upgrade?
        Like • Show 0 Likes0
        Actions
        Sean Miller @ Kevin Perkins on Nov 15, 2019 1:23 PM
        Mark Correct
        Correct Answer
        Fair questions. Ideally even our OOTB reports should just reference PV_ views so they dont break ever. They are special just because RSA maintains them so we know if the view it relies on changes. We should revisit this though and just use public views.
        2 people found this helpful
        Like • Show 1 Like1
        Actions

Out-of-the-box reports and public schema

Outcomes

Related Content

Recommended Content