Dear aBI: Skirting Security in a Dashboard

[Former Member]

Another installment in the "Dear aBI" series of posts where some BusinessObjects Community Experts take a question from a Community Member, take a stab at answering, and open it up for discussion.  I'm considered an expert because I'm an SAP Mentor, an ASUG Ambassador, a Reportapolooza participant, and I was a co-valedictorian at my High School.  This entry's question comes from EyeCandy from Chicago. Or Chicago from EyeCandy (sometimes I don't pay attention to what columns things are in).

I'm trying to put together an Xcelsius model from data that I need to generate with a Live Office connection from a Webi report that I need to schedule.  I have to limit the regions that someone can see in the report based on individual security levels.  I have a relatively small amount of data points, and I'd rather not burst the report or the .swf files. What can we do?

Let me first say that I am not a developer; I am a tool user. That means that figuring out ways to make stuff work without having to change code or configurations or do anything that I can't do with the software I've already got. So this little challenge is right up my alley.

PLEASE NOTE! JUST BECAUSE I'M ABOUT TO EXPLAIN HOW YOU COULD DO POTENTIALLY GO AROUND SECURITY IN A SIMPLE, EASY, AND SORT OF AWESOME WAY DOES NOT MEAN YOU SHOULD NECESSARILY DO IT! PLEASE DO YOUR DUE DILLIGENCE TO MAKE SURE YOU WON'T BE EXPOSING PEOPLE'S SOCIAL SECURITY NUMBERS ON THE NET OR GETTING YOURSELF FIRED!

Sorry for yelling, but that was sort of important. So, how will I skirt security? First, let me set up a scenario for you.

Let's say that I have 10 stores, and 4 vice presidents that should each see from 1 to 10 of them. My list looks like this, and I'm going to hardcode it in my Xcelsius.

VP	Store
Todd	Joplin
Todd	Kirksville
Todd	Bourbon
Todd	Blue Springs
Todd	Pierce City
Todd	Shell Knob
Todd	Macon
Pete	Macon
Pete	Union
Pete	Kirksville
Steve-a-reen-o	Columbia
Steve-a-reen-o	Jefferson City
"The Daver"	Shell Knob
"The Daver"	Pierce City
"The Daver"	Joplin
"The Daver"	Bourbon

Separately, I'll create a report that returns a row (or a couple of rows) for each Store and schedule it to run. I'll create a Live Office connection to bring all of that data into my Xcelsius spreadsheet. That's right: I'm bringing it all in, even though not everyone can should be able to see everything. I'll want this to run after the components are loaded (by adding a hidden connection refresh button that runs after components are loaded).

Next I'm going to create a Universe Object called "Username" with the definition @variable('BOUSER') (and make sure to attach it to the dual table if I'm running Oracle) and call it in a Live Office query and throw it into a cell in my Xcelsius spreadsheet. That's right: all I want out of that connection is a username. And I'll want to make sure it runs before the components are loaded. 

Now that those two pieces are in place the magic can happen.

First, I add a combobox selector that will effectively move just the store names I want each user to see to a different spot based on who logged in. So, for the labels I'll select the first row of my table (from the left here), for the source data I'll pick the entire table (sans header, of course) and I'll insert into some other area (be sure to leave enough room in this landing area to accommodate the maximum number of stores one VP might have). Next, I'll set the Selected Item's Type on the Behavior tab to Label and for the Item I'll browse to the landing spot from my username Live Office query, the thinking being that when a user runs the dashboard, it will first figure out who they are, then produce a list at runtime that only includes the stores which I've given them access to.

Second, I add a Listbox Selector and maps its Labels properties to the area of the spreadsheet that my first selector dumped its data. From this selection, I can insert the row (or rows) I've brought in from my other Live Office connection that has the actual Store Data using standard Data Insertion protocols. Finally, I built a graph off of whatever data I've dumped out of this second selector (more info on abusing selectors is available Don't go chasing waterfalls (Or how to use "Insert Filtered Rows" effectively)).

In the end, I've allowed all of the data into the model regardless of row-level security which means that I don't have to do any report bursting -- one instance of the report is all that will need to be scheduled and maintained. However, I haven't let users access all of the data, because the only selection they can make has already been filtered internally based on their BOBJ Username. The pros of this particular solution is that it is quick and dirty, it doesn't require bursting, and it doesn't have to have some sort of table or groups maintained in a formal environment. The cons are that it can't really handle a ton of data (not a great use-case for thousands of users or thousands of stores) and that it will probably make your audit people wiggle in their seat. Which I suppose could be a pro so long as you enjoy making them wiggle and you have a marketable resume just in case things turn south.