How to query a public database schema table for Segregation of Duties (SOD) violations in RSA Identity Governance & Lifecycle
3 years ago
Originally Published: 2018-09-18
Article Number
000063932
Applies To
RSA Product Set: RSA Identity Governance & Lifecycle
RSA Version/Condition: 7.0.2 and 7.1.0



 
Issue
You have created an Segregation of Duties (SOD) rule that detects violations when you add, change and/or remove a user's access. You would like to know what public database schema database view contains this information.
Resolution
SOD violations may be found in database view CHANGE_REQUEST, column VIOLATIONS.
   
This type of information is in the RSA Identity Governance & Lifecycle Public Database Schema Reference documentation.  See page 125 of the RSA Identity Governance and Lifecycle 7.0.2 Public Database Schema Reference and page 121 of the RSA Identity Governance & Lifecycle 7.1 Public Database Schema Reference guide.
 
You may access this database view as either

     AVDWUSER.CHANGE_REQUEST
     or
     AVUSER.PV_CHANGE_REQUEST.
 
Please note that the VIOLATIONS column is of type XMLTYPE. Here are two methods for viewing the data in that column.
 

Example

You have the following SOD rule and request both entitlements be added to the same user. This generates two violations:
 
User-added image

I.  From SQL Developer

  1. Select the data:
SELECT VIOLATIONS FROM AVDWUSER.CHANGE_REQUEST;
  1. Edit the column to see the contents:
User-added image
 
User-added image

II.  From SQL Plus

  1. Use the following commands to generate the output shown below:
SQL> set pagesize 0 echo off;
SQL> set linesize 30000 long 30000 longchunksize 30000 Trimspool on;
SQL> SELECT VIOLATIONS FROM AVDWUSER.CHANGE_REQUEST:;
<?xml version="1.0" encoding="US-ASCII"?>
<simple-record xmins="https://www.aveksa.com/schemas/policy">
  <record rule-id="1" rule-name="SOD" rule-desc="" entitled-id="35085" user-ent-id="" first-name="John" last-name="Smith" user-disp-name="Smith, John" ent-id="47" 
ent-type="ent" ent-name="Web Services, View" res-name="Web Services" act-name="View" violating-ent-type="ent" violating-ent-id="47" violating-ent-name="" 
violating-res-name="" violating-act-name="" bucket-id="1" app-id="1" app-name="Aveksa" state="OP"/>
  <record rule-id="1" rule-name="SOD" rule-desc="" entitled-id="35085" user-ent-id="" first-name="John" last-name="Smith" user-disp-name="Smith, John" ent-id="47"
ent-type="ent" ent-name="Web Services, View" res-name="Web Services" act-name="View" violating-ent-type="ent" violating-ent-id="47" violating-ent-name=""
violating-res-name="" violating-act-name="" bucket-id="2" app-id="1" app-name="Aveksa" state="OP"/>
</simple-record>
 
 

Related Articles

Trending Articles

Don't see what you're looking for?