APEX

Use Case

First of all i want to prevent multiple login with same user. So i decided to drop multiple sessions for same user and keep the latest one. Basically when you login from your phone, then login from your computer; your phone's session will be drop and you would kicked out from system for phone and you can keep going with computer.

I already have users table with id primary key. But in some cases usernames could be same. For example a ‘ABC’ person working in a company and username is ABC. Someone from other branch or company could have exact same person. So basically i have a unique constraint like username + branch . But in this case if i drop sessions just by checking username that would be wrong.

That's why i tried to use this tenant_id thing as described below → https://docs.oracle.com/en/database/oracle/apex/24.2/aeapi/SET_TENANT_ID.html

So i set_tenant_id with user_id then i do this in post authentication procedure →

Procedure p_check_multiple_sessions(
        p_user_id Varchar2 Default v('APP_TENANT_ID')
    ) As
        Cursor c_sessions Is
            Select q1.apex_session_id,q2.application_id
              From apex_workspace_sessions q1,
                   (
                       Select apex_session_id,
                              application_id,
                              Max(view_date) last_access,
                              Min(view_date) first_access,
                              Count(*)       requests
                         From apex_workspace_activity_log
                        Group By apex_session_id,
                                 application_id
                   )                       q2
             Where q1.apex_session_id = q2.apex_session_id
               And q1.session_tenant_id = upper(p_user_id)
               And q2.application_id In (
                Select q2.application_id
                  From apex_workspace_sessions q1, (
                    Select apex_session_id,
                           application_id, Max(view_date) last_access,
                           Min(view_date) first_access, Count(*) requests
                      From apex_workspace_activity_log
                     Group By apex_session_id,
                              application_id
                )                       q2
                 Where q1.apex_session_id = q2.apex_session_id
                   And q1.session_tenant_id = upper(p_user_id)
                 Group By q2.application_id
                Having Count(*) >= 1);
    Begin        

        For r_session In c_sessions Loop
            apex_authentication.remove_persistent_auth ( APEX_CUSTOM_AUTH.GET_USER );
            Exit;
        End Loop;
    End;

As you can see i can duplicated sessions by looking the user_id that i have instead of using username. But because of persistent auth thing this procedure not working as intended

My Question Is

Is it makes sense to use tenant_id like this ?

What should i do to remove persistent auth only for that user id ?
Currently it looks like all persistent auth feature of oracle working with username only.

Is there any other way to do this more clean ?

Note: I'm using custom authentication.

My alternative option

I can user user_id as username to make it uniq. In app i should convert APP_USER usage with this →

select username from users where id = v('APP_USER');

That could also an option maybe ?