My Organisation has a requirement to audit specific data returned in queries against the Oracle system and identify the users. Oracle has audit functionality available (fine-grained auditing) to audit the query and the data accessed in the query. However, as the BOE connection to Oracle through a Universe is via a single Oracle user it presents some challenges to identify the user. My Organisation does not want the overhead of maintaining both BOE and Oracle users. Oracles fine grained auditing, however, provides a potential solution, as it can accept user information from an application, and record it as an attribute against the session (see Fine-grained auditing for real world problems - part 2 http://www.oracle.com/technology/pub/articles/nanda_fga_pt2.html). So, can you advise whether this functionality can be easily incorporated into a BOE Oracle connection. If it has to be done through the SDK, which SDK needs to be used and how do I retrieve the BOE user to pass to the Oracle session (eg. EXECUTE DBMS_SESSION.SET_IDENTIFIER (‘BOE_User’)).
My organisation has the Business Objects Enterprise XIR2 (BOE) system running on a Sun Solaris 9 server, with the repository and data sources in Oracle 10g also running on Sun Solaris 9. Users log in to BOE using individual logins and passwords and are authenticated against an LDAP directory. Data connections from BOE are via a single Oracle user defined in each Universe. The organisation currently uses both Deski and Webi reports with plans to create Crystal reports. All connections will be via a Universe connection.
Did you find any solution to set oracle session parameters for universe connections?
We have a quite simliar environment (Solaris, BOXI R2, LDAP, Oracle) and we also access our data with technical users but want oracle to have the user name as a session parameter to apply row level security wiith oracle policies.
Frederik
I have not found any solution to set Oracle session parameters. However Business Objects technical support have logged an enhancement request on my behalf (Adapt ID = ADAPT00962221) which apparently I have to keep an eye out for in a possible future release.
The other suggested workarounds from Business Objects technical support to have a Business Objects user identified in Oracle are: 1) Using kerberos delegation (this is only supported for SQL and oracle possibly only oracle on windows) In this method the user that logs into infoview has their AD security context cached and thrown at the DB.
2) Using DBuser\DBpass. This method the DBuser/DBpass values need to be populated per account, in conjunction with use DB credentials on all universes and some changes would need to be made for crystal to use this method as well. This should be available for any DB but it may have a bug in it on SP2 right now.
I have not tried either of these methods. They are not really the approach I wish to take. I am still investigating whether we can somehow use LDAP with Oracle to avoid user-id and password management in Oracle but still achieve the ability to track individual users.
I am suprised BO technical support did not mention using END_SQL as a database auditing option. It is one of the universe parameters.
We are using it as follow to determine the user running the query, the universe name and the report name:
/:@VARIABLE(‘BOUSER’):@VARIBALE(‘DOCNAME’):/
with the comments in this thread i was able to complete a solution for my problem:
within a universe connection it is possible to define custom parameters. Since BOXI R2 it is possible to set the parameter ConnectInit. The string entered here is executed in the oracle session each time a session is opened.
In my example we created an oracle procedure setsecurity() and set the connectinit parameter to:
begin setsecurity(’@VARIABLE(‘BOUSER’)’); Commit; end;
thanx for the BOUSER parameter
best regards
PS: this is not documented in R2 and even BO support does not have the information.
This is a Universe Parameter called “CONNECTINIT” ???
And you put a PL/SQL block in the Value?
Are you sure that with many users and connection pooling etc. this really works? It seems the first report would create a connection to the datasource and set the paramters. Then it may be “reused” for future “connections”
I have tested this technique, using ConnectInit custom parameter in the Universe connection setup to call a “setsecurity” function and passing the bouser variable. I am on a 10G database in a shared/pooled connection environment, with BOE XI R2. In my function, I call sys_context (userenv) fields and write them all to a table so I can view the contents (good for auditing). With several testers querying test reports at the same time, I captured the exact same sessionid/SID which should indicate the same Oracle shared connection, and our own individual bouser was captured, not the same bouser. Our Oracle DBA explained to me that the shared connection can be used for multiple queries, but each query will have it’s own session variables. This is a security feature of Oracle. I can set the client_identifier to the bouser in the function, or I can create my own context and use that throughout the session. Because the client_identifier can be set by a user who knows what he is doing, there was some question about how secure it was to use the client_identifier for identification of the user at the database level.
I want to use security rights set in an ERP system (using an oracle 9i DB) in my webi-reports. I have read several threads regarding similar issues, where suggestions such as:
END_SQL
or:
ConnectInit = begin DBMS_SESSION.SET_IDENTIFIER(’@Variable(‘BOUSER’)’);COMMIT;END;
have been discusssed. However, I can not get either of these suggestions to work in my case.
I use BOXI R2 SP3 and have created an Oracle OCI connection (as generic ODBC connections don’t work) in the universe.
Does anyone have any tips regarding my problem? If yes; I would appreciate to hear from you
procedure SetSecurity (userid varchar2)
is
begin
DBMS_SESSION.set_context( ‘SESSION_VALUES’, ‘USERID’, userid );
end;
The session value is then used within oracle policies to restrict access. Users and groups are within separate tables and are referenced from the policies.
I was not really able to test this to the end. So be carefull - there could be problems with connections pools and report caching. In both cases the ConnectInit String would not be executed for a new report request from a different user.
Please share experiences when you implement this mechanism.
I still face a similar problem regarding passing the Client ID in the ConnectInit parameter. I am using a Oracle connection (Does not work for this). However, BO has some PDF having this info and tells it now works fine with all data sources. Also, I am not able to understand the second limitation. Please explain this.
Please let me know how to do this using a Oracle driver.
ADAPT00605690 Patch ID: 38,977,350
Description:
The ConnectInit function, which lets parameters be set during connection initialization, has limited usefulness because it does
not apply to all data sources.
For example, if it were possible to apply the ConnectInit function to an Oracle data source, an Oracle connection could be
opened with a security context that uses the CLIENT_IDENTIFIER session variable to filter the data returned to the user.
ConnectINIT could set this variable.
Other databases are requesting that type of security and parameter setting at connection initialization. For example, Informix
queries prioritization, Oracle queries optimizer tuning, and Teradata queries auditing.
New Behavior:
This problem is resolved.
To solve the problem, the ConnectInit function applies to all data sources. This fix has been created for Web Intelligence and
Desktop Intelligence applications, working with thin client documents that are based on Business Objects universes.
Known Limitations:
If many instruction statements need to be set through the ConnectINIT function, it is recommended to do the following:
Ensure that the configuration section of the PRM file for your database is set as follows:
N
When designing a Universe in the Desktop Intelligence Designer, avoid setting instructions into the ConnectINIT parameter in
its current Universe connection to avoid potentially interfering with catalog information.
Can you tell me more about this? Also, does this patch/hotfix work on a Linux server where my BO is installed?Please let me know if this impacts any other feature in BO XI 3.0?
It is to get round an Oracle issue, where every client has to connect using the American language. Its a well know 11i eBusines problem.
Pretty much if you connect normally in sql plus, the views contain no data. If you connect and then set the NLS_LANG to American, the views contain data.
I needed to trap who is running what on our data warehouse from BOXI r2
I have got the END_SQL working in my universe parameter by adding this: /*: User is:@VARIABLE(‘BOUSER’) Document is: @VARIABLE(‘DOCNAME’) */
That is all great - if I run the report through Deski or Infoview it is fine. I can see on the data warehouse the username and the report in the session SQL so that is good.
However if I schedule a report then the document name comes through onto the database session as ‘#Unknown’ - probably not surprising.
Is there an application variable that I can add that will get schedule name or schedule id or anything of that sort that would help me trap a report that is scheduled and running from BOXI on our warehouse database?
(BOB member since 2007-01-31)
(BOB member since 2006-07-06)
(BOB member since 2002-06-25)
(BOB member since 2007-02-07)
(BOB member since 2002-08-15)