create or replace package body wsgsr is
--
--
c_session_duration_hours constant number := 3;
v_current_user varchar2(30);
--
-- 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 ) return boolean
is
l_stored_ip varchar2(30);
l_current_ip varchar2(30);
l_check_ok boolean;
l_expires date;
begin
if p_client_id is not null then
--
-- Get IP address of client
--
l_current_ip := owa_util.get_cgi_env('REMOTE_ADDR');
l_check_ok := true;
--
-- Check wsgsr_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 wsgsr_sessions
where client_id = p_client_id
and ip_address = l_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 wsgsr_sessions where client_id = p_client_id;
commit;
end if;
--
return l_check_ok;
end;
--
--
-- Generate_Client_Id: Generate a unique identifier for a new session.
--
function generate_client_id return varchar2
is
l_client_id varchar2(240);
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 wsgsr_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;
--
--
procedure create_session ( p_username in varchar2 )
is
l_client_id varchar2(240);
l_current_ip varchar2(30);
begin
l_client_id := generate_client_id;
--
-- Get current IP address
--
l_current_ip := owa_util.get_cgi_env('REMOTE_ADDR');
--
WSGL.StoreClientID( l_client_id );
--
-- Create new session in session table
--
dbms_transaction.begin_discrete_transaction;
insert into wsgsr_sessions( client_id, user_name, ip_address, expires )
values( l_client_id, p_username, l_current_ip, (sysdate + (c_session_duration_hours / 24)) );
commit;
end;
--
-- Get_User: Returns user name of current user.
--
function get_user return varchar2
is
begin
return v_current_user;
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 varchar2(240);
begin
--
-- Get the current client ID
--
l_client_id := WSGL.GetClientID;
if l_client_id is not null then
--
-- Call check_session to ensure current session
-- is valid
--
if check_session( l_client_id ) then
begin
select user_name
into v_current_user
from wsgsr_sessions
where client_id = l_client_id;
exception when others then
v_current_user := null;
end;
return true;
else
--
-- No valid session was found, so display a
-- logon form.
--
show_welcome_form;
return false;
end if;
else
--
-- No current client ID, so display logon form.
--
show_welcome_form;
return false;
end if;
end;
--
--
-- Show_Welcome_Form: Renders welcome form
--
procedure show_welcome_form( p_embedded in boolean default false
, p_username in varchar2 default null
, p_password in varchar2 default null
, p_confirm in varchar2 default null
, p_title in varchar2 default null
, p_name1 in varchar2 default null
, p_name2 in varchar2 default null
, p_sex in varchar2 default null
, p_email in varchar2 default null
, p_address1 in varchar2 default null
, p_address2 in varchar2 default null
, p_address3 in varchar2 default null
, p_address4 in varchar2 default null
, p_postcode in varchar2 default null
, p_telephone in varchar2 default null
, p_urllink in varchar2 default null)
is
l_mr varchar2(1);
l_ms varchar2(1);
l_mrs varchar2(1);
l_dr varchar2(1);
l_miss varchar2(1);
l_m varchar2(1);
l_f varchar2(1);
l_port_number varchar2(10) := ltrim(rtrim(owa_util.get_cgi_env('SERVER_PORT')));
l_url_store varchar2(1000);
begin
--
-- Set up variables for default option selection
--
if p_title = 'Mr' then
l_mr := 'Y';
end if;
if p_title = 'Ms' then
l_ms := 'Y';
end if;
if p_title = 'Mrs' then
l_mrs := 'Y';
end if;
if p_title = 'Miss' then
l_miss := 'Y';
end if;
if p_title = 'Dr' then
l_dr := 'Y';
end if;
if p_sex = 'M' then
l_m := 'Y';
end if;
if p_sex = 'F' then
l_f := 'Y';
end if;
if p_urllink is null then
l_url_store := owa_util.get_cgi_env('SCRIPT_NAME') || owa_util.get_cgi_env('PATH_INFO');
else
l_url_store := p_urllink;
end if;
if not p_embedded then
htp.htmlOpen;
htp.headOpen;
htp.title(WSGL.MsgGetText(605,WSGLM.SEC605_LOGON_CAPTION));
htp.headClose;
htp.bodyOpen;
end if;
htp.header( 2, 'Please log in' );
htp.p('If this is the first time you have accessed this site, please enter your details below, along
with a username and password, to register. You will then be able to use the username and password to
log on to this site in future.');
htp.formOpen( curl=>'wsgsr.process_logon', ctarget=>'' );
htp.tableOpen;
htp.tableRowOpen;
htp.tableData( ''||WSGL.MsgGetText(603,WSGLM.SEC603_USERNAME_PROMPT)||' ', calign=>'RIGHT');
htp.tableData( htf.formText( cname=>'p_username', cvalue=>p_username ) );
htp.tableRowClose;
htp.tableRowOpen;
htp.tableData( ''||WSGL.MsgGetText(604,WSGLM.SEC604_PASSWORD_PROMPT)||' ', calign=>'RIGHT');
htp.tableData( htf.formPassword( cname=>'p_password', cvalue=>p_password ) );
htp.tableRowClose;
htp.tableRowOpen;
htp.tableData( 'Confirm Password:', calign=>'RIGHT');
htp.tableData( htf.formPassword( cname=>'p_confirm', cvalue=>p_confirm ) );
htp.tableRowClose;
htp.tableRowOpen;
htp.hr;
htp.tableRowClose;
htp.tableRowOpen;
htp.tableData( 'Title:', calign=>'RIGHT');
htp.tableData( htf.formSelectOpen( cname=>'p_title', nsize=>1) ||
htf.formSelectOption('Mr', cselected=>l_mr) ||
htf.formSelectOption('Ms', cselected=>l_ms) ||
htf.formSelectOption('Mrs', cselected=>l_mrs) ||
htf.formSelectOption('Miss', cselected=>l_miss)||
htf.formSelectOption('Dr', cselected=>l_dr) ||
htf.formSelectClose );
htp.tableRowClose;
htp.tableRowOpen;
htp.tableData( 'First Name:', calign=>'RIGHT');
htp.tableData( htf.formText( cname=>'p_name1', cvalue=>p_name1, csize=>20, cmaxlength=>30 ) );
htp.tableRowClose;
htp.tableRowOpen;
htp.tableData( 'Second Name:', calign=>'RIGHT');
htp.tableData( htf.formText( cname=>'p_name2', cvalue=>p_name2, csize=>20, cmaxlength=>30 ) );
htp.tableRowClose;
htp.tableRowOpen;
htp.tableData( 'Sex:', calign=>'RIGHT');
htp.tableData( htf.formSelectOpen( cname=>'p_sex', nsize=>1 ) ||
htf.formSelectOption('M', cselected=>l_m) ||
htf.formSelectOption('F', cselected=>l_f) ||
htf.formSelectClose );
htp.tableRowClose;
htp.tableRowOpen;
htp.tableData( 'E-Mail Address:', calign=>'RIGHT');
htp.tableData( htf.formText( cname=>'p_email', cvalue=>p_email, csize=>30, cmaxlength=>30 ) );
htp.tableRowClose;
htp.tableRowOpen;
htp.tableData( 'Address (line 1):', calign=>'RIGHT');
htp.tableData( htf.formText( cname=>'p_address1', cvalue=>p_address1, csize=>30, cmaxlength=>30 ) );
htp.tableRowClose;
htp.tableRowOpen;
htp.tableData( 'Address (line 2):', calign=>'RIGHT');
htp.tableData( htf.formText( cname=>'p_address2', cvalue=>p_address2, csize=>30, cmaxlength=>30 ) );
htp.tableRowClose;
htp.tableRowOpen;
htp.tableData( 'Address (line 3):', calign=>'RIGHT');
htp.tableData( htf.formText( cname=>'p_address3', cvalue=>p_address3, csize=>30, cmaxlength=>30 ) );
htp.tableRowClose;
htp.tableRowOpen;
htp.tableData( 'Address (line 4):', calign=>'RIGHT');
htp.tableData( htf.formText( cname=>'p_address4', cvalue=>p_address4, csize=>30, cmaxlength=>30 ) );
htp.tableRowClose;
htp.tableRowOpen;
htp.tableData( 'Postal/ZIP code:', calign=>'RIGHT');
htp.tableData( htf.formText( cname=>'p_postcode', cvalue=>p_postcode, csize=>10, cmaxlength=>10 ) );
htp.tableRowClose;
htp.tableRowOpen;
htp.tableData( 'Telephone:', calign=>'RIGHT');
htp.tableData( htf.formText( cname=>'p_telephone', cvalue=>p_telephone, csize=>30, cmaxlength=>30 ) );
htp.tableRowClose;
htp.tableRowOpen;
htp.tableData( htf.formSubmit( cvalue=>WSGL.MsgGetText(605,WSGLM.SEC605_LOGON_CAPTION) ) );
htp.tableRowClose;
htp.tableClose;
htp.formHidden( cname=>'p_urllink', cvalue=>l_url_store );
htp.formClose;
if not p_embedded then
htp.bodyClose;
htp.htmlClose;
end if;
end;
--
--
-- Process_Logon: Accepts submitted logon form and logs user on. If logon
-- fails then notifies user.
--
procedure process_logon( p_username in varchar2
, p_password in varchar2
, p_confirm in varchar2
, p_title in varchar2
, p_name1 in varchar2
, p_name2 in varchar2
, p_sex in varchar2
, p_email in varchar2
, p_address1 in varchar2
, p_address2 in varchar2
, p_address3 in varchar2
, p_address4 in varchar2
, p_postcode in varchar2
, p_telephone in varchar2
, p_urllink in varchar2 )
is
l_client_id varchar2(240);
l_rows integer := 0;
l_success boolean := false;
l_newuser boolean := false;
l_current_ip varchar2(30);
l_missing_data boolean := false;
l_pw_confirm_fail boolean := false;
begin
--
-- Check that user exists in user table with the specified
-- username and password. l_rows will be non-zero if this
-- is the case.
--
begin
select count(*)
into l_rows
from wsgsr_userdata
where user_name = p_username
and password = p_password;
exception when no_data_found then
l_rows := 0;
end;
--
-- Get current client ID
--
l_client_id := WSGL.GetClientID;
--
-- If username/password valid
--
if l_rows != 0 then
--
-- Check that current client ID is valid
--
if l_client_id is not null and check_session( l_client_id ) then
--
-- Update the session table for this client with
-- the new user
--
dbms_transaction.begin_discrete_transaction;
update wsgsr_sessions
set user_name = p_username
where client_id = l_client_id;
commit;
l_success := true;
else
create_session( p_username );
l_success := true;
end if;
else
--
-- Logon failed. This is either because an incorrect password was typed, or
-- because a new user is registering. Check which.
--
begin
select count(*)
into l_rows
from wsgsr_userdata
where user_name = p_username;
exception when no_data_found then
l_rows := 0;
end;
--
-- If l_rows is 0, this is a new user since the username
-- does not exist in the user table. Otherwise, this is
-- either a failed logon, or a new user trying to use an
-- existing username.
--
if l_rows = 0 then
if p_password != p_confirm then
l_pw_confirm_fail := true;
else
begin
dbms_transaction.begin_discrete_transaction;
insert into wsgsr_userdata( USER_NAME ,
PASSWORD ,
TITLE ,
NAME1 ,
NAME2 ,
SEX ,
EMAIL ,
ADDRESS1 ,
ADDRESS2 ,
ADDRESS3 ,
ADDRESS4 ,
POSTCODE ,
TELEPHONE
) values (
p_username,
p_password,
p_title,
p_name1,
p_name2,
p_sex,
p_email,
p_address1,
p_address2,
p_address3,
p_address4,
p_postcode,
p_telephone
);
commit;
exception when others then
l_missing_data := true;
end;
if not l_missing_data then
create_session( p_username );
l_success := true;
end if;
end if;
l_newuser := true;
else
--
-- If any data was entered, assume that this was a new user
-- trying to use an existing username.
--
if ( p_name1 is not null or p_name2 is not null or p_email is not null ) then
l_newuser := true;
l_success := false;
else
l_newuser := false;
l_success := false;
end if;
end if;
end if;
--
-- Output HTML to indicate success or failure
--
htp.htmlOpen;
htp.bodyOpen;
if not l_success then
htp.header(4, 'Sorry, the logon was not successful');
if l_newuser then
if l_missing_data then
htp.p('Please enter all details on the registration form.');
elsif l_pw_confirm_fail then
htp.p('The passwords entered in the Password and Confirm Password fields do not match');
else
htp.p('The username you entered has already been registered. Please choose another.');
end if;
else
htp.p('The username and password entered were not recognised');
end if;
htp.hr;
show_welcome_form( p_embedded=>true,
p_username=>p_username,
p_password=>p_password,
p_title=>p_title,
p_name1=>p_name1,
p_name2=>p_name2,
p_sex=>p_sex,
p_email=>p_email,
p_address1=>p_address1,
p_address2=>p_address2,
p_address3=>p_address3,
p_address4=>p_address4,
p_postcode=>p_postcode,
p_telephone=>p_telephone,
p_urllink=>p_urllink );
else
htp.script('window.location = "' || p_urllink || '";');
htp.p('');
end if;
htp.bodyClose;
htp.htmlClose;
end;
--
--
--
-- 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
begin
null;
end;
--
--
-- Logoff: Removes the current user's session, logging them off. Outputs
-- HTML message when complete.
--
procedure logoff
is
l_client_id varchar2(240);
begin
htp.htmlOpen;
htp.headOpen;
htp.title(WSGL.MsgGetText(611,WSGLM.SEC611_LOGGING_OFF));
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 wsgsr_sessions where client_id = l_client_id;
commit;
end if;
htp.header(2, WSGL.MsgGetText(612,WSGLM.SEC612_LOGGED_OFF));
htp.bodyClose;
htp.htmlClose;
end;
--
--
end wsgsr;
/
show errors