Introduction

Security. We hear about it everywhere. Make this more secure, make this less secure. In the computer industry this is also true, and a constant need for any system or application. APEX allows for basic security by user login, but there is more you might need to do. For this, we have Access Controls. This tutorial will walk you though adding a custom authentication control setup to an application. If you haven’t already, we recommend you review our article Oracle APEX Tutorial Preparation and OEHR Sample Data Install to familiarize yourself with APEX. This tutorial also uses a second downloaded file, which the link is below.

Part 1 – Creating the Base Application and Controls

Location of ACL_EMPLOYEE.zip file

http://www.oracle.com/technology/products/database/application_express/packaged_apps/acl_employees.zip

Simply download the .zip file and extract it, keeping note of where you put the extracted file.

High Level Steps

1) Creating and Testing the Base Application (0:30)
2) Create the Access Controls (3:30)
3) Add User to Access Control List (4:56)
4) Create and Link the Custom Authentication Function (6:10)

Times in parenthesis are the approximate start time for that step

Notes and Resources

Authentication Options

APEX offers you three primary ways you can authenticate your users. The built in APEX option, no authentication, and by database account. All of our tutorials so far have used the APEX built in option. Within this option you can build more layers of controls, as we are in this example, or leave it as is. What this boils down to is whether or not you want to have to create APEX system level accounts or program level accounts for each user. In this example, we are creating program level accounts based off the “acl_employees” data. Using this method, we don’t have to create additional APEX system accounts, and you can better contain access to the program and to the APEX system. The actual needs may vary and you should consult your IT department to ensure you are complying with any in-house security standards you may have in place.

Application Modes

We have the option of four modes in which the application can run in. You can choose to run a given application in one of ‘Full Access to all’ , ‘Restricted Access’, ‘Public Access’, or ‘Administrative Access Only’. We are using the second of these, as it allows us to define view, edit and administrative levels of access and assign users to one of those groups. The mode you need can vary by application and the needs and policies of your group. Each level of access except for Full Access still allows some restriction, based on the level you chose.

Access Inclusion

When you set a specific access level as we are in this example, it means that is the minimum level needed to use that item. Any access above that is included in the permissions. IE If you go to a page that is set for view, if you have view, edit or admin rights you can use it, but if you try and go to a edit restricted page, anyone with view rights is locked out, and only edit or higher (admin) will be able to proceed.

Code and Pasted Text

PL/SQL fuction

CREATE OR REPLACE FUNCTION acl_custom_auth (
p_username IN VARCHAR2,
p_password IN VARCHAR2)
RETURN BOOLEAN IS
BEGIN
FOR c1 IN (SELECT 1
FROM acl_employees
WHERE UPPER(userid) = UPPER(p_username)
AND UPPER(last_name) = UPPER(p_password))
LOOP
RETURN TRUE;
END LOOP;
RETURN FALSE;
END;
/

Authorization Function Command

return acl_custom_auth