Forum Stats

  • 3,874,172 Users
  • 2,266,676 Discussions
  • 7,911,751 Comments

Discussions

How Security or Data restriction is applied in Service - CRM Service Requests Real Time subject area

User_PXXMZ
User_PXXMZ Member Posts: 1 Green Ribbon

Hi,

I am converting an OTBI analysis (subject area - Service - CRM Service Requests Real Time) to BIP report and facing issues with security.

I ran the OTBI analysis with two different login id's and logical sql's are generated with different security.

1).Logged in as Manager who should see global data and below security sql is generated.

OR ( ( SR.STRIPE_CD = 'ORA_SVC_HCM' ) ) 

OR ( SR.STRIPE_CD = 'ORA_SVC_HCM' 

     AND ( SR.QUEUE_ID IN 

   ( select /*+QB_Name(qb_queues)*/ queue_id from  

         (SELECT r.QUEUE_ID FROM

      

       (SELECT resource_id FROM FUSION.JTF_RS_REP_MANAGERS mgrrep WHERE trunc(SYSDATE) between mgrrep.start_date_active AND mgrrep.end_date_activ  AND mgrrep.parent_resource_id = (SELECT FUSION.HZ_SESSION_UTIL.GET_USER_PARTYID FROM dual)) myres,

          (SELECT object_id, queue_id FROM FUSION.SVC_QUEUE_RESOURCES WHERE OBJECT_TYPE_CD = 'ORA_SVC_RESOURCE_TEAM') r,

          (SELECT team_resource_id, team_id FROM FUSION.JTF_RS_TEAM_MEMBERS WHERE RESOURCE_TYPE = 'GROUP' AND DELETE_FLAG = 'N') tm,

          (SELECT group_id,resource_id FROM FUSION.JTF_RS_GROUP_MEMBERS WHERE DELETE_FLAG = 'N') g

          AND tm.TEAM_ID = r.OBJECT_ID

          WHERE g.GROUP_ID = tm.TEAM_RESOURCE_ID

          AND g.resource_id = myres.resource_id

          UNION ALL

          SELECT r.QUEUE_ID FROM

           (SELECT resource_id FROM FUSION.JTF_RS_REP_MANAGERS mgrrep WHERE (trunc(SYSDATE) between mgrrep.start_date_active AND mgrrep.end_date_active) AND mgrrep.parent_resource_id = (SELECT FUSION.HZ_SESSION_UTIL.GET_USER_PARTYID FROM dual)) myres,

           FUSION.SVC_QUEUE_RESOURCES r, FUSION.JTF_RS_TEAM_MEMBERS tm

           WHERE r.OBJECT_TYPE_CD = 'ORA_SVC_RESOURCE_TEAM'

           AND tm.RESOURCE_TYPE  = 'INDIVIDUAL'

           AND tm.TEAM_ID     = r.OBJECT_ID

           AND tm.DELETE_FLAG   = 'N'

           AND tm.TEAM_RESOURCE_ID = myres.resource_id

          UNION ALL

          SELECT r.QUEUE_ID FROM

           (SELECT resource_id FROM FUSION.JTF_RS_REP_MANAGERS mgrrep WHERE (trunc(SYSDATE) between mgrrep.start_date_active AND mgrrep.end_date_active) AND mgrrep.parent_resource_id = (SELECT FUSION.HZ_SESSION_UTIL.GET_USER_PARTYID FROM dual)) myres,

           FUSION.SVC_QUEUE_RESOURCES r

           WHERE r.OBJECT_TYPE_CD = 'ORA_SVC_RESOURCE'

           AND r.object_id    = myres.resource_id) 

    )  

  )

)


2.Logged in as different manager and want see his market queue's data only. This time first OR condition alone is not coming an d remaining sql is same.

How OTBI subject area - Service - CRM Service Requests Real Time is identifying and restricting the data ? Based on any roles ?.

Any help here would be appreciated.

Thank You,

Kishore.

Answers