Tech Monger

Informatica stores details of users who have access over objects like workflow, mapping, session, connection etc. These details are stored inside repository table OPB_OBJECT_ACCESS. By querying this table you can fetch mapping between users and objects on which those users have access. Below are the details of the table.

OPB_OBJECT_ACCESS

OBJECT_ID

OBJECT_TYPE

OBJECT_SUBTYPE

USER_ID

USER_TYPE

PERMISSIONS

To retrieve detailed relationship details between users and corresponding objects on which user has access you should be familiar with following commonly used object tables and views.

• User Table : REP_USERS and OPB_USERS
• Folders : REP_SUBJECT and OPB_SUBJECT
• Workflows : REP_WORKFLOWS and OPB_WORKFLOW
• Mappings : OPB_MAPPING
• Connections : OPB_CNX
• Sessions : OPB_SESSION and OPB_TASK

Informatica User Access Queries

List of workflows owned by specific user

SELECT w.SUBJECT_AREA, w.WORKFLOW_NAME FROM REP_WORKFLOWS w, 
REP_USERS u, OPB_OBJECT_ACCESS a WHERE w.WORKFLOW_ID=a.OBJECT_ID and
u.USER_ID=a.USER_ID and u.USER_NAME='username';

List of mappings owned by specific user

SELECT m.MAPPING_NAME FROM OPB_MAPPING m,
REP_USERS u, OPB_OBJECT_ACCESS a WHERE m.MAPPING_ID=a.OBJECT_ID and
u.USER_ID=a.USER_ID and u.USER_NAME='username';

List of users who have access over specific connection

SELECT u.USER_NAME FROM REP_USERS u, OPB_CNX c, OPB_OBJECT_ACCESS a
WHERE c.OBJECT_ID=a.OBJECT_ID and U.USER_ID=A.USER_ID and
c.OBJECT_NAME='connection-name';

List of sessions owned by specific user

SELECT t.TASK_NAME FROM OPB_SESSION s, OPB_TASK t,
REP_USERS u, OPB_OBJECT_ACCESS a WHERE s.SESSION_ID=a.OBJECT_ID and
u.USER_ID=a.USER_ID and u.USER_NAME='username';