prompt Package body wsgsso...
create or replace package body wsgsso is
--
c_session_duration_hours constant number := 3;
v_current_user wsgsso_user.user_name%type;
--
--***********************************************************************
--
-- Check_Session: Check that the supplied client ID is valid
-- for the IP address of the client, that the
-- session is still valid, and that the client
-- ID supplied is not null.
--
--***********************************************************************
function check_session( p_client_id in varchar2,
p_current_ip in varchar2 )
return boolean
is
l_stored_ip wsgsso_sessions.ip_address%type;
l_check_ok boolean := false;
l_expires wsgsso_sessions.expires%type;
begin
if p_client_id is not null and p_current_ip is not null then
l_check_ok := true;
--
-- Check wsgsso_sessions table for an existing session
-- with the specified ID and the current IP address
--
begin
select ip_address
, expires
into l_stored_ip
, l_expires
from wsgsso_sessions
where client_id = p_client_id
and ip_address = p_current_ip;
exception when no_data_found then
l_check_ok := false;
l_expires := sysdate;
end;
--
-- Check that the session has not expired
--
if (sysdate > l_expires) then
l_check_ok := false;
end if;
else
--
-- Supplied client ID was null, so return fail.
--
l_check_ok := false;
end if;
--
-- If an invalid session was found (either expired or for
-- another IP address) then delete that session.
--
if not l_check_ok and p_client_id is not null then
dbms_transaction.begin_discrete_transaction;
delete from wsgsso_sessions where client_id = p_client_id;
commit;
end if;
--
return l_check_ok;
end check_session;
--***********************************************************************
--
-- Generate_Client_Id: Generate a unique identifier for a new session.
--
--***********************************************************************
function generate_client_id return varchar2
is
l_client_id wsgsso_sessions.client_id%type;
l_is_unique boolean := false;
l_count integer;
begin
-- Generate a pseudo random number.
select to_char(to_number( userenv( 'sessionid' ) ) *
to_number( to_char( sysdate,'DDDSSSS' ) ) )
into l_client_id
from sys.dual;
--
-- Check that it is unique in the session table.
-- If it is not, increment it until it is.
--
while not l_is_unique loop
begin
select count(*)
into l_count
from wsgsso_sessions
where client_id = l_client_id;
exception when no_data_found then
l_count := 0;
end;
if l_count = 0 then
--
-- The ID does not already exist in session table
--
l_is_unique := true;
else
l_client_id := to_number(l_client_id) + 1;
end if;
end loop;
return l_client_id;
end generate_client_id;
--
--***********************************************************************
--
-- Get_User: Returns user name of current user.
--
--***********************************************************************
function get_user return varchar2
is
begin
return v_current_user;
end get_user;
--
--***********************************************************************
--
-- Get_User_Data: Returns information about a user, specified by
-- p_key. This implementation accepts the key
-- 'PASSWORD' only. Any other key will result in
-- a null string being returned.
--
--***********************************************************************
function get_user_data( p_key in varchar2 ) return varchar2
is
l_client_id wsgsso_sessions.client_id%type;
l_password wsgsso_user.password%type;
begin
if upper(p_key) = 'PASSWORD' then
--
-- Get the current client ID
--
l_client_id := WSGL.GetClientID;
--
-- Get user's password by joining to user table
--
if l_client_id is not null then
begin
select password
into l_password
from wsgsso_user u
, wsgsso_sessions s
where s.client_id = l_client_id
and u.user_name = s.user_name;
exception when others then
l_password := null;
end;
end if;
end if;
return l_password;
end get_user_data;
--
--***********************************************************************
--
-- Build_Request_Url: Build URL to be passed as requested URL to the
-- Login Server. It is built from CGI environment
-- variables.
--
--***********************************************************************
function build_request_url( p_package_name in varchar2 ) return varchar2
is
l_protocol varchar2(256);
l_request_url varchar2(4000);
begin
--
-- The mod_plsql gateway will pass in the protocol in the
-- environment variable REQUEST_PROTOCOL
-- The SERVER_PROTOCOL, which the Apache Listener sets,
-- and currently always sets to HTTP/1.0 will not be modified
-- by the gateway.
--
l_protocol := owa_util.get_cgi_env('REQUEST_PROTOCOL');
--
-- Defualt the protocol to http
--
if (l_protocol is null) or (length(l_protocol) = 0) then
l_protocol := 'http';
end if;
--
-- Build the request URL
-- The the request URL is built from CGI environment variables.
--
l_request_url:=lower(l_protocol) || '://' ||
owa_util.get_cgi_env('HTTP_HOST') ||
owa_util.get_cgi_env('SCRIPT_NAME');
--
-- The if the request was a post then we don't have the posted
-- values. We therefore need to go back to the startup page to
-- ensure correct entry into the application.
-- If the request is a get we have everything we need to go right
-- back into our original destination.
--
if owa_util.get_cgi_env('REQUEST_METHOD')='POST' then
l_request_url:=l_request_url || '/' || p_package_name ||
'.startup';
--
-- If the package is a module component package we need to
-- pass a default value for Z_CHK. Module component package names
-- do not
--
if INSTR(p_package_name,'$',-1)=0 then
l_request_url:=l_request_url || '?Z_CHK=0';
end if;
else
l_request_url:=l_request_url || owa_util.get_cgi_env('PATH_INFO');
if owa_util.get_cgi_env('QUERY_STRING') is not null then
l_request_url:=l_request_url|| '?' ||
owa_util.get_cgi_env('QUERY_STRING');
end if;
end if;
--
return l_request_url;
end build_request_url;
--
--***********************************************************************
--
-- Gen_HTML_Post_Str: Generate a form to redirect to
-- l_gen_url.
--
--***********************************************************************
function gen_html_post_str( l_gen_url in varchar2 ) return varchar2
is
l_htmlstr VARCHAR2(4000);
l_ls_url VARCHAR2(2000);
l_tname VARCHAR2(100);
l_tvalue VARCHAR2(1000);
l_len NUMBER;
l_qindex NUMBER;
l_eq_index NUMBER;
begin
l_len := length(l_gen_url);
l_qindex := instr(l_gen_url, '?');
l_eq_index := instr(l_gen_url, '=');
l_ls_url := substr(l_gen_url, 0, l_qindex-1);
l_tname := substr(l_gen_url, l_qindex+1, l_eq_index-l_qindex-1);
l_tvalue := substr(l_gen_url, l_eq_index+1);
l_htmlstr :=
''
|| '';
return l_htmlstr;
exception
when others then
htp.p(sqlerrm);
end gen_html_post_str;
--
--***********************************************************************
--
-- get_cancel_url: Obtain the cancel URL
-- This is the URL that the user is returned to if they
-- click on the cancel button on the logon form.
-- The URL should be a public page that has no security.
-- This implementation merely gets the value from the
-- WSGSSO_CONSTANTS package that is created when
-- wsgsso.sql is run.
--
--***********************************************************************
function get_cancel_url return varchar2
is
begin
return wsgsso_constants.g_cancel_url;
end get_cancel_url;
--
--***********************************************************************
--
-- get_listener_token: Obtain the listener_token
-- The listener token should be the
-- : for the listener that this
-- application is running on.
-- This implementation builds the token from CGI
-- environment variables.
-- The listener token value *must* be the same as
-- the value supplied to the regapp.sql script when
-- the enabler configuration info was registered.
--
--***********************************************************************
function get_listener_token return varchar2
is
l_listener_token VARCHAR2(1000);
begin
l_listener_token := owa_util.get_cgi_env('SERVER_NAME') ||
':' || owa_util.get_cgi_env('SERVER_PORT');
return l_listener_token;
end get_listener_token;
--
--***********************************************************************
--
-- SSO_Redirect: Cause a redirect to the login server
--
--***********************************************************************
procedure sso_redirect( p_package_name in varchar2 )
is
l_html_post VARCHAR2(32000);
l_gen_redirect_url VARCHAR2(32000);
begin
--
-- Generate the redirect URL
--
l_gen_redirect_url :=
wwsec_sso_enabler_private.generate_redirect
(
p_lsnr_token => get_listener_token(),
urlrequested => build_request_url(p_package_name),
urloncancel => get_cancel_url()
);
--
-- The l_gen_redirect_irl is usually large url which might
-- get truncated by browser.
-- Instead of using owa_util.redirect_url, we will use
-- HTTP POST for sending redirect
-- For mobile applications etc. it might not be possible to use HTTP
-- POST since it may not support html hidden form parameter
-- owa_util.redirect_url(l_gen_redirect_url);
-- Note however that redirecting by HTTP POST does not work with some
-- mobile devices that do not support the javascript on_load event.
-- Using the owa_util.redirect will work with mobile devices.
--
l_html_post := gen_html_post_str(l_gen_redirect_url);
htp.p(l_html_post);
--
end sso_redirect;
--
--***********************************************************************
--
-- package_permission: Check that the user p_username has a
-- responsibility that allows it to access the
-- package p_package_name. Return true if the user
-- does have access, false otherwise.
--
--***********************************************************************
function package_permission ( p_username in varchar2,
p_package_name in varchar2 )
return boolean
is
l_rows integer := 0;
begin
--
-- Lookup the roles that are common to the package and
-- user passed in.
--
begin
select count(*)
into l_rows
from wsgsso_userresp ur
, wsgsso_perm pm
where ur.user_name = p_username
and pm.package_name = p_package_name
and ur.responsibility_name = pm.responsibility_name;
exception when no_data_found then
l_rows := 0;
end;
--
-- If no rows were found the user does not have permission
--
if l_rows = 0 then return false;
end if;
return true;
end;
--
--***********************************************************************
--
-- Security_Check: Returns true if the currently logged on user is
-- allowed access to the specified package. If not,
-- will return false. This function will also provide
-- a means for the user to log on.
--
--***********************************************************************
function security_check( p_package_name in varchar2 ) return boolean
is
l_client_id wsgsso_sessions.client_id%type;
l_current_ip wsgsso_sessions.ip_address%type;
begin
--
-- Get the current client ID and IP address
--
l_client_id := WSGL.GetClientID;
l_current_ip := owa_util.get_cgi_env('REMOTE_ADDR');
if l_client_id is not null then
--
-- Call check_session to ensure current session
-- is valid
--
if check_session( l_client_id, l_current_ip ) then
--
-- Get user name
--
begin
select user_name
into v_current_user
from wsgsso_sessions
where client_id = l_client_id;
exception when others then
v_current_user := null;
end;
else
--
-- No valid session was found so redirect to the login-server
-- for authentication.
--
sso_redirect(p_package_name);
return false;
end if;
--
-- Check that the user has correct package permission
--
if package_permission( v_current_user, p_package_name ) then
--
-- User is logged on and has permission to access
-- the specified package.
--
return true;
else
--
-- User is logged on but does not have permission
-- to access the specified package
--
show_no_access;
return false;
end if;
else
--
-- No current client ID, so redirect to the login server
-- for authentication.
--
sso_redirect( p_package_name );
return false;
end if;
end security_check;
--
--***********************************************************************
--
-- Valid_Username: returns true if username is a known username.
--
--***********************************************************************
function valid_username( p_username in varchar2 ) return boolean
is
l_rows number;
begin
begin
select count(*)
into l_rows
from wsgsso_user
where user_name = p_username;
exception when no_data_found then
l_rows := 0;
end;
if l_rows = 0 then return false; end if;
return true;
end valid_username;
--
--***********************************************************************
--
-- Update_Session: updates the wsgsso_session table with information
-- passed. Must pass true for z_direct_call. This
-- prevents update_sesssion being called from a URL
-- request because a boolean cannot be passed.
-- The value of p_client_id passed back is the new
-- client session id. This maybe different from the
-- value passed in if that did not correspond to a
-- currently valida row in wsgsso_sessions table.
-- If p_username does not correspond to a known
-- username the invalid_user exception is raised.
--
--***********************************************************************
procedure update_session( p_client_id in out varchar2,
p_ip_address in varchar2,
p_username in varchar2,
z_direct_call in boolean default false )
is
pragma autonomous_transaction;
begin
if z_direct_call then
--
-- Verify that the user is a valid user on this application.
--
if not valid_username(p_username) then
--
-- User is not a known user. They do not have access.
--
raise wsgl.invalid_user;
end if;
--
-- Check that current client ID is valid
--
if p_client_id is not null and
check_session( p_client_id, p_ip_address ) then
--
-- Update the session table for this client with
-- the new user
--
dbms_transaction.begin_discrete_transaction;
update wsgsso_sessions
set user_name = p_username
where client_id = p_client_id;
commit;
else
--
-- Current client ID is not valid so create a new one
--
p_client_id := generate_client_id;
--
-- Create new session in session table
--
dbms_transaction.begin_discrete_transaction;
insert into wsgsso_sessions( client_id, user_name, ip_address, expires )
values( p_client_id, p_username, p_ip_address,
(sysdate + (c_session_duration_hours /24)) );
commit;
end if;
end if;
exception
when others then
raise;
end update_session;
--
--***********************************************************************
--
-- Sign_On: Recieves encoded url login cookie from login server and
-- uses it to create wsgsso session.
--
--***********************************************************************
procedure sign_on( urlc in varchar2 )
is
l_sso_user_name wsgsso_sessions.user_name%type;
l_ip_address wsgsso_sessions.ip_address%type;
l_sso_time_remaining VARCHAR2(1000);
l_site_time_stamp VARCHAR2(1000);
l_url_requested VARCHAR2(1000);
l_unused_param VARCHAR2(1000);
l_client_id wsgsso_sessions.client_id%type;
begin
--
-- Process urlc token passed from login server.
--
wwsec_sso_enabler_private.parse_url_cookie
(
p_lsnr_token => get_listener_token,
encrypted_urlcookie => urlc,
ssousername => l_sso_user_name,
ipadd => l_ip_address,
ssotimeremaining => l_sso_time_remaining,
sitetimestamp => l_site_time_stamp,
urlrequested => l_url_requested,
newsitekey => l_unused_param
);
--
-- Get current client ID and IP address
--
l_client_id := WSGL.GetClientID;
l_ip_address := owa_util.get_cgi_env('REMOTE_ADDR');
--
update_session( l_client_id,
l_ip_address,
l_sso_user_name,
true );
--
-- Write client id to browser as a cookie
-- Open http headers but don't close.
WSGL.StoreClientID( l_client_id, true, false );
--
-- Redirect user to the requested application url
owa_util.redirect_url(l_url_requested);
owa_util.http_header_close;
--
exception
when wsgl.invalid_user then
show_no_user;
when others then
raise;
end sign_on;
--
--***********************************************************************
--
-- Show_No_Access: Displays message to inform user that they do not
-- have access to the application.
--
--***********************************************************************
procedure show_no_access
is
begin
htp.htmlOpen;
htp.headOpen;
htp.title(WSGL.MsgGetText(609,WSGLM.SEC609_ACCESS_DENIED));
htp.headClose;
htp.bodyOpen;
htp.header(2, WSGL.MsgGetText(609,WSGLM.SEC609_ACCESS_DENIED));
htp.header(4, WSGL.MsgGetText(610,WSGLM.SEC610_NO_PERMISSION));
htp.hr;
htp.anchor( curl=>get_cancel_url,
ctext=>WSGL.MsgGetText(613,WSGLM.SEC613_HOME));
htp.bodyClose;
htp.htmlClose;
end show_no_access;
--
--***********************************************************************
--
-- Show_No_User: Displays message to inform user that they do not
-- have a user on the partner application corresponding
-- to their Single-Sign On user.
--
--***********************************************************************
procedure show_no_user is
begin
htp.htmlOpen;
htp.headOpen;
htp.title(WSGL.MsgGetText(609,WSGLM.SEC609_ACCESS_DENIED));
htp.headClose;
htp.bodyOpen;
htp.header(2, WSGL.MsgGetText(609,WSGLM.SEC609_ACCESS_DENIED));
htp.header(4, WSGL.MsgGetText(614,WSGLM.SEC614_NO_USER));
htp.hr;
htp.anchor( curl=>get_cancel_url,
ctext=>WSGL.MsgGetText(613,WSGLM.SEC613_HOME));
htp.bodyClose;
htp.htmlClose;
end show_no_user;
--
--***********************************************************************
--
-- Add_Package_Resp: Adds a respondibility to a package, allowing users
-- with that responsibility to access the application.
-- This is called from the generated application
-- installation script if a value is supplied for
-- preference SECRES.
--
--***********************************************************************
procedure add_package_resp( p_package_name in varchar2,
p_responsibility in varchar2 )
is
l_rows integer := 0;
begin
--
-- Determine whether responsibility exists
--
begin
select count(*)
into l_rows
from wsgsso_resp
where responsibility_name = p_responsibility;
exception when no_data_found then
l_rows := 0;
end;
if l_rows = 0 then
--
-- Responsibility does not exist, so create it
--
dbms_transaction.begin_discrete_transaction;
insert into wsgsso_resp( responsibility_name )
values ( p_responsibility );
commit;
end if;
--
-- Add row to wsgsso_perm table to grant package access
-- to the responsibility, if it does not already exist.
--
begin
select count(*)
into l_rows
from wsgsso_perm
where responsibility_name = p_responsibility
and package_name = p_package_name;
exception when no_data_found then
l_rows := 0;
end;
if l_rows = 0 then
dbms_transaction.begin_discrete_transaction;
insert into wsgsso_perm( responsibility_name, package_name )
values( p_responsibility, p_package_name );
commit;
end if;
end add_package_resp;
--***********************************************************************
--
-- Logoff: Removes the current user's session, logging them off.
-- Then causes a redirect to p_refresh_url after p_delay seconds.
-- The refresh URL is defaulted to be the SSO Cancel URL.
-- In case the device does not support the
-- tag, a link to the cancel URL is
-- included.
--
--***********************************************************************
procedure logoff( p_refresh_url in varchar2 :=
wsgsso_constants.g_cancel_url,
p_delay in varchar2 := '1' )
is
l_client_id wsgsso_sessions.client_id%type;
l_refresh_url varchar2(4000);
begin
--
-- Default the p_refresh_url to be the same as the SSO cancel URL.
--
if p_refresh_url is not null then
l_refresh_url := p_refresh_url;
else
l_refresh_url := get_cancel_url;
end if;
htp.htmlOpen;
htp.headOpen;
htp.title(WSGL.MsgGetText(611,WSGLM.SEC611_LOGGING_OFF));
htp.p('');
htp.headClose;
htp.bodyOpen;
--
-- Get current client ID
--
l_client_id := WSGL.GetClientID;
if l_client_id is not null then
--
-- Delete session for this client
--
dbms_transaction.begin_discrete_transaction;
delete from wsgsso_sessions where client_id = l_client_id;
commit;
end if;
htp.header(2, WSGL.MsgGetText(612,WSGLM.SEC612_LOGGED_OFF));
htp.hr;
htp.anchor( curl=>l_refresh_url,
ctext=>WSGL.MsgGetText(613,WSGLM.SEC613_HOME));
htp.bodyClose;
htp.htmlClose;
end logoff;
--***********************************************************************
--
-- create_User: Creates a user with the specified name and password
--
--***********************************************************************
procedure create_user( p_username in varchar2,
p_password in varchar2 )
is
l_rows integer;
begin
--
-- Determine whether user already exists
--
begin
select count(*)
into l_rows
from wsgsso_user
where upper(user_name) = upper(p_username);
exception when no_data_found then
l_rows := 0;
end;
if l_rows = 0 then
--
-- User does not exist, so create new one
--
dbms_transaction.begin_discrete_transaction;
insert into wsgsso_user( user_name, password )
values (upper(p_username), nvl(p_password, p_username));
commit;
else
--
-- User exists, so update password
--
dbms_transaction.begin_discrete_transaction;
update wsgsso_user set password = nvl(p_password, p_username)
where user_name = p_username;
commit;
end if;
end;
--
end wsgsso;
/
show errors