Capture Log (Login, Logout, Session, System) Details in Oracle Apex 4.2

Objective:

This post will explain you about, how to capture use login, logout and session out time in Oracle APEX applications using security attributes.

Section 1: 

To Capture User Login Details:

User Log Reports provides the detailed information about the users' login details along with their history. These reports are more accurate and also provides the details of the user, their logon time, logoff time, the computer from which they logged on, the domain controller they reported, etc., along with their logon history. It also provides  the details of Currently Logged On Users and Frequently Logged-on Users.

The following information can be captured in the Database.

1. Login Date
2. Login Time
3. Session Id
4. System IP Address
5. Host Name
6. User Name

Step 1: Table Creation.

 <<table_name>> (column_names)   {Based on the column Requirement}

Step 2: Creation of Page Hidden Items.

Create Page Hidden Items P0_SESSION_ID, P0_USER_ID for getting Session id as well as User id.

P0_SESSION_ID:

SELECT v ('session')
   FROM dual;

Step 3: Creation of Insertion Process in the User Login Page.

On Click of Login Button or Enter Button in Login Page the following process has to be created.

Insertion Process:

DECLARE
  ln_session_id     NUMBER;
  lv_ip_address     VARCHAR2 (30);
  lv_login_date     VARCHAR2 (100);
  lv_login_time     VARCHAR2 (50);
  lv_user_role      VARCHAR2 (50);
  lv_current_schema VARCHAR2 (50);
  lv_host           VARCHAR2 (50);
BEGIN

  -- Select Session id,Login Date,Login Time, Ip Address, Host name and Current Schema

  BEGIN
    SELECT v ('session'),
      TO_CHAR (SYSDATE, 'dd-Mon-yyyy'),
      TO_CHAR (SYSDATE, 'hh12:mi:ss PM'),
      sys_context ('USERENV', 'IP_ADDRESS'),
      sys_context ('USERENV', 'HOST'),
      sys_context ('USERENV', 'CURRENT_SCHEMA'),
    INTO ln_session_id,
      lv_login_date,
      lv_login_time,
      lv_ip_address,
      lv_host,
      lv_current_schema
    FROM dual;
  EXCEPTION
  WHEN OTHERS THEN
    raise_application_error (-20001, 'Error on Selecting Session Id' || sqlerrm );
  END;

  -- Select the User Role

  BEGIN
    SELECT user_role
    INTO lv_user_role
    FROM homes_fnd_users
    WHERE user_id = :p0_user_id;
  EXCEPTION
  WHEN OTHERS THEN
    raise_application_error (-2007, 'Error on Selecting User Name' || sqlerrm );
  END;

  -- Insert Data to Corresponding Table

  BEGIN
    INSERT
    INTO homes_inv_user_log_details
      (
        session_id,
        user_role,
        user_name,
        CURRENT_SCHEMA,
        host,
        log_id,
        login_date,
        login_time,
        ip_address
      )
      VALUES
      (
        ln_session_id,
        lv_user_role,
        :p0_user_id,
        lv_current_schema,
        lv_host,
        homes_log_details.nextval,
        lv_login_date,
        lv_login_time,
        lv_ip_address
      );
  EXCEPTION
  WHEN OTHERS THEN
    raise_application_error (-20006, 'Error on While Inserting records' || sqlerrm );
  END;
END;

Step 4:  Interactive Report Creation. Create a new page with Interactive Report that shows all the Login Details.

Output:

Section 2:

To Capture User Logout Details:

The following information will be captured in the Database.

1. Logout Date
2. Logout Time
3. Session Id
4. System IP Address
5. Host Name
6. User Name

Step 1: Creation of New Image.

Upload designed or downloaded images to Apex Workspace.

Step 2: Button Creation

In Edit Page remove Navigation Bar Entry Label, instead create a new Button in the Global Page (Zeroth Page) in your Apex Application.

       1. Now create a new Region (Template=> No Template & Display Point => Page Template Region Position 8)
       2. Create new Buttons (which is used in application) in this Region only. This Button will be displayed throughout the application.
       3. Change the button style => Images.
       4. Call the image from Apex Workspace,

        #WORKSPACE_IMAGES#<<workspaceimage_name>>

Region Creation:

Button Creation:

Navigation Bar Logout:

Set Navigation Bar Logout Condition as NEVER.

Step 3:  Creation of Dynamic Action

Since it is not possible to create a Process in Global Page, so create a dynamic action.

1. Execute PLSQL Code:

Create the following action On Click of Logout Button.

DECLARE
  lv_logout_date VARCHAR2 (100);
  lv_logout_time VARCHAR2 (100);
BEGIN
  -- Select Logout Date and Logout Time
  BEGIN
    SELECT to_char (SYSDATE, 'dd-Mon-yyyy'),
      to_char (SYSDATE, 'hh12:mi:ss PM')
    INTO lv_logout_date,
      lv_logout_time
    FROM dual;
  exception
  WHEN others THEN
    raise_application_error (-20001, 'Error on Selecting Logout Date & Time' || sqlerrm );
  END;

  --Update Logout Date and Logout Time
  BEGIN
    UPDATE homes_inv_user_log_details
    SET logout_date  = lv_logout_date,
      logout_time    = lv_logout_time
    WHERE session_id = :p0_session_id;
  exception
  WHEN others THEN
    raise_application_error (-20003, 'Error on Updating Records' || sqlerrm );
  END;

END;
/

2. Execute Java script Code:

        window.location='&LOGOUT_URL.'

Step 4: Create a new page with Interactive Report that shows all the Login Details.

Output:

Section 3:

Session Out Time Capture: 

Paste the below code into SECURITY ATTRIBUTES = > DATABASE SESSION.

Execute PLSQL Code:

BEGIN

  --Select Session Out Date & Session Out Time

  BEGIN
    SELECT TO_CHAR (SYSDATE, 'dd-Mon-yyyy'),
      TO_CHAR (SYSDATE, 'hh12:mi:ss PM')
    INTO lv_session_date,
      lv_session_time
    FROM dual;
  EXCEPTION
  WHEN OTHERS THEN
    raise_application_error (-20001, 'Error on Selecting Logout Date' || sqlerrm );
  END;

  -- Update Session Out Date & Session Out Time When Session is Out

  BEGIN
    UPDATE homes_inv_user_log_details
    SET session_date = lv_session_date,
      session_time   = lv_session_time
    WHERE session_id = :p0_session_id;
  EXCEPTION
  WHEN OTHERS THEN
    raise_application_error (-20003, 'Error on Updating Records' || sqlerrm );
  END;

END;

Output:



Comments

Popular posts from this blog

Highlight the cell of Interactive Report based on Search Criteria in Oracle APEX

Querying Data From Flat Files in Oracle

Number of Tables by The Number of Rows in Oracle Database

Save Selected Interactive Grid Records into a Collection - Oracle APEX

Highlight Data That Meet Certain Criteria in Oracle APEX Report

Return a Validation Message using JavaScript if the Oracle Application Express Item is Empty

Remove X (Close Mark) from Oracle APEX Modal Dialog

Display User Rating Icons Dynamically in Oracle APEX Report

Printing Page Numbers in RTF Template [Oracle BI Publisher]

Find Where Specific Table or View is Used in Oracle Database