Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 16 Oracle Analytics Lounge
- 215 Oracle Analytics News
- 43 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 79 Oracle Analytics Trainings
- 15 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
Rowlevel security: Can you build a case statement that only invokes a "where clause" injection based
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?
Answers
-
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.
0 -
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.
0 -
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.
0