How to logout sessions in oracle APEX?

Question

I'm trying to avoid users to login in different computers.

I want to create a Login view, if the user is already logged and you try to login with that user, the app will display a message "user already logged" and have the option of closing the open session with a link or button.

EDIT : Apex Version 5.1.1.00.08, no possibility to updating this version.

I've tried multiple ways to close the session but none have worked so far.

FIRST ACTION :

Create the url to log out.

I'm taking the APEX_SESSION_ID from the table apex_workspace_sessions

http://xxxxxxxx.com/apex/apex_authentication.logout?p_app_id=100&p_session_id=APEX_SESSION_ID

But when I open the link it doesn't log out the user with that APEX_SESSION_ID

SECOND ACTION :

I tried to create a function to log out the session extracting APEX_SESSION_ID from the table apex_workspace_sessions but it's not working either by generating this error :

ORA-06550: line 2, column 11: PLS-00328: A subprogram body must be defined for the forward declaration of DELETE_SESSION. ORA-06550: line 6, column 16: PLS-00302: component 'DELETE_SESSION' must be declared ORA-06550: line 6, column 3: PL/SQL: Statement ignored

CODE:

declare 

procedure delete_session (p_session_id in number default wwv_flow.g_instance );

begin
    apex_session.delete_session (
    p_session_id => APEX_SESSION_ID );
end;

the p_session_id in the image is APEX_SESSION_ID that already exists in the table apex_workspace_sessions

THIRD ACTION :

I tried to run the query

DELETE FROM APEX_050000.wwv_flow_sessions$ WHERE ID = APEX_SESSION_ID;

displaying the error : ORA-00942: table or view does not exist

and the query

DELETE FROM apex_workspace_sessions WHERE APEX_SESSION_ID = APEX_SESSION_ID;

displaying the error : ORA-01031: insufficient privileges

FOURTH ACTION :

I tried to use the LOGOUT PROCEDURE from the official oracle docs but didn't work either

apex_authentication.logout(APEX_SESSION_ID, :APP_ID);

no error or anything to display.

Does anyone knows how to log out a user from another computer ?

Thanks

Sometimes understanding why someone is trying to do something can help with picking the correct implementation. Why are you trying to do this? Why does it matter if they log in on different computers or browsers? — Dan McGhan
I dont want to have multiple sessions for one single user logged in different computers, just one session for one user, kind of like web whastapp, security issues, thanks @DanMcGhan — user11082029

Dan McGhan Dan McGhan · Accepted Answer · 2019-11-02T15:31:15

Here's an example that uses a custom table.

Create the table

create table most_recent_logins (
  id         number generated by default on null as identity  
             constraint most_recent_logins_id_pk primary key,
  username   varchar2(255) not null,
  app_id     number not null,
  session_id number not null
             constraint most_recent_log_session_id_unq unique,
  login_date date not null,
  constraint most_recent_log_user_app_unq unique (username, app_id)
);

Create post-auth process

Go to Shared Components > Authentication Schemes and select the current scheme. Put the following in the PL/SQL code field:

procedure post_auth
is

  cursor mrl_cur
  is
    select *
    from most_recent_logins
    where app_id = :app_id
      and username = :app_user
    for update;

  mrl_rec mrl_cur%rowtype;

begin

  open mrl_cur;
  fetch mrl_cur into mrl_rec;

  if mrl_cur%notfound
  then
    insert into most_recent_logins (
      username,
      app_id,
      session_id,
      login_date
    ) values (
      :app_user,
      :app_id,
      :app_session,
      sysdate
    );
  else
    update most_recent_logins
    set session_id = :app_session,
      login_date = sysdate
    where current of mrl_cur;
  end if;

  close mrl_cur;

end;

Create an application process

Go to Shared Components > Application Process and click Create >. Set Name to Check most recent login, then click Next >. Enter the following code in PL/SQL code and click Next >:

declare

  l_mr_session_id number;

begin

  select session_id
  into l_mr_session_id
  from most_recent_logins
  where username = :app_user
    and app_id = :app_id;

  if l_mr_session_id != :app_session
  then
    apex_util.redirect_url('f?p=' || :app_id || ':9999');
  end if;

end;

Configure the condition so that the process doesn't run on the login page, then click Create Process.

That should do it.

How to logout sessions in oracle APEX?

2 Answers