APEX developer account check & session

S

Sometimes I need to check if user is a developer or not. So I created simple function and map it to APEX authorization scheme, which I use on specific pages, regions or components. With this function I can easily allow access only to current APEX developers without any maintenance.

You can find recent version (and more) on my Github Kvido project in packages/apex.sql.

    FUNCTION is_developer (
        in_username         VARCHAR2        := NULL
    )
    RETURN BOOLEAN AS
        valid               VARCHAR2(1);
    BEGIN
        SELECT 'Y' INTO valid
        FROM apex_workspace_developers d
        JOIN apex_applications a
            ON a.workspace                  = d.workspace_name
        WHERE a.application_id              = sess.get_app_id()
            AND d.is_application_developer  = 'Yes'
            AND d.account_locked            = 'No'
            AND COALESCE(in_username, sess.get_user_id()) IN (UPPER(d.user_name), LOWER(d.email));
        --
        RETURN TRUE;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN FALSE;
    END;

Open APEX Builder (not the app)

I also need to identify developers session in APEX (not the app session, but the session from APEX ide). Imagine that I have a page which check other pages for some issues. With following function I can provide a link "fix this page" to open APEX ide on specific page without need to login.

SELECT MIN(s.apex_session_id) KEEP (DENSE_RANK FIRST ORDER BY s.session_created DESC) AS session_id
FROM apex_workspace_developers d
JOIN apex_applications a
    ON a.workspace                  = d.workspace_name
JOIN apex_workspace_sessions m
    ON m.apex_session_id            = sess.get_session_id()
    AND m.workspace_name            = d.workspace_name
JOIN apex_workspace_sessions s
    ON s.workspace_id               = m.workspace_id
    --AND s.remote_addr               = m.remote_addr       -- sadly this may not match
    AND s.apex_session_id           != m.apex_session_id
    AND UPPER(s.user_name)          IN (UPPER(d.user_name), UPPER(d.email))
WHERE a.application_id              = sess.get_app_id()
    AND d.is_application_developer  = 'Yes'
    AND d.account_locked            = 'No'
    AND UPPER(sess.get_user_id())   IN (UPPER(d.user_name), UPPER(d.email));