Here is the sample query to retrieve user - role membership in SailPoinr IdentityIQ:
Select idy.name as "Username", (select idy2.name from spt_identity idy2 where idy.manager=idy2.id) AS "Manager", idy.extended1 AS "UserType", bun.name AS "Role Name", bun.disabled AS "Status"
from spt_identity idy, spt_identity_bundles idb, spt_bundle bun
where idy.id = idb.identity_id
and bun.id=idb.bundle Order by idy.name;
from spt_identity idy, spt_identity_bundles idb, spt_bundle bun
where idy.id = idb.identity_id
and bun.id=idb.bundle Order by idy.name;
Tables Used:
- User Data: SPT_IDENTITY
- Role Table: SPT_BUNDLE
- User-Role Membership: SPT_IDENTITY_BUNDLES
Note: In SailPoint IdentityIQ, Roles are also known as Bundles.
