Apps To Fusion

Application Security is one of the prime focuses when designing web applications, as they are prone to many security vulnerabilities. Oracle Application Express (APEX) has many built in security features like Authentication, Authorization i.e. access level of the user, session state protection etc.

This article helps you understand Authentication schemes in APEX and how to use EBS login credentials in APEX. Authentication is a mechanism to securely identify a trusted user. It may be really simple (just enter username) or complex hash algorithm to validate username and password entered by the user.
Authentication scheme verifies user's identify before they can access your application. Once the user has been identified, APEX keep track of each user by setting the value of built-in substitution string APP_USER. You can access APP_USER using the following syntax:

• From PL/SQL: v('APP_USER')

• As a bind variable from either PL/SQL or SQL: :APP_USER

APEX Authentication Schemes are created/managed in Shared Components > Authenication Schemes (Security section) in your application. Below screenshot shows navigation to authentication schemes.

APEX comes with Preconfigured authentication schemes. When you select a preconfigured authentication scheme, APEX follows a standard behavior for authentication and session management. Preconfigured authenication schemes available are:

• Open Door Credentials: It enables anyone to access your application using a built-in
  login page that captures a user name. This authentication method is useful during
  application development.
• Oracle Application Express Account Credentials: It uses APEX username and password. For example, your apex.oracle.com login credentials have to be entered to run the application if you use this authentication method.
• Database Account Credentials: It utilizes database scheme accounts. This authentication scheme requires that a database user (schema) exist in apex database.
• LDAP Credentials Verification: You can configure any authentication scheme that uses a login page to use Lightweight Directory Access Protocol (LDAP) to verify the user name and password submitted on the login page.
• DAD Credentials Verification: This authentication scheme gets the user name from the DAD configuration or, if the account information is not stored in the DAD configuration, as the user name captured using the basic authentication challenge. This scheme also known as No Authenication.
• Single Sign-On Server Verification: Oracle Application Server Single Sign-On verification delegates authentication to the Oracle AS Single Sign-On (SSO) Server. To use this authentication scheme, your site must have already been registered as a partner application with the SSO server.

Below screenshot shows preconfigured authentication scheme when creating authentication scheme.

 

If your Oracle EBS is not integrated with Oracle SSO, you will have to create custom authentication scheme from scratch. Creating from scratch gives you complete control over your authentication interface. When defining your custom authentication scheme the following points should be noted:

1. Building a login page.

When you create a new application in APEX, a login page (page 101) is created. You can use this page as the "Invalid session page" in authentication scheme, i.e. when user session is stale or invalid, APEX redirects user to Invalid session page. You may build a custom login page instead of using default login page, and assign it to authentication scheme.

Default login page (Pg. 101) is shown below. This page has user name, password text fields, a login button and few processes to complete login process.

The Login process takes values of entered username and password, validates them based of authentication scheme attached to APEX application, and finally redirects user to a page i.e. P_FLOW_PAGE parameter (default is 1) on successful validation. If you wish to redirect user to page n, change P_FLOW_PAGE parameter. Below is code for login procedure.

wwv_flow_custom_auth_std.login(
P_UNAME => :P101_USERNAME,
P_PASSWORD => :P101_PASSWORD,
P_SESSION_ID => v('APP_SESSION'),
P_FLOW_PAGE => :APP_ID||':1'
);

In the login API call, you can optionally specify a p_preserve_case boolean argument. Set this to true if you don't want the username converted to upper case during credentials verification and session registration.

2. Custom Authentication Function.

This function will check the username/password and return boolean. APEX engine expects this function to have the signature (p_username in varchar2, p_password in varchar2) return boolean. The value of the username and password fields passed to the login API, which is called by the login page, will be passed to your function.

Suppose your authentication function is custom_ebs_auth, you enter return custom_ebs_auth in authentication function field during creation. Below Code for custom_ebs_auth uses fnd_web_sec package to validate against Oracle EBS users.

CREATE OR REPLACE function custom_ebs_auth (p_username IN VARCHAR2, p_password IN VARCHAR2)
return boolean
as
begin
if fnd_web_sec.validate_login(p_username, p_password) = 'Y' then
return true;
else
return false;
end if;
end;

3. Logout URL

This URL is used to redirect the user when logout button is clicked. Use the below URL.
wwv_flow_custom_auth_std.logout?p_this_flow=&APP_ID.&p_next_flow_page_sess=&APP_ID.:101:&SESSION.:LOGOUT


Steps for Creating an Authentication Scheme from Scratch

As I cannot integrate my apex.oracle.com account with local Oracle EBS server, I will mimic login functionality of Sample Application (App. 100) which uses custom authentication by calling custom_auth function. This function checks username/password in DEMO_USERS table. Password column in DEMO_USERS is encrypted using DBMS_OBFUSCATION package and an encryption key (l_salt variable in custom_hash function). Remember this approach is only for demonstration of custom authentication function, not to be used for production.

1. Insert users in DEMO_USERS table.

custom_auth function encrypts entered password using key in custom_hash function, and compares it to password column in DEMO_USERS. So when inserting users in DEMO_USERS, use same custom_hash function to encrypt password. Code for inserting a2f_admin and a2f_user in demo_users table. a2f_admin user has ADMIN_FLAG set to 'Y'.

declare
l_username varchar2(4000) := 'A2F_ADMIN';
l_password varchar2(4000) := 'welcome1';
l_username1 varchar2(4000) := 'A2F_USER';
l_password1 varchar2(4000) := 'welcome2';
l_salt varchar2(4000) := '4BS4EJ1R3L4UNRWZKPCX0HK6MTJ5YB';
begin
-- ADMIN User
l_password := custom_hash(l_username, l_password);
insert into demo_users values (DEMO_USERS_SEQ.nextval, l_username, l_password, SYSDATE, NULL, 'Y', NULL, 'Y');
-- Trigger BI_DEMO_USERS overrides admin_flag to 'N' for new users
-- So update is required for A2F_ADMIN
update demo_users set admin_user = 'Y'
where user_name = 'A2F_ADMIN';
-- User
l_password1 := custom_hash(l_username1, l_password1);
insert into demo_users values (DEMO_USERS_SEQ.nextval, l_username1, l_password1, SYSDATE, NULL, 'Y', NULL, 'N');
commit;
end;

2. Create custom authentication scheme.

Go to Shared Components > Authentication Scheme (Security Scheme) > Create. Select From scratch in create scheme.

3. Make authentication scheme Current

Once the authentication scheme is created, it has to be assigned to application. This is done from Change Current section in Authentication Scheme.

4. Run the application. I've changed login page to include some html text.

URL for the application:

My application can be accessed using the url http://apex.oracle.com/pls/otn/f?p=62577:1

Packaged Application:

My Packaged applications are created using APEX 3.2 version, you can only import them into APEX with same version. This packaged application has supporting objects i.e. table and sample data, along with apex application. You can import and run it without going through the above steps.

Download Packaged Application

The zip file has sql files for application (apex_tut03_app.sql) and image (apex_tut03_img.sql).

Video for deploying packaged application (2:41 min). This video is applicable for deploying packaged applications for my next articles as well.

The next article will be on Authentication vs Authorization, how Authorization is used in APEX.