Introduction:
This document explains the implementation of a Login and Logout Audit mechanism in Oracle APEX. The objective of this solution is to capture and track user activity within the application, specifically focusing on login and logout events.
The audit captures key details such as user ID, employee information, session ID, timestamps (login and logout), IP address, host details, and application details
Why We Need to Do This?
Implementing a login and logout audit in Oracle APEX is essential for ensuring both security and accountability within the application.
Without an audit mechanism, it becomes difficult to track who accessed the system, when they logged in or out, and whether their sessions were terminated properly.
How Do We Solve It?
STEP 1: Run the below code
CREATE TABLE APEX_USERLOG_AUDIT
(sr_no NUMBER GENERATED Always as identity PRIMARY KEY,
user_name VARCHAR2(100),
session_id VARCHAR2(100),
login_date VARCHAR2(30),
login_time VARCHAR2(30),
login_remarks VARCHAR2(2000),
logout_date VARCHAR2(30),
logout_time VARCHAR2(30),
logout_remarks VARCHAR2(2000),
appl_no NUMBER(10),
appl_name VARCHAR2(100),
ip_address VARCHAR2(25),
asset_host_details VARCHAR2(100)
) ;
create or replace package pkg_apex_login_audit
is
PROCEDURE sp_user_logout(p_remark varchar2 default ‘ Logged out by USER. ‘) ;
PROCEDURE sp_user_logout_auto ;
PROCEDURE Sp_user_session_audit;
function fn_apex_app_name return varchar2 result_cache;
end pkg_apex_login_audit;
/
create or replace package body pkg_apex_login_audit
is
PROCEDURE sp_user_logout(p_remark varchar2 )
IS
BEGIN
update APEX_USERLOG_AUDIT
set logout_date =sysdate,
logout_time = systimestamp,
logout_remarks = p_remark –‘ Logout by USER ‘
where SESSION_ID= V(‘APP_SESSION’);
COMMIT;
exception
when others then null;
end sp_user_logout;
PROCEDURE sp_user_logout_auto
IS
BEGIN
update APEX_USERLOG_AUDIT
set logout_date =sysdate,
logout_time = systimestamp,
logout_remarks = ‘ Logged out by system ‘
where SESSION_ID not in (
SELECT APEX_SESSION_ID
FROM apex_workspace_sessions );
COMMIT;
exception
when others then null;
end sp_user_logout_auto;
PROCEDURE sp_user_session_audit
IS
BEGIN
INSERT INTO APEX_USERLOG_AUDIT (
user_name, session_id, login_date, login_time, login_remarks,
appl_no, appl_name, ip_address, asset_host_details
) VALUES
(
V(‘APP_USER’),
V(‘APP_SESSION’),sysdate,SYSTIMESTAMP,’Login Remarks’,
V(‘APP_ID’),pkg_apex_login_audit.fn_apex_app_name(), OWA_UTIL.get_cgi_env(‘REMOTE_ADDR’),
null
);
COMMIT;
end sp_user_session_audit;
function fn_apex_app_name return varchar2 result_cache
is
lv_result varchar2(100);
BEGIN
Select application_name into lv_result from apex_applications where APPLICATION_ID =V(‘APP_ID’);
return lv_result;
exception
when others then
return null;
end fn_apex_app_name;
end pkg_apex_login_audit;
/
STEP 2: In APEX Application
Go to Page 0 and Create Page Item (P0_ACTIVE_SESSION).
STEP 3: Create Dynamic Action,
Name : User Login in Info
Event :On Page Load
Server-Side Condition : Item is null (P0_ACTIVE_SESSION)
Plsql Code:-
/* This is to get user login details*/
pkg_apex_login_audit.Sp_user_session_audit;
:P0_ACTIVE_SESSION :=’Y’;
Items to Return : P0_ACTIVE_SESSION
Step 4: /* To get User logout detail*/
Create a new page(2).
Go to Page : Prerendering create a process.
Name : LogoutProcess
Plsql Code :-
pkg_apex_login_audit.sp_user_logout;
apex_util.redirect_url(
p_url => ‘login?session=’||:APP_SESSION||’9999’);
Step 5:
Go to Shared Components -> Navigation Bar List
-> Select Navigation Bar
–>Sign-out
and Change the Target type from URL to Page in this Application
and select the page id(2).
Conclusion:
The implementation of a Login and Logout Audit in Oracle APEX provides a reliable mechanism to monitor and secure user activity within the application.
By capturing essential session details such as user credentials, timestamps, IP address, and host information, the system ensures transparency, accountability, and compliance with organizational and regulatory requirements.
Output:
After Login:
After Logout :
