I am developing a data warehouse for eventual deployment over an extranet. I have been considering how I can limit what various parties can see. Each user will pay a subscription for part of the data.
One idea that I am toying with is to refresh on first use the LOV’s so that the user only sees the Dimension’s values that they have paid for. This way they can only pick what they can see. I have thought through most of the things that need to be done to support this idea, e.g. setting the LOV’s to refresh on first use, export to repository, disabled them as conditions, constraining all prompts etc. but the one issue I don’t fully have an answer for is this.
LOV’s are stored in files on the server, right? Well what happens if these are continually being refreshed to reflect specific users choices, could a deadlock arise? Could a user see another users values? Are there any other issues to consider?
Yes, the LOVs are on the server for Webi users. Each object has one LOV, how are you going to ‘tailor’ each user’s LOV? I think that you may want to consider using the security feature of Row Level Security or add a table to your universe of users and security levels that each query can join to and thereby restricting the data that they will have access to.
No I don’t want to use Row Level Security, I want a method which has no impact on the core sql generated. That is to say I do not want additional security tables attached to the dimension or even the Fact tables.
I will customise the LOV’s by reference to the security tables, thus rendering invisible values that users aren’t entitled to see. As I said before I will be
Users won’t be able to modify sql either so I think I’ve got all angles covered. I was only wondering what would happen if two users simultaneously caused a LOV file to be modified. Having reflected on this for a few hours I think the risk is minimal and acceptable
It sounds like you have what you need but I am stiil curious about your technique. Are you going to add a condition to each LOV SQL that will give each user a tailored LOV based on what they are allowed to see?
My technique which I am going to call “What They See Is What They Get” is only practical because many of the LOV’s that I want to alter will be derived from 3 tables.
[list]A Time Dimension Hierarchy, customers will buy a subsription for a period of time
A Location Dimension Hierarchy, customers will subscribe for information on a specific physical area, all sub divisions of this area are held in additional columns of the Location Hierarchy table
A Supplier Dimension, customers will pay to see information on certain suppliers[/list]
By having a Control table against these dimensions which has a stub join to @Variable(‘BOUSER’) I can alter the content of the LOV’s according to each user. Once this is done, and remember the prompts are all constained, the user can’t pick what he hasn’t paid to see.
To me this has the advantage that retrieval from the FACT table is not slowed because of constant checking against a joined security table.
(BOB member since 2002-09-05)