In this Blog, we are going to create two tables

• tenants

• t_products

An APEX application will be built on the t_products table.

We will create two FREE Oracle Cloud IAM Domains representing two Tenants (aka. Customer user sets).

Each Tenant can see a slice of the t_products data i.e. 1 row for Tenant 1 and 3 rows for Tenant 2.

Access to rows will be achieved via a VPD

Both Authentication and Authorization (demonstrated in this blog) will be achieved in Oracle IAM.

A Post Authentication PLSQL will map IAM roles to Custom APEX Roles & activate the VPD.

Checklist

First, check if you have IAM or the older IDCS in Oracle Cloud click Menu > Identity & Security

If you can see the word 'Domains' then you have IAM. If you cannot see the word 'Domains', then you are unable to continue this guide.

I've been informed that your tenancy will update to IAM in the future. I do not have any information on timescales.

Next, check to ensure you are running Enterprise Edition of the database

select banner from v$version;

This will need to have EE or Enterprise Edition in the name.

Next, check to see if you have execute on the dbms_rls package.

select 'PASS' from dual where exists ( select 1 from user_tab_privs where table_name = 'DBMS_RLS' and owner = 'SYS' and privilege = 'EXECUTE');

This will return a PASS if you have access. If it returns no rows, have your DBA run the following statement

NOTE: This will not be possible to run on apex.oracle.com

grant execute on sys.dbms_rls to YOUR_SCHEMA;

Steps

Database & APEX Configuration

1. Run the following in your schema

    -- create tables 
    create table tenants ( id number generated by default on null as identity constraint tenants_id_pk primary key, name varchar2(32 char), domain varchar2(32 char) ) ;
   
    create table t_products ( id number generated by default on null as identity constraint t_products_id_pk primary key, tenant_id number constraint t_products_tenant_id_fk references tenants on delete cascade, name varchar2(50 char), description varchar2(4000 char), sku varchar2(30 char), unit number, unit_price number ) ;
   
    -- table index 
    create index t_products_i1 on t_products (tenant_id);
   
    -- Add Tenant Default 
    alter table T_PRODUCTS modify ("TENANT_ID" NUMBER default on null nvl(sys_context('APEX$SESSION', 'APP_TENANT_ID'), -1) );
   
    insert into tenants ( id, name, domain ) values ( 1, 'TENANT_1', 'DOMAIN_1' );
   
    insert into tenants ( id, name, domain ) values ( 2, 'TENANT_2', 'DOMAIN_2' );
   
    insert into tenants ( id, name, domain ) values ( 3, 'TENANT_3', 'DOMAIN_3' );
   
    commit;
   
    alter table tenants modify id generated by default on null as identity restart start with 3;
   
    insert into t_products ( id, tenant_id, name, description, sku, unit, unit_price ) values ( 1, 1, 'M-AABJ-35', 'Sn faucibus. Ut id nulla.', 'Fames Ac Ante', 38, 89 );
   
    insert into t_products ( id, tenant_id, name, description, sku, unit, unit_price ) values ( 2, 2, 'M-AADP-93', 'Is. Duis et commodo.', 'Ipsumprimis In Faucibus', 86, 63 );
   
    insert into t_products ( id, tenant_id, name, description, sku, unit, unit_price ) values ( 3, 2, 'M-AACX-75', 'Vulputate um a ligula.', 'Ante Ipsumprimis In', 65, 16 );
   
    insert into t_products ( id, tenant_id, name, description, sku, unit, unit_price ) values ( 4, 2, 'M-AAAX-23', 'Ex. Etesque. Proin.', 'Vestibulum Ante Ipsumprimis', 4, 78 );
   
    insert into t_products ( id, tenant_id, name, description, sku, unit, unit_price ) values ( 5, 3, 'M-AAAX-23', 'Domain 3 Lorem Ipsum.', 'Lorem Ipsum', 5, 7 );
   
    insert into t_products ( id, tenant_id, name, description, sku, unit, unit_price ) values ( 6, 3, 'M-AAAX-23', 'Domain 3 Latin Text.', 'Domain 3 Latin Text.', 6, 8 );
   
    alter table t_products modify id generated by default on null as identity restart start with 7;
   

2. Run the following Package Specification in SQL Workshop

    create or replace PACKAGE apx_tenant
    IS
        function get_tenant_predicate(
                object_schema IN VARCHAR2, 
                object_name VARCHAR2) return varchar2;
   
          PROCEDURE p_set_tenant_id( p_domain VARCHAR2);
   
    END apx_tenant;
    /
   

   Now run the following Package Body in SQL Workshop

    create or replace PACKAGE BODY apx_tenant
    IS
   
        function get_tenant_predicate(
                object_schema IN VARCHAR2, 
                object_name VARCHAR2) return varchar2 as
   
                l_predicate varchar2(4000);
   
        begin
            -- Only apply VPD to specific APEX applications
            -- Change this "if condition" as appropriate but note that it should be VERY FAST 
            if sys_context('APEX$SESSION', 'APP_ID') IS NOT NULL then
                -- this predicate is optimized for performance
                -- Note the use of sys_context. This allows the query to be parsed once.
                -- And sys_context values are in memory so it is very very fast.
                l_predicate:= q'! tenant_id = sys_context('APEX$SESSION', 'APP_TENANT_ID') !';
            end if;
   
            return l_predicate;
        end get_tenant_predicate;
   
        PROCEDURE p_set_tenant_id( p_domain VARCHAR2)
        IS
        BEGIN
        FOR x IN ( SELECT id FROM tenants WHERE domain = p_domain )
        LOOP
          APEX_SESSION.SET_TENANT_ID( p_tenant_id => x.id );
        END LOOP;
        END p_set_tenant_id;
   
    END apx_tenant;
    /
   

3. Create the VPD Policy for the T_PRODUCTS table

    begin
        dbms_rls.add_policy(
          object_schema => 'MYSCHEMA', -- Change
          object_name => 'T_PRODUCTS', 
          policy_name => 'MY_TABLE_VPD', 
          function_schema =>'MYSCHEMA', -- Change
          policy_function => 'apx_tenant.get_tenant_predicate',
          statement_types => null,
          update_check  => true);
   
        commit;  
    end;
   

4. Create an APEX Application, in SQL Workshop > Object Browser > Tables > T_PRODUCTS > More > Create App > Create Application

5. Click Edit Application Definition and make a note of the application alias e.g T-PRODUCTS

   

6. Click Shared Components > Authorization Schems > Create > Next

   Use the table below:

7. Click Create Authorization Scheme

8. Click Create to create a new Authorization Scheme and click Next

9. Use the table below:

10. Click Create Authorization Scheme

11. Click Shared Components > Security Attributes > Source for Role or Group Schemes and set it to Custom Code

12. Click Apply Changes

13. Click Shared Components > Lists > Page Navigation

14. In the Interactive Grid Set Dashboard to Empty & T Products Search to Supervisors

15. Click Apply Changes

16. Edit Page 1 and add an Interactive Report region using the following SQL. This will show you the Roles that the logged in User has access to

select WORKSPACE_ID,
       WORKSPACE_NAME,
       WORKSPACE_DISPLAY_NAME,
       APEX_SESSION_ID,
       USER_NAME,
       GROUP_NAME
  from APEX_WORKSPACE_SESSION_GROUPS
 where apex_session_id = :APP_SESSION

Domain & Authentication Configuration

1. Oracle Cloud click Menu > Identity & Security > Domains > Create Domain

2. Use the table below:

3. Click Create Domain
This will take a few minutes to create (This page does not appear to refresh by itself). In the meantime, check your inbox and activate your account by following the instructions to reset your password. Don't bother clicking Continue to Sign In, just close the browser tab.

4. Click on IAM.APEX.DOMAIN_1 > Overview > Domain URL > Show
e.g.

https://idcs-9948b68b.identity.oraclecloud.com:443

5. Copy this Discovery URL to Notepad and append the following to it

/.well-known/openid-configuration/

e.g so that it looks like this

https://idcs-9948b68b.identity.oraclecloud.com:443/.well-known/openid-configuration/

6. Click Groups > Create Group

7. In Name type Supervisors and check the username that you've just created and click Create

8. Click IAM.APEX.DOMAIN_1 in the breadcrumb

9. Click Groups > Create Group

10. In Name type Empty and click Create (without assigning any users)

11. Click IAM.APEX.DOMAIN_1 in the breadcrumb

12. Click Applications > Add Application > Confidential Application > Launch Workflow

13. Use the table below:

Click Next

14. Click on the radio button from the picture below

15. Click Authorization Code and then proceed to the next step

16. Run the following in your SQL Workshop

select APEX_AUTHENTICATION.GET_CALLBACK_URL () from dual;

From the results, just copy everything up to the callback phrase, e.g

https://apex.oracle.com/pls/apex/apex_authentication.callback

17. Paste the above into the Redirect URL

18. In the Post-logout redirect URL type in the following

[BASE URL]f?p=[APPLICATION ALIAS]:1::APEX_AUTHENTICATION=DOMAIN1

e.g

https://apex.oracle.com/pls/apex/f?p=T-PRODUCTS:1::APEX_AUTHENTICATION=DOMAIN1

19. When it resembles the following... click Next

20. Click Finish (without configuring a policy)

21. Click Activate > Activate Application

22\ Click Users > Assign Users

23. Check the User checkbox

and click Assign

24. Click OAuth configuration & find the General Information

25. Copy the Client ID > Paste into Notepad

26. Click Client Secret >Show Secret > Copy > Paste into Notepad

27. In APEX click App Builder > Workspace Utilities > Web Credentials > Create

28. Use the following table

29. Click Create

30. Go back to your APEX Application > Authentication Schemes > Create

31. Click Next on Based on a pre-configured scheme from the gallery

32. Use the following table

33. Click Create Authentication Scheme

34. Click on Authentication Scheme DOMAIN1

35. Use the following table

  PROCEDURE p_post_processing 
  IS
    l_group_names apex_t_varchar2;
  BEGIN

    apx_tenant.p_set_tenant_id( 'DOMAIN_1');
    --
    -- add all group names to l_group_names
    -- 
    for i in 1 .. apex_json.get_count('groups') loop
        apex_string.push (
          p_table => l_group_names,
          p_value => apex_json.get_varchar2 (
                        p_path => 'groups[%d].name',
                        p0     => i ));
    end loop;

    -- save group names in session

    apex_authorization.enable_dynamic_groups (
        p_group_names => l_group_names );

  END p_post_processing;

36. Click Apply Changes

37. Navigate to Page 1 in APEX

Testing the Authentication

1. Click Run Page and it'll look like this

   

2. Sign in with your email and newly reset password

3. Allow the access

   

4. Now you should have access to the application

   

   Notice, that since the User has the Supervisors Role, they can see the T Products Search Card. However, since they do not have the Empty Role, they cannot see the Dashboard

5. Click on T Products Report

6. 

   Notice the VPD has been applied and only the 1 row is displayed for this Tenant.

Multiple Tenancies

1. Perform the Domain & Authentication Configuration steps again, but use DOMAIN2 instead of DOMAIN1, so that you have two IAM Domains and two APEX Authentication Schemes.

   NOTE: There will be several places where you need to change DOMAIN_1 to DOMAIN_2 and DOMAIN1 to DOMAIN2

2. Run/Test the application by using the Post-Logout URL i.e

1. When clicking the DOMAIN2 link, and then viewing T Products Report, notice that the VPD has been applied and now 3 rows are displayed for Tenant 2

   

2. ENJOY

Sources