Data warehouse database is Oracle. Universe has shared login ie all sessions use the same username/password (let’s say boenterprise/boenterprise) to access warehouse database.
If user1 makes a query with InfoView (logs into InfoView user1) then in Orace database the the session info in v$session shows that query is executed by boenterprise.
If user2 makes a query (logs into InfoView as user2) the in database level this query is also executed by boenterprise.
Is is possible to populate column client_info in Oracle v$session view with user info (Oracle database has procedure DBMS_APPLICATION_INFO.SET_CLIENT_INFO for that purpose). For example when query is executed by user1, then in v$session username = boenterprise ja client_info = user1 and when it’s made by user2, then username = boeneterpise and client_info = user2?
What you can do is use the END_SQL statement in your universe parameters to ‘tag’ you sql with information like the BO username, universe, data provider and report that the sql was generated for. That might help you.
Thank you Nick for the END_SQL hint. It’s something I didn’t know. With END_SQL I can add BO username as a comment at the end of the SQL and in database level DBA can get the sql_id from v$session and using sql_id it’s possible to see the actual sql query from v$sql system view.
But it’s still not 100% I want to achieve. Is it somehow possibe to make it so that when user logs into InfoView the first action is to call DBMS_APPLICATION_INFO.SET_CLIENT_INFO and set username into v$session.client_info and then run queries?
(BOB member since 2009-05-12)
(BOB member since 2002-08-15)