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
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; /
Grant execute to the newly created procedure to your parsing schema MY_APP
GRANT EXECUTE ON SYS.p_change_authentication_scheme to MY_APP;
Create a supporting object script for your app 101 -> App Builder -> Supporting Objects -> Installation Scripts
Create -> Create from scratch
Save your script.
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