Informatica Metadata (Repository) Queries

Abstract

Below are queries to retrieve the data from Informatica metadata (Repository Tables) . You need to have access to the Repository schema to access the metadata. Please prefix with your schema name to the tables and view in the below queries.

All Folders in Repository 

SELECT SUBJ_NAME,SUBJ_DESC FROM OPB_SUBJECT ORDER BY 1,2;

All Shared Folders in the Repository

SELECT SUBJ_NAME,SUBJ_DESC FROM OPB_SUBJECT WHERE IS_SHARED <>0 ORDER BY 1,2;

User or Groups having access to the folders in Repository

SELECT subj.subj_name folder_name,

         user_group.NAME user_name,

         DECODE (obj_access.user_type,  1, 'USER',  2, 'GROUP') TYPE,

         CASE

            WHEN ( (obj_access.permissions - (obj_access.user_id + 1)) IN

                     (8, 16))

            THEN

               'READ'

            WHEN ( (obj_access.permissions - (obj_access.user_id + 1)) IN

                     (10, 20))

            THEN

               'READ & EXECUTE'

            WHEN ( (obj_access.permissions - (obj_access.user_id + 1)) IN

                     (12, 24))

            THEN

               'READ & WRITE'

            WHEN ( (obj_access.permissions - (obj_access.user_id + 1)) IN

                     (14, 28))

            THEN

               'READ, WRITE & EXECUTE'

            ELSE

               'NO PERMISSIONS'

         END

            permissions

    FROM opb_object_access obj_access,

         opb_subject subj,

         opb_user_group user_group

   WHERE     obj_access.object_type = 29

         AND obj_access.object_id = subj.subj_id

         AND obj_access.user_id = user_group.ID

         AND obj_access.user_type = user_group.TYPE

ORDER BY 1, 2, 3;