X

Welcome to All Things Warehouse Builder

OWB Public Views in 11g Release 1

Jean-Pierre Dijcks
Master Product Manager

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...

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.