create or replace package body wsgsec 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 wsgsec_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   wsgsec_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 wsgsec_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   wsgsec_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;
--
--
--  Get_User: Returns user name of current user.
--
function get_user return varchar2
is
begin
   return v_current_user;
end;
--
--
--  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  varchar2(240);
   l_password   varchar2(30);
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    wsgsec_user     u
            ,       wsgsec_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;
--
--
--  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);
   l_username    varchar2(30);
   l_rows        integer := 0;
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
         --
         --  Get user name
         --
         begin
            select user_name
            into   v_current_user
            from   wsgsec_sessions
            where  client_id = l_client_id;
         exception when others then
            v_current_user := null;
         end;
         --
         --  Check that the current user has a responsibility
         --  that allows it to access the specified package name
         --
         begin
            select count(*)
            into   l_rows
            from   wsgsec_userresp ur
            ,      wsgsec_perm     pm
            where  ur.user_name = v_current_user
            and    pm.package_name = p_package_name
            and    ur.responsibility_name = pm.responsibility_name;
         exception when no_data_found then
            l_rows := 0;
         end;
      else
         --
         --  No valid session was found, so display a
         --  logon form.
         --
         call_logon_form;
         return false;
      end if;
      --
      if l_rows != 0 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 display logon form.
      --
      call_logon_form;
      return false;
   end if;
end;
--
--
--  Show_Logon_Form: Renders logon form
--
procedure show_logon_form( p_embedded  in  boolean )
is
begin
   if not p_embedded then
      htp.htmlOpen;
      htp.headOpen;
      htp.title(WSGL.MsgGetText(605,WSGLM.SEC605_LOGON_CAPTION));
      htp.headClose;
      htp.bodyOpen (cattributes=> 'onLoad="return Logon_OnLoad()"');
   end if;

   -- Output JavaScript for handling onLoad event for setting
   -- the focus to the username field upon invocation.

   htp.p ('<SCRIPT><!--');
   htp.p('
function Logon_OnLoad (ctl, index) {
document.Logonform.p_username.focus();
}
');
   htp.p ('//-->');
   htp.p ('</SCRIPT>');

   htp.header( 2, WSGL.MsgGetText(601,WSGLM.SEC601_LOGON_REQUIRED) );
   htp.header( 4, WSGL.MsgGetText(602,WSGLM.SEC602_USERNAME_PASSWORD) );

   htp.formOpen( curl=>'wsgsec.process_logon', ctarget=>'', cattributes => 'NAME="Logonform"' );

   htp.tableOpen;
   htp.tableRowOpen;
   htp.tableData( '<I>'||WSGL.MsgGetText(603,WSGLM.SEC603_USERNAME_PROMPT)||' </I>');
   htp.tableData( htf.formText( cname=>'p_username' ) );
   htp.tableRowClose;
   htp.tableRowOpen;
   htp.tableData( '<I>'||WSGL.MsgGetText(604,WSGLM.SEC604_PASSWORD_PROMPT)||' </I>');
   htp.tableData( htf.formPassword( cname=>'p_password' ) );
   htp.tableRowClose;
   htp.tableRowOpen;
   htp.tableData( htf.formSubmit( cvalue=>WSGL.MsgGetText(605,WSGLM.SEC605_LOGON_CAPTION) ) );
   htp.tableRowClose;
   htp.tableClose;
   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 )
is
   l_client_id      varchar2(240);
   l_rows           integer := 0;
   l_success        boolean := false;
   l_current_ip     varchar2(30);
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   wsgsec_user
      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 wsgsec_sessions
         set user_name = p_username
         where client_id = l_client_id;
         commit;
         l_success := true;
      else
         --
         --  Current client ID is not valid so create a new one
         --
         l_client_id := generate_client_id;
         --
         --  Write to browser as a cookie
         --
         WSGL.StoreClientID( l_client_id );
         --
         --  Get current IP address
         --
         l_current_ip := owa_util.get_cgi_env('REMOTE_ADDR');
         --
         --  Create new session in session table
         --
         dbms_transaction.begin_discrete_transaction;
         insert into wsgsec_sessions( client_id, user_name, ip_address, expires )
         values( l_client_id, p_username, l_current_ip, (sysdate + (c_session_duration_hours / 24)) );
         commit;
         l_success := true;
      end if;
   elsif l_client_id is not null then
      --
      --  Invalid logon so delete the session for the current client.
      --
      dbms_transaction.begin_discrete_transaction;
      delete from wsgsec_sessions where client_id = l_client_id;
      commit;
   end if;
   --
   --  Output HTML to indicate success or failure
   --
   htp.htmlOpen;
   if l_success then
      htp.bodyOpen;
      htp.header(2, WSGL.MsgGetText(606,WSGLM.SEC606_LOGON_SUCCESS));
      htp.header(4, WSGL.MsgGetText(607,WSGLM.SEC607_NAVIGATE_BACK));
      htp.script( '
  <!-- 
  //opener.location.reload(true);
  opener.location = opener.location;
  //-->' );
      htp.script( '
  <!-- 
  self.close();
  //-->' );
   else
      htp.bodyOpen (cattributes=> 'onLoad="return Logon_OnLoad()"');
      htp.header(2, WSGL.MsgGetText(608,WSGLM.SEC608_INVALID_LOGON));
      htp.hr;
      show_logon_form( true );
   end if;
   htp.bodyClose;
   htp.htmlClose;  
end;
--
--
--  Show_No_Access: Displays message to inform user that they do not
--                  have access to the application. Provides a link
--                  to the logon form.
--
procedure show_no_access
is
begin
   htp.htmlOpen;
   htp.headOpen;
   htp.title('Access Denied');
   htp.headClose;
   htp.bodyOpen;
   htp.script('
function show_logon() {
   frmLogon = open("wsgsec.show_logon_form","winLogon","scrollbars=no,resizable=yes,width=400,height=300");
   if (frmLogon.opener == null) {
       frmLogon.opener = self;
   };
};');
   htp.header(2, WSGL.MsgGetText(609,WSGLM.SEC609_ACCESS_DENIED));
   htp.header(4, WSGL.MsgGetText(610,WSGLM.SEC610_NO_PERMISSION));
   htp.hr;
   htp.p('<SCRIPT>');
   htp.p('<!--');
   --htp.p('document.write("' || htf.anchor2( curl=>'javascript:show_logon()', ctext=>'Logon') || '");');
   htp.p('document.write(''<A HREF="javascript:show_logon()">Logon</A>'');');
   htp.p('//-->');
   htp.p('</SCRIPT>');
   htp.p('<NOSCRIPT>');
   htp.anchor2( curl=>'wsgsec.show_logon_form', ctext=>'Logon');
   htp.p('</NOSCRIPT>');
   htp.bodyClose;
   htp.htmlClose;
end;
--
--
--  Call_Logon_Form: Outputs javascript to popup logon form in new window,
--                   or include logon form in current window if javascript
--                   is not available.
--
procedure call_logon_form
is
begin
   htp.htmlOpen;
   htp.bodyOpen;
--
   htp.script('
<!--
frmLogon = open("wsgsec.show_logon_form","winLogon","scrollbars=no,resizable=yes,width=400,height=300");
if (frmLogon.opener == null) {
    frmLogon.opener = self;
};
//-->' );
   htp.p('<NOSCRIPT>');
   show_logon_form( true );
   htp.p('</NOSCRIPT>');
--
   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
   l_rows   integer := 0;
begin
   --
   --  Determine whether responsibility exists
   --
   begin
      select  count(*)
      into    l_rows
      from    wsgsec_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 wsgsec_resp( responsibility_name ) values ( p_responsibility );
      commit;
   end if;
   --
   --  Add row to wsgsec_perm table to grant package access
   --  to the responsibility, if it does not already exist.
   --
   begin
      select  count(*)
      into    l_rows
      from    wsgsec_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 wsgsec_perm( responsibility_name, package_name )
      values( p_responsibility, p_package_name );
      commit;
   end if;
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 wsgsec_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;
--
--
--  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   wsgsec_user
      where  user_name = 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 wsgsec_user( user_name, password ) values (p_username, nvl(p_password, p_username));
      commit;
   else
      --
      --  User exists, so update password
      --
      dbms_transaction.begin_discrete_transaction;
      update wsgsec_user set password = nvl(p_password, p_username) where user_name = p_username;
      commit;
   end if;
end;
   
end wsgsec;
/
show errors