Clarity

  • 1.  Project View Management Rights - Mitigating Performance issues

    Posted Feb 02, 2016 08:45 PM

    We are currently running v13.2 (SQL Server 2012) and attempting to run a Web Intelligence report against the project object data.  When a report is generated via WebIntelligence, it inserts SQL code to check to see if the user has Project View Management rights.

     

    If the user has global Project - View Management rights, the data returns w/in a few seconds.  However, if these are defined at the OBS level, the report hangs and never returns.

     

    Providing global rights is not desired from a functional standpoint.  Has anyone come across this issue?  and if so, is there an OBS right that I can try to assign to help improve performance?  They already have most of the core PM rights along with View Management rights at the OBS level.

     

    Any and all input is appreciated.  ThankS



  • 2.  Re: Project View Management Rights - Mitigating Performance issues

    Posted Feb 03, 2016 04:08 AM

    Seems odd if just a WEBI report would suffer performance issues with those access rights - if it were a generic performance problem with those rights then I would expect the application to also exhibit the same issues when accessing projects?

     

    That is assuming that your WEBI report is using the same "security logic" that the application uses (abstracted through a view so that only the one 'line' of code would be required in the WEBI query)? If you are using some security logic from a canned WEBI universe then I'm not sure I am saying the right thing here - but if that is the case perhaps you could try taking that out of the report and then implementing the security 'manually' using the view (you'd have to edit the query I think)?



  • 3.  Re: Project View Management Rights - Mitigating Performance issues

    Posted Feb 03, 2016 05:27 AM
      |   view attached

    Thanks for the quick response!!!!!!.  I've attached the culprit code.  I'll check with the BI resource working on this to see if removing the query is feasible from a maintenance standpoint.

     

    Incidentally, I don't think this is confined to the WebE reports.  We currently have a case open with CA as we are experiencing similar performance issues with the project - create from template functionality.

     

    I'm thinking there has to be a group of rights we can assign at the OBS level to mitigate this.  Thanks again for the reply!!!!!

    Attachment(s)

    zip
    WebE Code.txt.zip   451 B 1 version


  • 4.  Re: Project View Management Rights - Mitigating Performance issues

    Posted Feb 03, 2016 06:41 AM

    Ok well I wouldn't really try to write my own logic to decode access rights (like you have in your attached code), that could be your problem?

     

    The view that the application uses to decide if a user has access to a project can be found by examining what SQL code the @WHERE:SECURITY:PROJECT:i.id@ NSQL construct gets converted to. So if you preview a NSQL query with that construct in, it gets 'turned into' the following SQL;

     

    i.id in ( select object_instance_id from odfsec_project_v2 where user_id = ??? )

     

    (??? being the executing user's id)

     

    So you can just use that same odfsec_project_v2 view in your query (just join to it using the appropriate columns) and security is implemented!

     

    --

     

    Other "security constructs" are available in NSQL, for example here's an old thread that talks about following the logic down to underlying tables for resource security ; Adding protlet security NSQL

    (and also if you are just looking for specific access rights rather than "any access to this object")



  • 5.  Re: Project View Management Rights - Mitigating Performance issues

    Posted Feb 03, 2016 07:06 AM

    We didn't write it.  It is auto-generated whenever a WebE report is generated against the project universe.



  • 6.  Re: Project View Management Rights - Mitigating Performance issues

    Posted Feb 03, 2016 08:10 AM

    yeah that was what I was meaning when I said "...If you are using some security logic from a canned WEBI universe..." I'm not sure how one would "fix" that in the Universe apart from building your own objects - to be honest I'm not at all familiar with the stock universes (I seem to recall that the original versions didn't have any security "built-in" even - might be wrong here?) - the only WEBI reports I've really played with have been built on custom universes where I could control/replace the SQL that was being "built".



  • 7.  Re: Project View Management Rights - Mitigating Performance issues

    Posted Feb 05, 2016 07:32 AM

    Thanks to all that have weighed in on this topic.  Looks like the answer we are going with is to remove the SQL making the security check before we deploy the reports to production.

     

    The big drawback to this would be that if there whenever a change is made to the underlying query (i.e. adding / deleting objects from the WEBI report), the code regenerates, so if we don't remember to remove it before production deployment (via the SQL editor in WEBI) the problem resurfaces.

     

    On a related topic I have posted an idea to remove (or reduce) the dependency between Clarity Security and Application performance (link below)

     

    https://communities.ca.com/ideas/235729602

     

    Please review when you get the chance (and spread the word).....If you feel it is worthy of further attention, please vote up.

     

    Thanks!