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

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?

Thomas Dodds

Store the user_ID, warehouse_ID in a DB table and model it on the physical join ... then based on the user the join will limit the data coming back.  Based on the users groups in AD you can modify the refresh of the security table(s) to write the combination rows you need.  Doing this in the database will perform better than doing it in the logical layer in the BI server.

1322506

Thank you for your response.  But your answer does not pertain to the question per se.  We do have a table in a database that contains the user and the ids.  What happens is that some users have access to all the ids which are rows in that table.  We do not want to inject all those ids in a where clause.  We also do not want to have those in a separate role since the information can change at any given time.  What we want to do is have a conditional decision when to apply the row level security for a given object.  See the original writeup.

Thomas Dodds

I did see the original write up ... what I'm proposing to eliminate the logical step (in the BI server) AND there's no injecting of thousands of ID into a where clause ... Nor does this invoke any new ad groups - I merely suggest you could use them to be able to keep the security table current.