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.
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 :
- Folders :
- Workflows :
- Mappings :
- Connections :
- Sessions :
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';
Using the same table OPB_OBJECT_ACCESS you can get details of ownership of more granular objects like source, target, lookup, transformations etc.