Creating Custom Authentication
Getting started. To get started on creating and deploying these customer authentication schemes in your own suite of applications, first download the zip file and extract the contents. The download contains four SQL files—ns_auth_util.sql, ns_auth_aux.sql, login_logout_procs .sql, and table_ddl.sql—that need to be uploaded to Oracle Application Express.
To upload the files
1. In Oracle Application Express, go to the SQL Scripts page ( Home -> SQL Workshop -> SQL Scripts )
2. Click Upload , and on the Upload Script page, for File , browse to a file from the download such as ns_auth_util.sql, and click Upload
3. Repeat the previous step for the remaining files from the download
Setting up the suite login authentication scheme. Begin constructing the custom authentication solution by creating the login authentication scheme. In Oracle Application Express, go to the application designated as the login application and create a new authentication scheme from scratch.
1. On the Shared Components page, under Security, click Authentication Schemes .
2. On the Authentication Schemes page, click Create .
3. On the Create Authentication Scheme page, for Create Scheme , select From scratch and click Next .
4. For Name , enter Suite Login Authentication Scheme , and click the Create Scheme button.
Setting the authentication function. One of the entry points for integrating custom code into an Oracle Application Express authentication scheme is the authentication function. Oracle Application Express can run custom authentication function code to validate a username/password combination.
Listing 1 contains the code from the ns_auth_util.sql file you uploaded earlier; that code includes the ns_auth_util package and CHECK_CREDENTIALS—a custom authentication function. To plug the CHECK_CREDENTIALS function call into the suite login authentication scheme, add it as the authentication function.
Code Listing 1: NS_AUTH_UTIL package for suite login authentication
CREATE OR REPLACE PACKAGE ns_auth_util
AS
procedure set_sso_cookie;
function check_credentials (
--
-- credentials verification function for demonstration purposes only.
-- checks emp for valid username/pw combo; sets suite cookie
--
p_username in varchar2,
p_password in varchar2)
return boolean ;
END ns_auth_util;
/
create or replace PACKAGE BODY ns_auth_util
AS
g_cookie_expires date := sysdate + 1;
procedure set_sso_cookie is
--
-- sets suite cookie, called from check_credentials only
--
l_t_id number := null;
begin
owa_cookie.send( name => ns_auth_aux.g_cookie_name,
value => upper(v('P101_USERNAME')) || '^' || v('APP_SESSION'),
expires => null,
path => '/'
);
end set_sso_cookie;
function check_credentials (
--
-- credentials verification function. checks emp for
-- valid username/pw combo; sets suite cookie
--
p_username in varchar2,
p_password in varchar2)
return boolean
as
begin
for c1 in (select 1
from emp
where ename = upper(p_username)
and ename = upper(p_password))
loop
htp.init;
owa_util.mime_header('text/html', FALSE);
set_sso_cookie;
return true;
end loop;
return false;
end check_credentials;
END ns_auth_util;
/
show errors package body ns_auth_util;
1. On the Authentication Schemes page ( Home -> Application Builder -> Application number -> Shared Components -> Authentication Schemes ), click Suite Login Authentication Scheme .
2. On the Edit Authentication Scheme page, for Authentication Function (under Login Processing), enter
return_ns_auth_util.check_credentials;
3. Click Apply Changes.
Upon successful verification, the CHECK_CREDENTIALS function also calls the SET_SSO_COOKIE procedure, which sets the suite cookie that enables subsequent, transparent authentications to other applications in the suite. Note that for demonstration purposes, the CHECK_CREDENTIALS function checks the values of the EMP table, which installs with the sample application in every Oracle Application Express instance.
Setting up the auxiliary authentication scheme. Now set up the auxiliary authentication scheme on any Oracle Application Express instance that contains applications considered part of the suite. As with the suite login authentication scheme, create a new scheme from scratch.
1. For an auxiliary application in the suite, on the Shared Components page, under Security, click Authentication Schemes .
2. On the Authentication Schemes page, click Create .
3. On the Create Authentication Scheme page, for Create Scheme , select From scratch and click Next .
4. For Name , enter Auxiliary Authentication Scheme , and click the Create Scheme button.
Setting up LOGIN_PAGE to handle unauthenticated users. This Oracle Application Express custom authentication framework checks for a valid suite cookie whenever users navigate to any application within the suite. Requests from users without a cookie are considered unauthenticated. In this example, both the suite login authentication scheme and the auxiliary authentication scheme integrate a custom procedure, LOGIN_PAGE, to properly handle unauthenticated users:
create or replace PROCEDURE login_page
as
begin
owa_util.redirect_url (ns_auth_aux
.g_logout_url);
end;
This procedure uses the owa_util.redirect_url API to send users to the login page of the login application. This is done rather than hard-coding the available Session Not Valid URL attribute, to allow for easier maintenance. Because the procedure is called via a URL, the mod_plsql DAD user needs to be granted access to it:
grant execute on login_page to apex_public_user;
The call to the LOGIN_PAGE procedure is specified in the suite login authentication scheme as well as in all auxiliary authentication schemes in the form of a relative URL.
To add this LOGIN_PAGE procedure call to the suite login authentication scheme
1. On the Authentication Schemes page ( Home -> Application Builder -> Application number -> Shared Components -> Authentication Schemes ), click Suite Login Authentication Scheme
2. On the Edit Authentication Scheme page, for Session Not Valid URL (under Page Session Management), enter
#OWNER#.login_page?p_app_id=&APP_ID.
3. Click Apply Changes
To add the LOGIN_PAGE procedure call to all auxiliary authentication schemes, repeat the preceding steps for each of those schemes. Using a page sentry function to validate the session. The other function critical to custom authentication schemes is the confirmation of a valid session with each requested page view. In Oracle Application Express, the page sentry function performs this duty. At a minimum, the custom page sentry function for this implementation’s authentication schemes must perform the following tasks in the order specified:
1. Confirm that the current user’s Web browser has a valid suite cookie.
2. Confirm that the current user’s Web browser has a valid Oracle Application Express session cookie for the currently requested application.
3. Confirm that the username from the suite cookie matches the Oracle Application Express session cookie.
4. Instantiate a new Oracle Application Express session or rejoin the existing session if the previous three conditions are met.
The ns_auth_aux.sql script, included with the download for this article, contains the code for the page sentry function (SENTRY). The page sentry function uses several of the Oracle Application Express authentication APIs from the APEX_CUSTOM_AUTH package that facilitate working with sessions, specifically
- IS_SESSION_VALID , which checks to see that the invoking Oracle Application Express application has a valid session
- GET_USERNAME , which retrieves the username associated with the session if the Oracle Application Express session is validGET_SESSION_ID_FROM_COOKIE , which retrieves the Oracle Application Express session ID from the Oracle Application Express session cookie stored by the user’s Web browser, if available
- DEFINE_USER_SESSION , which instantiates a new Oracle Application Express application session
- LOGOUT , which expires the Oracle Application Express session and redirects the user’s browser to a specified location
Every application in the suite must perform the suite and session cookie checks with each page view, so include the call to the SENTRY function for the page sentry function of the suite login authentication scheme and all auxiliary authentication schemes.
To add this SENTRY function call to the suite login authentication scheme
1. On the Authentication Schemes page ( Home -> Application Builder -> Application number -> Shared Components -> Authentication Schemes ), click Suite Login Authentication Scheme
2. On the Edit Authentication Scheme page, for Page Sentry Function (under Page Session Management), enter
return_ns_auth_aux.sentry;
3. Click Apply Changes
To add the SENTRY function call to all auxiliary authentication schemes, repeat the preceding steps for each of those schemes.
With the SENTRY function added to all of the suite’s authentication schemes, any application in the same domain as the suite cookie will respect the single-sign-on functionality.
Single-Sign-off Implementation
Signing users off from applications in the suite is somewhat more challenging than signing on, because it is not simply a matter of expiring the suite cookie set during authentication. For a proper sign-off, the session information for each application in the suite must also be expired.
Three additional constructs—a table, a logout procedure, and a branching logout page—work together to provide this single-sign-off mechanism.
Table of suite applications for logout. With authenticated applications potentially being distributed across multiple database instances, the implementation must be able to visit each application in the suite to ensure deauthentication before returning users to the login page. A table, NS_SUITE_APPS, contains the necessary information about every application in the suite.
The NS_SUITE_APPS table is stored in the schema of the login application, but it must be accessible to the schemas of every application. For any schemas on the same instance as the login application, grant the schema access to the table. For any schemas on the other instances, set up a database link and a synonym, NS_SUITE_APPS.
Code Listing 2: Creating the NS_SUITE_APPS table
drop table ns_suite_apps;
CREATE TABLE "NS_SUITE_APPS"
("ID" NUMBER,
"APP_ALIAS" VARCHAR2(30),
"APP_ID" NUMBER,
"LOGOUT_PAGE_ID" NUMBER,
"HOST_PORT" VARCHAR2(250),
"INSTANCE" VARCHAR2(30),
"STATUS" VARCHAR2(30),
"HOME_PAGE" VARCHAR2(300),
CONSTRAINT "NS_SUITE_APPS_PK" PRIMARY KEY ("ID") ENABLE,
CONSTRAINT "NS_SUITE_APPS_ALIAS_UNK_CON" UNIQUE ("APP_ALIAS") ENABLE,
CONSTRAINT "NS_SUITE_APPS_APPID_UNK_CON" UNIQUE ("APP_ID") ENABLE
)
/
drop sequence NS_SUITE_APPS_SEQ;
create sequence NS_SUITE_APPS_SEQ start with 1;
CREATE OR REPLACE TRIGGER "bi_NS_SUITE_APPS"
before insert on "NS_SUITE_APPS"
for each row
begin
if :new."ID" is null then
select "NS_SUITE_APPS_SEQ".nextval into :new."ID" from dual;
end if;
end;
/
ALTER TRIGGER "bi_NS_SUITE_APPS" ENABLE
/
-- example inserts into the ns_suite_apps table
Insert into NS_SUITE_APPS
(ID, APP_ALIAS, APP_ID, LOGOUT_PAGE_ID, HOST_PORT, INSTANCE, STATUS, HOME_PAGE)
values
(1, 'TASKS', 108,102, 'www.nianticsystems.com', 'ODBLAP1', 'ACTIVE', '1');
Insert into NS_SUITE_APPS
(ID, APP_ALIAS, APP_ID, LOGOUT_PAGE_ID, HOST_PORT, INSTANCE, STATUS, HOME_PAGE)
values
(2, 'HARDWARE', 114,102, 'www.nianticsystems_remote.com', 'ODBLAP4', 'ACTIVE', '1');
commit;
Listing 2 contains the data definition language for the NS_SUITE_APPS table. The following columns from that table are used by the single-sign-out procedure:
- APP_ID , the identification number of the application from which the user is to be deauthenticated
- LOGOUT_PAGE_ID , the page number of the logout page associated with the application
- HOST_PORT , the host and port of the Web server providing access to the application
- INSTANCE , the logical name of the application’s database instance
- APP_ALIAS , the alias of the application as defined on the application-level attributes page
Unique constraints defined on the APP_ALIAS and APP_ID columns simplify the flow of the example code, but if you remove these constraints, be sure to modify the main cursor of the logout procedure accordingly.
Log out from all suite applications. The logout procedure (included in the ns_auth_aux package) recursively expires all applications’ session data and ultimately redirects users to the public login page. The procedure accomplishes this by stepping through the applications in the NS_SUITE_APPS table in a known order. After all suite applications have been deauthenticated, the procedure takes users to the public login page.
Note that the packaged logout procedure is actually called via the following wrapper procedure:
create or replace PROCEDURE "NS_LOGOUT" (p_app_alias in varchar2 default null) as
begin
ns_auth_aux.logout (p_app_alias);
end;
The logout procedure call is isolated in this fashion to minimize the scope of the privileges granted to the mod_plsql DAD user. Because the procedure is called via a URL, the mod_plsql DAD user must have execution privileges on the procedure. Wrapping the packaged logout procedure in effect limits the scope of this grant.
The logout procedure is called from both the logout navigation link and a logout page. By default, Oracle Application Express defines the URL target of this link to be &LOGOUT_URL. (This built-in Oracle Application Express substitution variable references the logout URL attribute specified in the application’s authentication scheme.)
Now update the target of the logout URL link to use the NS_LOGOUT (ns_auth_aux.logout) procedure in the suite login authentication scheme and all auxiliary authentication schemes.
To update the logout URL for the suite login authentication scheme
1. On the Authentication Schemes page ( Home -> Application Builder -> Application number -> Shared Components -> Authentication Schemes ), click Suite Login Authentication Scheme
2. On the Edit Authentication Scheme page, for Logout URL (under Logout URL), enter
#OWNER#.ns_logout
3. Click Apply Changes
To update the logout URL for all the auxiliary authentication schemes, repeat the preceding steps for each of those schemes.
Note that the locally stored logout procedure is called without passing through an application alias. Invoking NS_LOGOUT this way starts the recursive logout process driven by the cursor query executed against the NS_SUITE_APPS table:
select app_id, instance, host_port, logout_page_id, app_alias from NS_SUITE_APPS where STATUS = 'ACTIVE' order by instance, app_id
This query returns all applications from the table with an ACTIVE status, and it does so in a known order. Because a P_APP_ID value is not specified in the call, the routine starts at the top of the list.
Logout page for branching logic for the logout process. In addition to the logout navigation link, a logout page that provides the necessary branching logic for the logout process must be available in each application in the suite. In this example, the logout page is identified by the LOGOUT_PAGE_ID in the NS_SUITE_APPS table. The logout page contains one branch firing with a branch point value of Before Header.
1. On the Application page ( Home -> Application Builder -> Application number ), click your logout page
2. On the details page, under Page Processing and Branches, click the branch for the logout procedure
3. On the Edit Branch page, in the Point section, for Branch Point , select On Load: Before Header
4. On the Edit Branch page, in the Action section, for Target type , select URL , and in the URL Target text box, enter
#OWNER#.ns_logout?p_app_alias=&APP_ALIAS.
5. Click Apply ChangesThe target of the branch firing in the logout page is a call back to the logout procedure, but using the application alias. By passing through the application alias, the procedure keeps track of where it is in the list of applications. For every application in the list, the logout procedure logs the user out from the current application and redirects the user’s browser to the next application for deauthentication.When the logout procedure reaches the last application in the list, the procedure logs the user out from the application, expires the suite cookie, and redirects the user to the public login page. With this last step, the single-sign-out process is complete.Conclusion
When my team implemented this authentication mechanism at the client site, the rollout was a success. Being a large government contractor, the client put the solution through a rather rigorous security review, which it passed without issue. A major contributing factor to that success was the leveraging of the existing authentication infrastructure that Oracle Application Express provides developers. Because our implementation augmented the authentication functionality already performed by Oracle Application Express, the security review was brief as well.Finally, it is important to reiterate that you should almost always try to take this approach when adding custom code to any Oracle Application Express application: leverage existing infrastructure whenever possible, and add customizations as necessary. As demonstrated by this article, following this approach makes even more-advanced tasks, such as developing custom authentication mechanisms, straightforward and manageable.
