Clarity

  • 1.  How to find instance rights of group.

    Posted Apr 10, 2015 07:00 AM

    Hi Team,

     

    I want to find the instances on which instance rights are given in the group.

    Ex. Suppose there is a group "A" having "Portlet - View" rights on "Unfilled Requirement portlet". so I would like to find on which portlets the instance level right is granted in this group.

    like this if there are other instance rights like "Page - View", "Process - Start" in group "A". That I would like to find.

     

    From below query i can find the names of rights like "Portlet - View", "Page - View", "Process - Start" but not on which instances these rights are given. Can anyone help me?

     

    SELECT DISTINCT ri.group_name Access_Group,

    G.GROUP_NAME Access_Right,

    'Instance' OBS_Name,

    DECODE(g.IS_ACTIVE,1,'ACTIVE',0,'IN ACTIVE') STATUS,

    g.is_active act_flag,

    ri.group_code,

    ri.id ri_id

    --null obsid

    --3 fltr_type

    FROM CMN_SEC_ASSGND_OBJ_PERM A,

    CMN_SEC_GROUPS_V G,

    ODF_OBJECTS_V O,

    CMN_SEC_USER_GROUPS UG,

    CMN_SEC_GROUPS_V RI,

    WHERE A.PRINCIPAL_ID =UG.GROUP_ID

    AND A.PRINCIPAL_TYPE = 'GROUP'

    AND A.RIGHT_ID = G.ID

    AND G.RIGHT_TYPE = O.RIGHT_CODE

    AND RI.ID = UG.GROUP_ID

    AND a.object_instance_id=cap.pk_id

    AND G.LANGUAGE_CODE = 'en'

    AND O.LANGUAGE_CODE = 'en'

    AND RI.language_code ='en'

    and ri.group_name like 'A'



  • 2.  Re: How to find instance rights of group.

    Posted Apr 10, 2015 08:45 AM

    Not surprisingly, similar discussions have come up often before, check out these threads (or Search for others)

     

    Re: Table name for Instance rights

    Instance right query



  • 3.  Re: How to find instance rights of group.
    Best Answer

    Posted May 09, 2019 01:51 PM

    Hi,

     

    The only way I found after a lot of digging, is to take an inner join on individual tables referred to in the 'right_type' from CMN_SEC_GROUPS_V using object_instance_id of the records you already arrived at. 

     

    SELECT DISTINCT rv.group_name,
    gv.group_name Rights_Name, gv.description, gh.object_instance_id, cp.portlet_code CODE
    FROM CMN_SEC_ASSGND_OBJ_PERM gh
    INNER JOIN cmn_sec_groups_v gv ON gh.right_id=gv.id
    INNER JOIN cmn_sec_groups_v rv ON gh.principal_id = rv.id
    INNER JOIN cmn_portlets cp ON gh.object_instance_id=cp.id
    WHERE gh.principal_id  = //your group//
    AND gv.right_type LIKE 'CMN_PORTLETS'
    AND rv.language_code='en' AND gv.language_code='en' AND gv.is_active=1

     

    UNION ALL

     

    SELECT DISTINCT rv.group_name,
    gv.group_name Rights_Name, gv.description, gh.object_instance_id, cp.page_code CODE
    FROM CMN_SEC_ASSGND_OBJ_PERM gh
    INNER JOIN cmn_sec_groups_v gv ON gh.right_id=gv.id
    INNER JOIN cmn_sec_groups_v rv ON gh.principal_id = rv.id
    INNER JOIN cmn_pages cp ON gh.object_instance_id=cp.id
    WHERE gh.principal_id = //your group//
    AND gv.right_type LIKE 'CMN_PAGES'
    AND rv.language_code='en' AND gv.language_code='en' AND gv.is_active=1

     

    UNION ALL

     

    ............carry on for the other tables.

     

    Though this thread is very old, I am sure someone might be looking for it as I was  

    Hope this helps.