APEX

My Solution to Generate User Level Links

One of my page item (primary key) has session state protection is set to “Checksum Required - User Level”. So i need to generate link with get_url. This get_url function is perfectly recognize those page items and generating checksum in link for this. For session based, user based or app based everything work better than prepare_url.

But, when i try to generate a url with checksum it is generating for current user. But i want to generate it for target user. There are no parameters to do it so i found a solution to generate it by creating session for target user then attaching the original one then removing the generated session. This way allowed me to generate user specific links e.g. when sending emails etc.

My Problem Is

After i re-attached my original session i'm losing my session values. I have to solve this with the most secure and clean way. I'm open to any opinion please share with me ^_^

You can find my code below →

Function f_get_fullurl (
        p_application        In Varchar2 Default sys_context('APEX$SESSION', 'APP_ID'), -- Default to the current application ID from the APEX session.
        p_user               In Varchar2 Default Null, -- Optional: Use this parameter to specify a username when generating the URL.
        p_page               In Varchar2, -- The page ID for the target APEX page.
        p_session            In Number Default Null, -- Optional: Provide a session ID, or use the current session if NULL.
        p_request            In Varchar2 Default Null, -- Optional: A request string for the page.
        p_debug              In Varchar2 Default Null, -- Optional: Enable debug mode by providing a value.
        p_clear_cache        In Varchar2 Default Null, -- Optional: Specify cache regions to clear when navigating.
        p_items              In Varchar2 Default Null, -- Optional: Comma-separated list of item names to pass to the page.
        p_values             In Varchar2 Default Null, -- Optional: Comma-separated list of item values corresponding to `p_items`.
        p_printer_friendly   In Varchar2 Default Null, -- Optional: Set to enable printer-friendly mode.
        p_trace              In Varchar2 Default Null, -- Optional: Enable trace mode by providing a value.
        p_triggering_element In Varchar2 Default Null, -- Optional: Specify the triggering element for the URL.
        p_plain_url          In Boolean Default True -- Optional: Set to false if a plain URL is required with javascript.
    ) Return Varchar2 Is
        -- Variable declarations
        v_security_id    Number; -- Security group ID for the workspace.
        v_url            Varchar2(4000 Char); -- Final URL to return.
        v_apex_session   Number := sys_context('APEX$SESSION', 'APP_SESSION'); -- Current APEX session ID.
        v_appid          Number := Nv('APP_ID');
        v_pageid         Number := Nv('APP_PAGE_ID');

    Begin
        -- Set the security group ID for the workspace.
        v_security_id := apex_util.find_security_group_id(p_workspace => gv_default_workspacename);
        apex_util.set_security_group_id(p_security_group_id => v_security_id);
        
        If p_user Is Not Null Then
            -- Create a new APEX session for the given application and page.
            apex_session.create_session(
                p_app_id => p_application, 
                p_page_id => p_page,
                p_username => upper(p_user));
        End If;        
        
        -- Generate the URL using APEX's page URL API.
        v_url := apex_page.get_url (
                p_application        => p_application,
                p_page               => p_page,
                p_session            => p_session,
                p_request            => p_request,
                p_debug              => p_debug,
                p_clear_cache        => p_clear_cache,
                p_items              => p_items,
                p_values             => p_values,
                p_printer_friendly   => p_printer_friendly,
                p_trace              => p_trace,       
                p_triggering_element => p_triggering_element,
                p_plain_url          => p_plain_url );
        
        If p_user Is Not Null Then
            -- Delete the newly created session after the URL is generated.
            Declare
                v_sessiontodelete Number := nv('APP_SESSION');
            Begin

                If v_apex_session Is Not Null Then
                                        
                    apex_session.attach(
                        p_app_id     => v_appid,
                        p_page_id    => v_pageid,
                        p_session_id => v_apex_session);
                    
                End If;
                   
                apex_session.delete_session( p_session_id => v_sessiontodelete);
                
            End;
        End If;
        
        -- If the original APEX session exists, prepend the host URL for a fully qualified URL.
        If v_apex_session Is Not Null Then
            -- BPMN Reminders automatically provide full url somehow.
            If v_url not like 'https://%' Then
                v_url := apex_util.host_url || v_url;
            End If;
        End If;
        
        -- Return the generated URL.
        Return v_url;
    End f_get_fullurl;