We have implemented a test of passing the ‘BOUSER’ variable to Oracle and setting a context to implement Oracle’s Virtual Private Database (VPD). It appears to work well: the same report run by two different BOE logins get different results based on restrictions set on their Oracle roles.
My question is, has anyone implemented row-level and column-level (data masking) security using BOE’s implementation of security? Are there major advantages to it as opposed to VPD? What are the down sides? We will eventually be supporting 200+ users.
In general you want to push your data security down as close to the database as possible. This allows you to leverage the security implementation for any product that may wish to report against the data.
We have implemented both Oracle VPD and access restrictions to control user access to data. On our production Oracle systems we only use VPD. It works great and I don’t have to worry about configuring security at the universe level. With the security kept at the database level a mistake on my part when configuring the universe or object access levels in the CMC won’t translate into users being able to see data they shouldn’t.
We have a data warehouse that is housed on a Netezza server which does not have functionality similar to Oracle’s VPD so we had to go with access restrictions. This also works well and a provisioning process is in place to be sure that the data security is correct. Other than setting up the initial access restriction there has been no on-going work at the universe level to support data security.
We’ve used VPD for years, and definitely prefer it over the headaches of universe security – that way, folks using other tools to access the database get the same data they would via BOE, and its much safer all around. Also, you only have to put it in once per data set, vs for each universe in which that data’s represented.
On the con side – BusObj doesn’t make it all that easy - if you have a password expiration policy at the database level and are using Enterprise security via BOE, you need to build something to allow users to keep passwords in sync.
Also, on the con side for VPD, which isn’t really an issue for us because we don’t do this much, if you give users access to scheduled instances, and they’re not actually retrieving the data from the database themselves, they’ll be able to see what’s in the report. Supposedly, if security is universe-based, it’ll be enforced when the instance is viewed.
I am new to the implementing VPD on BO 3.1version.
Can you please suggest me the steps to accomplish this setting in the universe.
What exactly I require in order to start this implementation.
Since, VPD policies have been set up in the Oracle and I am logging in to 3.1WebI using my WinAD authentication. how can I leverage VPD in to BO.
We use the Hint parameter on the database connection to pass the BO user id to the database. The VPD policy reads the user id and applies any require restrictions.
The #x#, #y#, #z# define each parameter, the VPD uses those tags to extract each piece of data.
You don’t need @Prompt in the string unless you want to prompt the user for a date that would be used by the VPD policy. If all you want to do is get the user name then you could use somethign as simple as
I haven’t seen 3.x yet, but I don’t think (hope!) this changed… we don’t do anything specific within BOE, rather have the universe connection set to use database credentials associated with the user account, and that takes care of the rest.
I am able to pass the @VARIABLE(‘BOUSER’) to the VPD policy and run the reports based on the security set up.
Now my other requirement, we have audit tables and switched on the "workspace manager"in Oracle to track the time and run the audit reports only based on the user selected input.
We have this other procedure created but how can I prompt the user to choose “From-To” dates while running the reports.
Am I clear in my requirement. Please input your thoughts.
The user can specify any value they wish for the prompt, it is up to you to determine how to use that value in your procedure to produce the desired results. If you want the prompt text to be dynamic then, no, the prompts cannot be dynamic.
Can you please let em know how the Oracle VPD can access the Hint parameter? Can you please post an example of the VPD function code where it is parsing the Hint parameter? If the HINT is being parsed before going to the VPD function, I would like to have an example.
Basically I need the handshake code that reads the Hint parameter in Oracle.
Hi,
Can you please let em know how the Oracle VPD can access the Hint parameter? Can you please post an example of the VPD function code where it is parsing the Hint parameter? If the HINT is being parsed before going to the VPD function, I would like to have an example.
Basically I need the handshake code that reads the Hint parameter in Oracle.
The procedure you create as part of the VPD policy will get the SQL statement and parse out the data you need. I cannot provide the code we use as I am a consultant and do not own the code.
Your DBA’s should be able to create a procedure that does what you need.
I am able to pass the BOUSER and retrieve the results based on the VPD policy setup.
Now, i am trying to pass @Prompts which can read the workspace manager PIT tables to run audit reports. Can you please help in providing a solution for passing @Prompts in the HINT parameter or ConnectInit.
You can only pass the values in the Hint parameter, the ConnectInit value must be a valid SQL command that you would want run once each time the connection is established.
In your universe connection definition you set the value of the Hint parameter to the text you want passed with each query. My original example showing the values we pass includes two prompts and the BOUSER variable. Modify this example to suit your needs.
I do not know anything about Workspace Manager so I cannot help with that, sorry.
I was using your example to pass the @Prompts along with the @Variable in the ConnectInit, but no result of @prompt is been carried when running a report. VPD works well using @Variable(‘BOUSER’).
Can you please send me exact syntax for passing @Prompt in the Hint parameter. Also, is the Prompt text a free text which will be passed to the user?
(BOB member since 2006-07-28)
(BOB member since 2002-08-15)