Sending automated emails to IAM users using Oracle APEX

Photo by erica steeves on Unsplash

Oracle Cloud Infrastructure Identity and Access Management (IAM) provides the security platform for Oracle Cloud, which allows users to securely and easily access, develop, and deploy business applications. It is designed to help organisations manage and secure user identities and access to applications, both within the organization and across cloud environments.

Oracle APEX is an enterprise low-code development platform that is used to develop and deploy web applications on Oracle databases. In this post, you’ll learn how to setup an automation to send emails to IAM users using Oracle APEX.

Step 1: Registering a Client Application

To create and register a client application:

1. On your OCI Console, open the navigation menu and click Identity & Security. Under Identity, click Domains.
2. Click the name of the identity domain that you want to work in. You might need to change the compartment to find the domain that you want. Then, click Integrated applications.
3. Click Add application.
4. In the Add application dialog box, select Confidential Application, and then click Launch workflow.
5. On the Add application details page, enter an application name and description, and then click Next.
6. On the Configure OAuth page, under Client configuration, select Configure this application as a client now.
7. Under Authorization, select only Client Credentials as the Allowed Grant Type.
8. At the bottom of the page, select Add app roles and then click Add roles.
9. In the Add app roles panel, select User Administrator, and then click Add.
10. Click Next and then click Finish.
11. On the application detail page, scroll down to General Information. Copy the Client ID and the Client Secret and store it in a safe place.
12. After the application is created, click Activate.

More details: https://docs.oracle.com/en-us/iaas/Content/Identity/mfa/register-client-app.htm#register-client-app

Step 2: Create a new Web Credential to access the IAM REST APIs on your APEX application using the client application you created in Step 1

On your APEX application backend, go to Shared Components -> Credentials -> Create a new credential of Authentication Type ‘OAuth 2 Client Credentials Flow’

1. Enter Scope ‘urn:opc:idm:__myscopes__’
2. Enter Client ID and Secret and Hit Save

Step 3: Create a new REST data source to fetch the IAM users

On your APEX application backend, go to Shared Components -> REST Data source -> Create

1. Create REST data source ‘From Scratch’
2. Select ‘REST Data Source Type’ as Oracle Cloud Infrastructure(OCI)
3. Enter name and URL Endpoint (https://XXXX.identity.oraclecloud.com:443/admin/v1/Users)
4. Choose Authentication Required ‘Yes’ and select the credentials you created in Step 2
5. Enter the OAuth Token URL (https://XXXX.identity.oraclecloud.com:443/oauth2/v1/token)
6. Click on ‘Discover’ and ‘Create REST Data Source’.

Step 4: Create a new Email Template

On your APEX application backend, go to Shared Components -> Email Templates and create a new Email Template that you wish to send to the IAM users.

Step 5: Setup Automation to send Emails

On your APEX application backend, go to Shared Components -> Automations -> Click create

1. Enter a name
2. Select Type as ‘Scheduled’
3. Actions initiated on ‘Query’
4. Pick an ‘Execution Schedule’
5. Select REST data source as Data Source and select the REST data source you just created to fetch the IAM users (step 3).
6. Hit Create.
7. Now go to Actions tab -> New Action -> Edit and add the PL/SQL code to call the IAM REST APIs to fetch the user details and send Emails.

DECLARE
l_rest_url              VARCHAR2(1000);
l_response_clob         CLOB;
l_token_url             VARCHAR2(1000);

CURSOR res_data (res_json IN CLOB) IS
    SELECT jt.*
    FROM   JSON_TABLE(res_json, '$.Resources[*]'
             COLUMNS (id      VARCHAR2(50)  PATH '$.id',
             NESTED PATH '$.emails[*]' COLUMNS
             (
primary VARCHAR2(50) PATH '$.primary',
value      VARCHAR2(50)  PATH '$.value'
             )
                      )) jt WHERE  jt.primary = 'true';

BEGIN

  apex_web_service.g_request_headers(1).name := 'Accept';
  apex_web_service.g_request_headers(1).value := 'application/json';
  l_rest_url := 'https://XXXX.identity.oraclecloud.com:443/admin/v1/Users';
  l_token_url := 'https://XXXX.identity.oraclecloud.com:443/oauth2/v1/token';

--   -- 3. Call Web Service.
  l_response_clob := apex_web_service.make_rest_request(
    p_url                  => l_rest_url,
    p_http_method          => 'GET',
    p_credential_static_id => 'my_web_credential', --Use the credential ID created in Step 2
    p_token_url            => l_token_url
     );
   dbms_output.put_line('HTTP Status Code: '||apex_web_service.g_status_code);
   IF apex_web_service.g_status_code = 200 THEN
    -- 4. Parse the response.

    FOR in_field IN res_data (res_json => l_response_clob) LOOP
apex_mail.send (
        p_to                 => in_field.value,
        p_template_static_id => 'SEND_EMAIL', --Use the email template id you created in step 4
        p_placeholders       => '{' ||
        '}' );
  END LOOP;
    
END IF;
  end;

More details:

APEX Automations

App Builder User's Guide

Identity Domain REST APIs

Oracle Cloud Infrastructure API Reference and Endpoints