SQL Query to obtain directly assigned entitlements for a user
Originally Published: 2019-01-28
Last Modified: 2026-05-13
Article Number
000063843
Applies To
RSA Product Set: Identity Governance and Lifecycle
RSA Version/Condition: 7.x
 
Issue
We are looking for a SQL query that returns users' direct entitlements only and number of entitlements per type of an entitlement. 
Resolution
To view direct entitlement of a user, you can query below two public views:
1) PV_USER_DIRECT_ACCESS
2) PV_USERS

Example queries:
(1) To display 'ent' type entitlement and total count for this entitlement of type 'ent', for all users:
select count(*), ENTITLEMENT_TYPE from PV_USER_DIRECT_ACCESS where ENTITLED_ID IN (Select ID from PV_USERS) group by  ENTITLEMENT_TYPE;

(2) To display entitlement name and type of a specific user
select D.ENTITLED_TYPE, D.ENTITLEMENT_NAME, D.ENTITLED_ID as MEU_ID, u.first_name, u.last_name from PV_USER_DIRECT_ACCESS D, PV_USERS U where ENTITLEMENT_TYPE = 'ent' and ENTITLED_ID IN (Select ID from PV_USERS U where USER_ID = 'shiremath');


 
Notes

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. Please check with your RSA Identity Governance and Lifecycle system administrator for the specific password used to access this account.

The URL's to the "Public Database Schema Reference" documents are as follows:


Related Articles

Trending Articles

Don't see what you're looking for?