Tech Monger

Programming, Web Development and Computer Science.

Skip to main content| Skip to information by topic

Informatica User - Object Access Map

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';

Using the same table OPB_OBJECT_ACCESS you can get details of ownership of more granular objects like source, target, lookup, transformations etc.

Tagged Under : Informatica Informatica Queries