We are planning to implement Business Objects on top of an Oracle database, and we’re hoping we can use Oracle’s Virtual Private Database feature (VPD) to control row level security. We believe we can do that by configuring BO to pass the BO username and password to the database (using ALS - Advanced Login Strategy).
Has anybody had any experience running Business Objects in conjunction with ALS and VPD? any words of caution (performance implications, pitfalls, etc)? any input will be appreciated.
BOBbers, Ely is one of my colleagues who is bringing up an entirely new installation of BusinessObjects/WebI for reporting from an Oracle database. We hope that using Oracle’s VPD would be a much more efficient, better-performing way of implementing row-level security.
We’d appreciate any insights you all can give us about VPD and Advanced Login Strategy (where each user is also set up in Oracle, and the BusObj connection is set up to pass the userid/password to Oracle for the query).
ALS is great, as long as BO is ignoring the Password, or you have a nice way of keeping your BO password in sync with the Oracle Password. BO doesn’t provide a method of making sure that they are synchronized.
You will also need to use “Personal” connections to design universes from. I believe that there is an issue with creating a Universe from ALS connections since you need to define the owners for the tables, and your initial “fetch” of tables may not return anything.
I’ve also been forced to “lock-down” the ability of all users to “modify” connections. People like to “change” the secure connection into something that has their username/password when they have issues.
Other than that, I haven’t tried the VPD so you’ll have to let us know how that works out.
Yes, Robert – Ely is thinking of a way of keeping the passwords in sync.
Hmmm – we use ALS and Secured Connections on our Decision-Support-Systems implementation of BusinessObjects – but we do build the universes with a “master” login that is aliased to DBO – so maybe that’s why we haven’t seen a problem with this.
[Updated by Anita at 2:54pm PST]
No – I created a new userid with Designer privileges, and was able to successfully able to navigate the tables/views to which that id’s Sybase group had been granted Select rights.
So, Robert – is this a specific issue with Oracle, or am I misinterpreting you?
I think it had to do with the fact that none of the “users” OWNED any of the tables, nor did they have PRIVATE synonyms to any of the tables. It wasn’t until we specifically granted them select access were they able to find the tables in the browsers.
It may have been the way some of the security was initially set-up, but it seems better now. Initially, unfolding all groups were empty (except for SYS tables). Now they can correctly “prefix” the tables to see the columns. Perhaps we had changed the fact that BO didn’t include OWNER names by default.
Another fact may be that this was initially done on 5.0, not any of the later versions. It could be resolved now automatically.
I don’t know what VPD does, but I don’t think it matters. If you pass the BO id and password to the database, security will be enforced if you have it defined there.
The issues are pretty clear. You have to maintain DB ids for each user. You have to figure out something for password synchronization. Or do what Rob suggest and have BO ignore passwords. I don’t like to ignore passwords, because you won’t have ALS for the repository.
I don’t see a downside. It’s really a philosophical decision. Should business users have database id’s. I prefer not, but there are times when it works better.
See, We have BO6.51 on shared infrastrcutre. We use LDAP to authenticate BO user logins.Now, we are planning to use VPD in oracle for security. In BO repository we don’t maitain any passwords as it is checks againt LDAP. We want use techincal user id (not bo user id) to connect to oracle. So, the connection uses tech user id and password for a universe.
In oracle we maintain security table in that we have all bo user id’s.
Our challenge is to pass bo user id some how to the oracle before we execute any sql queries through business objects.
We only connect though tech user id but before we execute any sql query we need to pass @variable(‘BOUSER’) to oracle.
Is there any solution.
We tried using following queries:-
SELECT * FROM ACCOUNT WHERE (0=(SELECT SET_USERID(@VARIABLE('BOUSER') FROM DUAL)
We thought that in the sub query we can send bo user id though a function to set userenv variables like client_id in oracle. Since VPD is applied on ACCOUNT table at time of validation this query it applies policies based on the tech user id.
Is there any other way?
Or can we call any pl/sql procedure at the time login to bo or connecting to oracle first time?
(BOB member since 2002-06-17)
(BOB member since 2002-08-15)
(BOB member since 2006-10-02)