Query Azure grom from APEX login

apex_post_authentication_azure.sql

/*
Create a table that will store the group object ID-s that your application is interested in (group_id)
and some kind of string that your application can understand (granted_role).

create table access_groups (
    group_id varchar2(100) not null,
    granted_role varchar2(100) not null,
    primary key (group_id, granted_role)
) organization index;
*/

create or replace procedure apex_post_authentication is
    l_clob  clob;
begin
    /*
    This is execute after APEX completes OAuth authentication - and we need to make an additional request to Azure to get more detailed user metadata,
    specifically the group membership.
    https://learn.microsoft.com/en-us/graph/api/user-get?view=graph-rest-1.0&tabs=http
    
    I also left in other options I tried (like GraphQL batch query) and more advanced filtering options, for documentation purposes.
    
    Ilmar Kerm 2023
    */
    -- Create collection to store enabled roles
    APEX_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION('APP_USER_ENABLED_ROLES');
    -- Build batch query
    /* Not sure if this batching works, since I get HTTP 403 for groups that the user does not have and 200 that the user is member of. Not sure if it is by design or not.
       Does not feel safe. */
    /*SELECT json_arrayagg(
        json_object(
            key 'id' is group_id, 
            key 'method' is 'GET',
            key 'url' is '/me/memberOf?$select=id&$filter='||apex_util.url_encode('id eq '''||group_id||'''')
        RETURNING varchar2(4000))
    ) INTO l_batch_request FROM (
        SELECT DISTINCT group_id FROM access_groups);
    BEGIN
        APEX_WEB_SERVICE.SET_REQUEST_HEADERS('Accept','application/json','Content-Type','application/json',p_reset=>false);
        l_clob:= apex_web_service.make_rest_request(
            p_url         => 'https://graph.microsoft.com/v1.0/$batch',
            p_http_method => 'POST',
            p_body => json_object('requests' VALUE l_batch_request FORMAT JSON)
        );
        insert into tmp_debug (ts, c) values (sys_extract_utc(systimestamp), l_clob);
    EXCEPTION
        WHEN OTHERS THEN
            APEX_DEBUG.ERROR(dbms_utility.format_error_backtrace);
    END;*/
    begin
        -- $search="id:value-here" -- does not work "The request uses a filter property that is not indexed"
        -- $filter=id in ('v1','v2','v3') -- not supported
        -- $filter=id eq 'v1' or id eq 'v2' -- not supported
        -- requesting all user groups, can be just too big response that gets paginated, but 500 and 1000 seem to be supported page sizes
        -- hopefully no users has nore enabled groups than that :) so I'm going ignore paging here
        l_clob:= apex_web_service.make_rest_request(
            p_url         => 'https://graph.microsoft.com/v1.0/me/memberOf?$select=id&$count=true&$top=500',
            p_http_method => 'GET'
        );
        -- For debugging the REST response
        --insert into tmp_debug (ts, c) values (sys_extract_utc(systimestamp), l_clob);
        -- Convert the member group ID-s to granted roles and store them in user session
        FOR rec IN (SELECT DISTINCT access_groups.granted_role FROM (SELECT id FROM json_table(l_clob, '$.value[*]' columns (id varchar2(100) path '$.id'))) user_groups JOIN access_groups ON user_groups.id=access_groups.group_id) LOOP
            APEX_COLLECTION.ADD_MEMBER('APP_USER_ENABLED_ROLES', rec.granted_role);
        END LOOP;
    exception
        when others then
            APEX_DEBUG.ERROR(dbms_utility.format_error_backtrace);
    end;
end;
/