How to query a public database schema table for Segregation of Duties (SOD) violations in RSA Identity Governance & Lifecycle
Originally Published: 2018-09-18
Last Modified: 2026-05-13
Article Number
Applies To
RSA Version/Condition: 7.0.2 and 7.1.0
Issue
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:
I. From SQL Developer
- Select the data:
SELECT VIOLATIONS FROM AVDWUSER.CHANGE_REQUEST;
- Edit the column to see the contents:
II. From SQL Plus
- 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
ORA-20126 in Review Definition User Selection query with custom table in the AVCSUSER schema in RSA Governance & Lifecycle 100Number of Views RSA Via Lifecycle and Governance 7.0 Global Roles do not show under matching entitlements when entitlement rule is set to … 21Number of Views 'ORA-00942: Table or View does not exist' error when referencing a custom table/view in a report query in RSA Identity Gov… 246Number of Views Overview of Public Database Schema Views in RSA Identity Governance & Lifecycle 189Number of Views RSA Identity Governance and Lifecycle 7.1.1.1 Release Notes 27Number of Views
Trending Articles
RSA Authentication Manager 8.9 Patches and Hotfixes Readme RSA MFA Agent 2.5 for Microsoft Windows Installation and Administration Guide RSA Authentication Manager Patch Updates RSA Authentication Manager Upgrade Process RSA-2026-24: RSA Authentication Manager Security Update for Third-Party Component Vulnerabilities
Don't see what you're looking for?