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.
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).
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
P_APP_USER IN VARCHAR2
) RETURN VARCHAR2 AS
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';
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.
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.
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.
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.
where project_region IN ('&F_REGION_IN.')