Introduction

Recently I had a requirement for an APEX application to change authentication schemes dynamically.

my app ( let's call it "My App") users authenticate using Microsoft Azure AD.
app is installed on 2 environments - Development(DEV) and Production(PROD).
Oracle APEX version Is 22.1.0
APEX parsing schema user is MY_APP
my example app number is 101

Every environment differs in authorization endpoint URL ( it's set in APEX authentication_scheme). So my app has two authentication schemes:

When my app is ready, I want to export it from DEV and install it on PROD.

Until today, after every installation at PROD, I had to manually switch the authorization scheme from dev_auth_scheme to prod_auth_scheme.

Nothing special, but will I always (or other developers) remember to switch the current authorization scheme on the PROD environment manually? Tomorrow or later, someone will forget, and users won't be able to authorize.

That's why I decided to automate it and forget about it.

Option 1 (SQLcl installation)

Add a few lines of the script before installing your app (assuming installation using a command line like SQLcl)

declare
  l_env_type   application_params.value%type := null;
  c_dev_env    constant application_params.value%type := 'DEV';
  c_prod_env   constant application_params.value%type := 'PROD';
  c_prod_auth_scheme constant varchar2(100) := 'prod_auth_scheme';
  c_dev_auth_scheme  constant varchar2(100) := 'dev_auth_scheme';
begin
  --param table definining your environment type
  select value
    into l_env_type
    from application_params
   where code ='ENVIRONMENT_TYPE';

  apex_application_install.clear_all;
  apex_application_install.set_workspace('MYAPP');
  apex_application_install.set_application_name('My APP');   
  apex_application_install.generate_offset;
  apex_application_install.set_schema('MY_APP');

  if l_env_type = c_prod_env then
     apex_application_install.set_authentication_scheme(p_name => c_prod_auth_scheme);   
  elsif l_env_type = c_dev_env then --in case of installing on DEV
     apex_application_install.set_authentication_scheme(p_name => c_dev_auth_scheme);
  end if;
end;
/
@f101.sql

Option 2 ( SQLcl and APEX Builder installation)

Create supporting objects script that will be installed during apex installation.

The authentication scheme will be updated automatically during the installation of APEX through APEX Builder or SQLcl

Step 1

Create a procedure and deploy it on the SYS schema (or ADMIN if you use OCI)

create or replace procedure sys.p_change_authentication_scheme(
  pi_app_id in number)
as
  l_authentication_scheme_id my_app.application_params.code%TYPE; --authent. scheme ID that should be set
  l_apex_schema dba_registry.schema%TYPE; --current schema used by APEX
  l_update_sql VARCHAR2(4000);
begin
/* Make sure to insert value to your param table before exec this proc.You can get authentication scheme id from APEX_220100.wwv_flow_authentications */
  select value
    into l_authentication_scheme_id
    from my_app.application_params
   where code   = 'AUTHENTICATION_SCHEME_ID';
-- adjust your param table if you have more than 1 app 

--get current APEX version schema
  select schema
    into l_apex_schema
    from dba_registry
   where comp_id = 'APEX'
   order by schema desc
   fetch first 1 row only;

  l_update_sql := 'update '|| l_apex_schema || '.wwv_flows '|| 'set authentication_id = ' || l_authentication_scheme_id || ' where id =' || pi_app_id;

  execute immediate l_update_sql;

end p_change_authentication_scheme;
/

Step 2

Grant execute to the newly created procedure to your parsing schema MY_APP

GRANT EXECUTE ON SYS.p_change_authentication_scheme to MY_APP;

Step 3

Create a supporting object script for your app 101 -> App Builder -> Supporting Objects -> Installation Scripts

Create -> Create from scratch

Save your script.

Step 4

Don't forget to set "Include supporting object definitions in export"

Export your app from DEV environment and install it into PROD.

Don't forget to check "Install supporting objects" at the end of your installation.

Your authentication schema will be updated automatically.

If you want to switch your authentication scheme in session, you want to take a look at this blog post http://www.grassroots-oracle.com/2019/01/apex-authentication-switch-in-session.html

Change your Oracle APEX authentication scheme dynamically

Table of contents