OWB Public Views in 11g Release 1

In OWB 11g the repository is a set of workspaces. In the public views this means you need to set the workspace context.This is not an issue from within a map as the workspace context is set prior to invoking the map as the map audit needs this.

However in order to view data inside OWB using the public views it is necessary to have this set and as OWB creates the connection, the only solution is a logon trigger. This also applies if you are going into any system where you do not control the login. In SQL Plus, you simply run the procedure after logging in or do some session stuff...

As the trigger requires a pl/sql procedure that has execution rights on owbsys.wb_workspace_management.set_workspace this
needs to be granted to the user. In addition, the OWB roles need to be enabled.

The workaround for this is to create the following procedure, grant the following rights to the users and then the logon trigger….

Note the logon trigger has to be granted by sysdba.

Package Script

CREATE OR REPLACE procedure wb_initialize_workspace
authid current_user as

/* To return the first workspace owned by a user */
CURSOR ws_user_owner IS
SELECT MIN(workspace_name) KEEP (DENSE_RANK FIRST ORDER BY workspace_id), user_name
FROM owbsys.all_iv_workspace_assignments
where user_name = USER and isworkspaceowner = 1
GROUP BY user_name;

/* To return the first workspace associated with a user */
CURSOR ws_user_user IS
SELECT MIN(workspace_id) KEEP (DENSE_RANK FIRST ORDER BY workspace_id)
FROM owbsys.all_iv_workspace_assignments
where user_name = USER
GROUP BY user_name;

p_id number;

/* To find the owner of a given workspace */
CURSOR ws_owner_user IS
SELECT workspace_name, user_name
FROM owbsys.all_iv_workspace_assignments
where workspace_id = p_id and isworkspaceowner = 1;

p_ws varchar2 (32);
p_user varchar2 (32);

BEGIN

/* First try to find a workspace owned by this user. If found make that the active workspace */
open ws_user_owner;
FETCH ws_user_owner INTO
p_ws,
p_user;

IF not(ws_user_owner%NOTFOUND) THEN
owbsys.wb_workspace_management.set_workspace(p_ws,p_user);
close ws_user_owner;
ELSE

/* If none owned, get the first associated with this user */
open ws_user_user;
FETCH ws_user_user INTO
p_id;
IF not(ws_user_user%NOTFOUND) THEN

/* Now find the owner of this workspace */
close ws_user_user;
open ws_owner_user;
FETCH ws_owner_user INTO
p_ws,
p_user;
IF not(ws_owner_user%NOTFOUND) THEN
owbsys.wb_workspace_management.set_workspace(p_ws,p_user);
close ws_owner_user;
END IF;
END IF;
END IF;

END;
/

Grants script

define user = &1
grant execute on owbsys.wb_workspace_management to &user;
grant execute on owbsys.wb_initialize_workspace to &user;
alter user &user default role all except OWB$CLIENT;

Trigger script

set concat ~
define user = &1
CREATE OR REPLACE TRIGGER &user~_LOGON_TRIGGER
AFTER LOGON ON &user.SCHEMA
BEGIN
owbsys.wb_initialize_workspace;
END;
/

In addition when using PL/SQL to query the views, for any design time views you will need to grant select access to the user from owbsys (as roles don't work from within pl/sql). This is of course outside of the trigger stuff discussed here...

Comments:

Post a Comment:
  • HTML Syntax: NOT allowed
About

ETL, CDC, Real-Time DI and Data Quality for the Oracle Database from the inside.

Search

Archives
« July 2015
SunMonTueWedThuFriSat
   
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
 
       
Today