Reporting and Analytics for ERP

Get Involved. Join the Conversation.

Topic

    Ayesha Thilakarathna
    Tables or views to pick values from manage data access for...
    Topic posted June 11, 2018 by Ayesha ThilakarathnaBronze Crown: 15,000+ Points, tagged BI Publisher, Fusion Financial reporting, OBI Answers, OBIEE Answers, OTBI, Reports 
    1259 Views, 8 Comments
    Title:
    Tables or views to pick values from manage data access for users function
    Content:

    Hello,

    Is there anyone know any table or view to get values from manage data access for users function?

    Purpose is to create a custom report and we need to find out the table to get security context and security context value of the users.

    We need to any table which shows following highlighted areas as per the attachment. 

     

    Image:

    Comment

     

    • Wendy Ware

      I would be interested in learning the answer to this, too.

      In the meantime, I have been getting what I need by exporting the Manage Data Access search results by role and combining these results.

      (see attached screenshot)

    • Siva Manickam

      Hi

      You can try the following tables/views:

      FUN_USER_ROLE_DATA_ASGNMNTS
      GL_ACCESS_SETS
      PER_USER_ROLES
      PER_USER_ROLES_DN_VL
      PER_USERS

      Siva M

    • Hasan Azmi

      Hi,

        You can use the below query ,

       

      SELECT Users.username ,asg.role_name,
      (SELECT Bu.BU_NAME 
      FROM fusion.FUN_ALL_BUSINESS_UNITS_V Bu 
      WHERE Bu.bu_id = Asg.org_id 
      AND SecMap.OBJECT_NAME = 'FUN_ALL_BUSINESS_UNITS_V' 
      AND ROWNUM =1 
      ) AS BU_NAME, 
      (SELECT PK1_COLUMN_NAME 
      FROM fusion.FND_OBJECTS Obj 
      WHERE obj.obj_name = secmap.object_name 
      ) AS SECURITY_CONTEXT
       
       
      FROM FUN_USER_ROLE_DATA_ASGNMNTS asg,
      fusion.PER_USERS Users,
      fusion.FUN_ROLE_DATA_SECURITY_MAPPING SecMap
      where 1=1
          AND Users.USER_GUID = Asg.USER_GUID 
      AND Asg.ROLE_NAME = SecMap.ROLE_NAME 
      order by asg.creation_date desc,asg.last_update_date desc
       
    • Milton Munoz

      As a follow up, how could we get the "modified by" and "modified date" for this?

      • MANISH S

        Hi Milton,

        I hope the modified by and date values couldn't be obtained on the data access as we can assign or revoke data access for a user without specifying any end dates. As per my observations, 'modified by' or 'modified date' can be obtained for records that have start and end dates.

        Regards,
        Manish

    • Siva Kumar

      Hello

      Please refer to the below document .

      SQL To Get Full List Of Security Context types, values and users associated to it - Manage Data Access for users (Doc ID 2460399.1)

      Regards

      Siva