Custom Authorisation controlling row filters

Written by Mandy. Posted in Authentication & Authorisation.

I had a requirement recently for a set of users to be managed within the application, assigning them access to certain 'Regions' (Australian States, etc). Based on this access, the data displayed in multiple reports throughout the application should only show the regions they have access to.

The reports mostly contain Projects & Project Financials information.

Create or Modify the custom Users table

To start, I set up my Custom Authentication table.

More information on how I do this in every application can be found here.

DB table definition 


Create Report and Form top edit table data

I then create the report and form which allows an "Admin User" to create and edit application users and their authorisation.

(The USERNAME field matches the username that is authenticated against our internal LDAP).

Manage users


Create Function to return text for WHERE Clause

I have then created a function to put together the string that will be used in a Select-statement WHERE clause. Since this will be used across many reports in the app, I stored the value in an application item.

The function code is below. You will notice that I have sandwiched the real values between dummy values 'X' and 'Z'. This is because of the comma that needs to be between each value in the WHERE value IN ('x','y','z'). This works so that even if the user has NO regions maked for them, it will return x','z

create or replace 
v_string varchar2(200);
cursor cur is
MY_USERS where upper(username) = upper(P_APP_USER);
v_string := 'X''';
for c1 in cur LOOP
if c1.REG_ACT = 'Y' then v_string := v_string||',''ACT'''; end if;
if c1.REG_NSW = 'Y' then v_string := v_string||',''NSW'''; end if;
if c1.REG_NZ = 'Y' then v_string := v_string||',''NZ'''; end if;
if c1.REG_QLD = 'Y' then v_string := v_string||',''QLD'''; end if;
v_string := v_string||',''Z';
return v_string;


Create Application Item to store value

Next I create an application item to store the value, that will be calculated when the user logs in.

Go to Shared Components > Application Items, and create a new item called F_REGION_IN.

Also set the Session State Protection to "Restricted - May not be set from browser" to prevent users from manipulating the URL to give them access to data they should not be able to see.

 application item

Once your item is created, go to Shared Components > Application Computations and Create New.

Choose the application item you just created. Set Computation Type to "SQL Expression", and then in the computation field, call your new function, passing the current user that is logged in.


item computation


Now when I run my application, and view the session state parameters, I can see what is being returned from the function into my application item F_REGION_IN.

 Session state


Use your item in your WHERE clauses

Now I can use this to filter all my reports, so that the users can only see the Projects within the regions that they have been selected to see.

select *
where project_region IN ('&F_REGION_IN.')

About Me

I currently specialise in developing bespoke applications using Oracle APEX.I have worked with APEX since it was known as HTMLDB, but have also dabbled with E-Business Suite, Oracle AS Portal, ECM, Webcenter and Website Design.

I enjoy working with new clients and assisting them develop custom applications to enhance and improve internal business processes.

Find Me

mandy nerd2