REM dbdrv: none REM +=======================================================================+ REM | Copyright (c) 1996 Oracle Corporation, Redwood Shores, CA, USA | REM | All rights reserved. | REM +=======================================================================+ REM | FILENAME REM | wfsecwsb.sql REM | DESCRIPTION REM | PL/SQL body for package: WFA_SEC REM | Web Agent Security implementation using web server REM | REM | HISTORY REM | 23 May 1996 gbuzsaki New spec, error messages REM | 10 Oct 2000 mcraig Logout Icon appears when running APACHE REM | 02 JAN 2002 JWSMITH Increased uname to varchar2(320) REM +=======================================================================+ SET VERIFY OFF WHENEVER SQLERROR EXIT FAILURE ROLLBACK; set arraysize 5 set scan off create or replace package body WFA_SEC as /* $Header: wfsecwsb.pls 26.24 2004/07/23 08:29:54 vshanmug ship $ */ -- -- Error (PRIVATE) -- Print a page with an error message. -- Errors are retrieved from these sources in order: -- 1. wf_core errors -- 2. Oracle errors -- 3. Unspecified INTERNAL error -- procedure Error as begin htp.htmlOpen; htp.headOpen; htp.title(wf_core.translate('ERROR')); htp.headClose; begin wfa_sec.Header(background_only=>TRUE); exception when others then htp.bodyOpen; end; htp.header(nsize=>1, cheader=>wf_core.translate('ERROR')); htp.bold (wf_core.translate('WFA_NO_LOGIN')); htp.br; htp.br; wfa_sec.Footer; htp.htmlClose; end Error; -- -- CreateSession -- procedure CreateSession( c_user_name in varchar2, c_user_password in varchar2) is colon number; uname varchar2(320); begin begin colon := instr(c_user_name, ':'); if (colon = 0) then select name into uname from wf_users where name = c_user_name and rownum < 2; else select name into uname from wf_users where orig_system = substr(c_user_name, 1, colon-1) and orig_system_id = substr(c_user_name, colon+1) and name = c_user_name; end if; exception when no_data_found then wf_core.token('USER', c_user_name); wf_core.raise('WFSEC_USER_PASSWORD'); end; -- Prepare a cookie for this session owa_util.mime_header('text/html', FALSE); owa_cookie.remove('MetaMolari', 'LOGIN', '/'); owa_cookie.send('WF_SESSION', c_user_name, '', '/'); owa_util.http_header_close; exception when others then wf_core.context('Wfa_Sec', 'CreateSession', c_user_name); raise; end CreateSession; -- -- GetSession -- procedure GetSession(user_name out varchar2) is c_territory VARCHAR2(80); c_language VARCHAR2(80); c_date_format VARCHAR2(40); uname varchar2(320); begin -- -- Get Remote user -- uname := upper(owa_util.get_cgi_env('REMOTE_USER')); -- -- if no remote user exists raise error -- if ( uname is null ) then -- wf_core.raise('WFSEC_NO_SESSION'); -- end if; -- user_name := uname; -- Get the language preference c_language := NVL(wf_pref.get_pref (user_name, 'LANGUAGE'), 'AMERICAN'); -- Get the terriory preference c_territory := NVL(wf_pref.get_pref (user_name, 'TERRITORY'), 'AMERICA'); -- get the date format preference c_date_format := NVL(wf_pref.get_pref (user_name, 'DATEFORMAT'), 'DD-MON-RRRR'); c_language := ''''||c_language||''''; c_territory := ''''||c_territory||''''; c_date_format := ''''||c_date_format||''''; dbms_session.set_nls('NLS_LANGUAGE' , c_language); dbms_session.set_nls('NLS_TERRITORY' , c_territory); dbms_session.set_nls('NLS_DATE_FORMAT', c_date_format); exception when others then wf_core.context('Wfa_Sec', 'GetSession'); raise; end GetSession; -- -- Header -- Print an html page header -- IN -- background_only - Only set background with no other header -- disp_find - When defined, Find button is displayed, and the value -- is the URL the Find button is pointting to. -- procedure Header(background_only in boolean default FALSE, disp_find in varchar2 default null, page_title in varchar2 default null, inc_lov_applet in boolean default TRUE, pseudo_login in boolean default FALSE) -- pseudo_login is used in wfsecicb.sql only. is begin htp.p(''); if (not background_only) then /* ** Create main table for toolbar and icon */ htp.p(''); htp.p(''); /* ** Put some space on the side */ htp.p(''); htp.p(''); /* ** Create the logo and close the toolbar and logo table */ htp.p('
'); /* ** inner table to define toolbar */ htp.p(''); /* ** Left rounded icon for toolbar */ htp.p(''); /* ** White line on top of toolbar */ htp.p(''); /* ** Right rounded icon for toolbar */ htp.p(''); /* ** End the table row for the icons that surround the real toolbar */ htp.p(''); /* ** Start the table for the real controls */ htp.p(''); /* ** Always create the home icon */ htp.p(''); /* ** Create the page title. If no page title was passed then use ** the default product title icon - Oracle Workflow */ htp.p(''); /* ** Create the dividing line */ htp.p(''); htp.p(''); /* ** Create the black border under the toolbar and close the icon table */ htp.p(''); htp.p('
'); htp.p(''|| ''); htp.p(''); htp.p(''); if (page_title IS NULL) then htp.p(' '||wf_core.translate('WF_WORKFLOW_TITLE')||' '); else htp.p(' '||page_title||' '); end if; htp.p(''); htp.p(''); /* ** Optionally create the find icon */ if (disp_find is not null) then htp.p(''|| '' || WF_CORE.Translate('QUERY_DETAIL') || ''); htp.p(''); end if; /* ** if running under webdb creat logout icon ** We should made this more generic as any oracle http server will work. ** That includes: ** ORACLE WEBDB ** ORACLE HTTP SERVER ** ORACLE-HTTP-SERVER (10g) */ htp.p(''); /* if ((upper(substr(owa_util.get_cgi_env('SERVER_SOFTWARE'),1,12)) = 'ORACLE WEBDB') OR (upper(substr(owa_util.get_cgi_env('SERVER_SOFTWARE'),1,6)) = 'APACHE') OR (instr(upper(owa_util.get_cgi_env('SERVER_SOFTWARE')), 'ORACLE HTTP SERVER')>0)) then */ if ((instr(upper(owa_util.get_cgi_env('SERVER_SOFTWARE')),'ORACLE') > 0) OR (upper(substr(owa_util.get_cgi_env('SERVER_SOFTWARE'),1,6)) = 'APACHE') ) then htp.p(''|| '' || WF_CORE.Translate('LOGOUT') || ''); htp.p(''); end if; /* ** Create the help icon */ htp.p('' || WF_CORE.Translate('WFMON_HELP_DETAILS') || '
'); /* ** Close the toolbar table data */ htp.p('
'); /* ** This was a test to see how preloading the applet to ** improve performance would work out. */ end if; exception when others then wf_core.context('Wfa_Sec', 'Header'); raise; end Header; -- -- Footer -- Print an html page footer -- procedure Footer is begin htp.bodyClose; exception when others then wf_core.context('Wfa_Sec', 'Footer'); raise; end Footer; -- -- DetailURL -- Produce URL for notification detail and response page. -- IN -- nid - notification id -- RETURNS -- URL of detail and response page for notification. -- function DetailURL(nid in number) return varchar2 is begin return('wfa_html.detail?nid='||to_char(nid)); exception when others then Wf_Core.Context('Wfa_Sec', 'DetailURL', to_char(nid)); raise; end DetailURL; -- -- PseudoSession - create ICX psuedo session for the client -- Creates a temp ICX session for the current user coming into ICX -- from an email notification with a link to the applications. -- Session information is typically stored on the web client as an -- http cookie. This only applies to ICX so only wfsecicb will -- have an actual implementation for this function. The others -- do nothing except close the http header. -- -- Added setting of user preference here, so that a French user -- when viewing a detached notification will still view this in -- French instead of English. procedure PseudoSession(IncludeHeader in BOOLEAN default TRUE, user_name in varchar2 default null) -- We are only using Include Header Parameter in ICX. is c_territory VARCHAR2(80); c_language VARCHAR2(80); c_date_format VARCHAR2(40); role_info_tbl wf_directory.wf_local_roles_tbl_type; begin if (IncludeHeader) then owa_util.http_header_close; end if; if (user_name is not null) then Wf_Directory.GetRoleInfo2(user_name,role_info_tbl); -- Get the language preference c_language := ''''||role_info_tbl(1).language||''''; -- Get the terriory preference c_territory := ''''||role_info_tbl(1).territory||''''; -- get the date format preference c_date_format := ''''||NVL(wf_pref.get_pref(user_name,'DATEFORMAT'), 'DD-MON-RRRR')||''''; dbms_session.set_nls('NLS_LANGUAGE' , c_language); dbms_session.set_nls('NLS_TERRITORY' , c_territory); dbms_session.set_nls('NLS_DATE_FORMAT', c_date_format); end if; exception when others then Wf_Core.Context('Wfa_Sec', 'PseudoSession'); raise; end PseudoSession; -- -- Create_Help_Syntax -- Create the javascript necessary to launch the help function -- Since this is only required for the apps install case -- I have covered this function with a wfa_sec function. -- The other wfsec cases are just a stub. -- procedure Create_Help_Syntax ( p_target in varchar2 default null, p_language_code in varchar2 default null) IS begin null; exception when others then Wf_Core.Context('Wfa_Sec', 'Create_Help_Syntax'); raise; end Create_Help_Syntax; -- -- get_role_info -- Gets role info for the user sources that we know about rather -- than using the ugly expensive wf_roles view -- procedure get_role_info ( role in varchar2, name out varchar2, display_name out varchar2, description out varchar2, email_address out varchar2, notification_preference out varchar2, language out varchar2, territory out varchar2, orig_system out varchar2, orig_system_id out number) IS begin null; --Not calling get_role_info2 unnecessarily end get_role_info ; procedure get_role_info2( role in varchar2, name out NOCOPY varchar2, display_name out NOCOPY varchar2, description out NOCOPY varchar2, email_address out NOCOPY varchar2, notification_preference out NOCOPY varchar2, language out NOCOPY varchar2, territory out NOCOPY varchar2, orig_system out NOCOPY varchar2, orig_system_id out NOCOPY number, FAX out NOCOPY VARCHAR2, STATUS out NOCOPY VARCHAR2, EXPIRATION_DATE out NOCOPY DATE ) IS begin null; end get_role_info2 ; -- -- ResetCookie -- -- IN: Name of the cookie to be reset to -1. -- procedure ResetCookie(cookieName in varchar2) is BEGIN owa_cookie.send(name=>cookieName, value=>'-1', expires=>'', path=>'/'); end ResetCookie; -- -- GET_PROFILE_VALUE (PRIVATE) -- function Get_Profile_Value(name varchar2, user_name varchar2) return varchar2 is begin return null; exception when OTHERS then Wf_Core.Context('Wfa_Sec', 'Get_Profile_Value', name, user_name); raise; end Get_Profile_Value; -- Local_Chr -- Return specified character in current codeset -- IN -- ascii_chr - chr number in US7ASCII function Local_Chr( ascii_chr in number) return varchar2 is begin if (WF_CORE.LOCAL_CS is null) then WF_CORE.LOCAL_CS := (substr(userenv('LANGUAGE'), (instr(userenv('LANGUAGE'),'.') + 1))); end if; if (ascii_chr = 10) then if (WF_CORE.LOCAL_CS_NL is null) then WF_CORE.LOCAL_CS_NL := (convert( chr(10), WF_CORE.LOCAL_CS, 'US7ASCII')); end if; return WF_CORE.LOCAL_CS_NL; elsif (ascii_chr = 9) then if (WF_CORE.LOCAL_CS_TB is null) then WF_CORE.LOCAL_CS_TB := (convert( chr(9), WF_CORE.LOCAL_CS, 'US7ASCII')); end if; return WF_CORE.LOCAL_CS_TB; else return( convert( chr(ascii_chr), WF_CORE.LOCAL_CS, 'US7ASCII')); end if; end Local_Chr; -- -- DirectLogin - Return proper function name for DirectLogin --Bug: 1566390 -- -- Bug 1838410: Added code/functionality from 11.0 -- function DirectLogin (nid in NUMBER) return VARCHAR2 IS BEGIN return ('WFA_HTML.Login?i_direct=' || WFA_SEC.DetailURL(nid)); exception when others then Wf_Core.Context('Wfa_Sec', 'DirectLogin', to_char(nid)); raise; end DirectLogin; -- -- GetFWKUserName -- Return current Framework user name -- -- NOTE: DO NOT implement outside of embedded environment until we -- understand the standalone Framework implementation. -- function GetFWKUserName return varchar2 is begin Wf_Core.Raise('WFXXX_STUB'); exception when others then Wf_Core.Context('Wfa_Sec', 'GetFWKUserName'); raise; end GetFWKUserName; -- -- Logout -- This is a dummy procedure, wfa_html.logout should be used -- unless single signon feature is activated -- procedure Logout is begin return; end Logout; -- -- DS_Count_Local_Role (PRIVATE) -- Returns count of a role in local directory service table -- IN -- role_name - role to be counted -- RETURN -- count of provided role in local directory service table -- function DS_Count_Local_Role(role_name in varchar2) return number is cnt number; begin select count(1) into cnt from WF_LOCAL_ROLES where NAME = role_name and ORIG_SYSTEM = 'WF_LOCAL_ROLES' and ORIG_SYSTEM_ID = 0; return(cnt); exception when others then WF_CORE.Context('WFA_SEC', 'DS_Count_Local_Role', role_name); raise; end DS_Count_Local_Role; -- -- DS_Update_Local_Role (PRIVATE) -- Update old name user/role in local directory service tables with new name -- IN -- OldName - original name to be replaced -- NewName - new name to replace -- procedure DS_Update_Local_Role( OldName in varchar2, NewName in varchar2 ) is begin update WF_LOCAL_ROLES set NAME = NewName where NAME = OldName and USER_FLAG = 'Y' and ORIG_SYSTEM = 'WF_LOCAL_USERS' and ORIG_SYSTEM_ID = 0; -- Update local user roles update WF_LOCAL_USER_ROLES set USER_NAME = NewName, ROLE_NAME = NewName where USER_NAME = OldName and USER_ORIG_SYSTEM = 'WF_LOCAL_USERS' and USER_ORIG_SYSTEM_ID = 0 and ROLE_NAME = OldName; update WF_LOCAL_USER_ROLES set USER_NAME = NewName where USER_NAME = OldName and USER_ORIG_SYSTEM = 'WF_LOCAL_USERS' and USER_ORIG_SYSTEM_ID = 0; exception when others then WF_CORE.Context('WFA_SEC', 'DS_Update_Local_Role', OldName, NewName); raise; end DS_Update_Local_Role; function GetUser return varchar2 is username varchar2(320); begin wfa_sec.GetSession(username); return username; exception when others then --Incase of exception just return null return ''; end; -- -- user_id -- Return current user id, in apps, wrapper to FND_GLOBAL.user_id -- In standalone, returns -1. function user_id return number is begin return -1; end; -- -- login_id -- Return current login id, in apps, wrapper to FND_GLOBAL.login_id -- In standalone, returns -1. function login_id return number is begin return -1; end; -- -- security_group_id -- Return current security_group_id, in apps, wrapper to -- FND_GLOBAL.security_group_id In standalone, returns -1. function security_group_id return number is begin return -1; end; -- -- CheckSession -- Check the cached ICX session id against the current session id to determine -- if the session has been changed. This function caches the current session id -- after the check. (STANDALONE IMPLEMENTATION) -- RETURN -- boolean - True if session matches, else False function CheckSession return boolean is begin return true; end CheckSession; end WFA_SEC; / commit; exit