000036745 - How to query a public database schema table for Segregation of Duties (SOD) violations in RSA Identity Governance & Lifecycle

Document created by RSA Customer Support Employee on Oct 9, 2018
Version 1Show Document
  • Comment • 0
  • View in full screen mode

Article Content

Article Number000036745
Applies ToRSA Product Set: RSA Identity Governance & Lifecycle
RSA Version/Condition: 7.0.2 and 7.1.0



 
IssueYou 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.
ResolutionSOD 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>

 
 

Attachments

    Outcomes