Our users want to automatically go to the last workspace they were using when they connect. Since they may use one of several applications, I decided to put this functionality into an after logon trigger on the database.
The trigger, table, and procedures are owned by user WM_UTILS:
----------------------------
create table wm_utils.recent_user_workspaces
( username varchar2(30 byte) primary key,
workspace_name varchar2(30 byte));
create or replace procedure wm_utils.goto_last_workspace authid current_user as
pragma autonomous_transaction;
last_workspace varchar2(30);
begin
last_workspace := get_last_workspace();
if (last_workspace is not null) then
dbms_wm.GotoWorkspace(last_workspace);
end if;
commit;
end;
create or replace procedure wm_utils.set_last_workspace (workspace in varchar2) as
begin
merge into recent_user_workspaces
using dual on (username = user)
when not matched then insert (username, workspace_name) values (user, workspace)
when matched then update set workspace_name = workspace;
end;
create or replace function wm_utils.get_last_workspace return varchar2 as
workspace varchar2(30);
begin
select workspace_name
into workspace
from recent_user_workspaces
where username = user;
return workspace;
exception
when NO_DATA_FOUND then
return null;
end;
create or replace procedure wm_utils.goto_workspace (workspace in varchar2) authid CURRENT_USER as
PRAGMA AUTONOMOUS_TRANSACTION;
begin
dbms_wm.GotoWorkspace(workspace);
set_last_workspace(workspace);
commit;
end;
create or replace trigger wm_utils.on_login
after logon on database
begin
goto_last_workspace();
exception
when others then
null;
end;
------------------------------------------------------
Users call wm_utils.goto_workspace instead of dbms_wm.GotoWorkspace if they want their choice to be remembered for their next login.
The problem is that this fails with error 'ORA-20073: insufficient privileges to ACCESS the workspace: 'TEST_WORKSPACE'. This is a workspace owned by the user who is trying to log in, but it does not have privileges explicitly granted. The user can call wm_utils.goto_last_workspace() after logging in and it works. Is there something about the way permissions work that would prevent this from working in an AFTER LOGON trigger?