3 Replies Latest reply on Mar 9, 2016 1:06 PM by Thomas Dodds

    Rowlevel security: Can you build a case statement that only invokes a "where clause" injection based a session variable?

    1322506

      Version is 11.1.1.1.9.0

       

      We have a table that indicates what data a user can access by unique id's.  Some users have what is called unrestricted access with several thousand ids.

      What we are wanting to do is to ONLYy invoke the Identify Manager/Application Role Data Filters when a user is restricted.  Otherwise don't apply any row level security.

       

      One apporach was to try to use 2 initialization blocks to do that.  One to indicate if the user is restricted or not (Y, N).  The other to generate a list of ids to use as row level security.  All this works.  What does not work is using a case statement together with both.Not sure if this is possible or we are not using the right approach.  We use MS active directory authentication as a side note.

      So, for example.  We tried various version of this logic.  With the thought being that only invoke any row level security if they are restricted.

        CASE WHEN  VALUEOF(NQ_SESSION.XYZ) = 'Y' THEN "A"."Dim ABC"."ABC ID" = VALUEOF(NQ_SESSION.CSADV) ELSE END

       

      Another approach was to limit the return of the initialization block to only users if they were restricted.  What happened then was that when there were no records returned, OBI throws an error since it is looking for values for a where clause.  If we could overcome this.  That would work as well.

       

      How can this be accomplished in other ways?