Oracle APEX Tutorial 10 – Access Control – Part 1 – Video Training

kevin.landonTechnical Tips3 Comments


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 file

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

p_username IN VARCHAR2,
p_password IN VARCHAR2)
FROM acl_employees
WHERE UPPER(userid) = UPPER(p_username)
AND UPPER(last_name) = UPPER(p_password))

Authorization Function Command

return acl_custom_auth

3 Comments on “Oracle APEX Tutorial 10 – Access Control – Part 1 – Video Training”

  1. Thanks for your effort. Really it was a very help series.

    I have one question,

    the function in this tutorial would not work. What could be the reason???

  2. I can’t find the the acl Zip file in oracle website, can you help me with that?
    And thanks a million for this useful serial of videos .

Leave a Reply to Najla Cancel reply

Your email address will not be published.