✍ Kashif M
Trends

Sunday, 6 September 2015


Query to find list of folder details in Informatica


By on September 06, 2015


The below query used to find list of folder, shared folders information from informatica repository tables.



List folder details

SELECT SUBJ_NAME,SUBJ_DESC FROM OPB_SUBJECT ORDER BY 1,2 


List of shared folders

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

Informatica
Informatica

List of Users and groups having privileges’ on Folders


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
-- and user_group.NAME not in ('Admin','READ_ONLY','Administrator','Administrators')
order by 1,2,3

Kashif
mkashu: Query to find list of folder details in Informatica
Review : Kashif | Kashif
Update: September 06, 2015 | Rating: 4.5

Comment for "Query to find list of folder details in Informatica"

0 comments

Post a Comment