Reporting and Analytics for ERP

Get Involved. Join the Conversation.

Topic

    Praveen Kumar
    Oracle Fusion BI Publisher Custom Physical SQL Security
    Topic posted September 16, 2019 by Praveen Kumar, tagged BI Publisher, Fusion Financial reporting, Fusion Procurement reporting, Reports 
    71 Views, 3 Comments
    Title:
    Oracle Fusion BI Publisher Custom Physical SQL Security
    Summary:
    Oracle Fusion BI Publisher Custom SQL - Security Considerations
    Content:

    Hi,

     

    When you create a Business Intelligence Publisher data model with physical SQL, you have two options.

    You can:

    • Select data directly from a database table, in which case the data you return isn't subject to data-security restrictions. Because you can create data models on unsecured data, you're recommended to minimize the number of users who can create data models.

    • Join to a secured list view in your select statements. The data returned is determined by the security profiles that are assigned to the roles of the user who's running the report.

     

    The above listed statement is only applicable from an HCM BI Publisher reporting perspective and not sure how this custom SQL security will be handled in SCM & Finance modules?

    It appears data returned for custom SQL BI Publisher report is determined by the security profiles that are assigned to the roles of the user who's running the report in HCM area and how does this work in SCM & Finance modules?

    Summary:

    If we decide to cater some of the reporting requirements via BIP Data model - using a custom physical SQL approach, then how the security is handled in case of a BIP Custom SQL that has tens of tables information across all three modules (HCM/SCM & Fin) modules.

    Please help me with your valuable inputs.

    Thanks

    Praveen

    Version:
    Oracle Business Intelligence 11.1.1.9.0; Oracle Cloud Application 19C (11.13.19.07.0)

    Comment

     

    • Prateek Parasar

      Our developers have created some BIP in receivables and Purchasing recently with security tabled joined to fetch the BU related data user has access to. Seems like a good approach to me but yes we don’t expose creation of model to functional users or end users.

      • Marybeth Snodgrass

        Would you be willing to post xml of the data model you mentioned above?

        • Prateek Parasar

          I will get the actual query out but you can basically pass the logged in userid to the security table to find which bu/inv org (context) user has access in the specific area(role) and only return the data for that.

          you can pass user info through the BIP variable

          if you try running this query in a model you will know this approach will work.

          Select :xdo_user_name from dual