Oracle Apex authorization scheme based on page item value

Question

I am currently working in Oracle Apex version 5.1 and I am trying to set up an authorization scheme that is based on the value of a text field page item on page 31 in my application, as well as other factors, but I am not having any success.

I created a dynamic list based on a SQL query from one of my tables called NAVIGATION. I then used this list as my "Navigation Menu" in my application. I set up the query for the list in such a way that if I click on the Navigation Menu tab that directs me to page 31 in the application, the value for the text field page item in question is automatically pushed to that text field. However, I also set up the query for the list in such a way that multiple tabs in the Navigation Menu actually direct the user to page 31, but the value that is pushed to the text field page item depends on the Navigation menu tab that is clicked. In other words, that text field page item can hold different values depending on which tab in the Navigation Menu was clicked. The reason I set it up this way is because different things are displayed on page 31 depending on the value in that text field. That all works perfectly. However, what I want displayed on that page is dependent on who the user is, which brings me to the issue of the authorization scheme.

Now, I want to set up an authorization scheme for page 31 that is dependent on that text field page item, as well as dependent on specific data from one of my tables called TEAMMEMBERS. TEAMMEMBERS has a column called "username" and a column called "P31_Access". The column "username" contains the person's username to get into the application. "P31_Acess" contains the different values that can be pushed to that text field on page 31. So basically what I want is that if I click on one of the tabs in the Navigation Menu that brings me to page 31, I want the application to look in my TEAMMEMBERS table, find my username in the "username" column and then look to see the value in the "P31_Access" column in the same row. If the value in "P31_Access" in that row is the same value of the text field page item on page 31, I am allowed access. Otherwise, no access granted.

I know this seems like a strange way of handling accesses, but setting up authorization schemes for the individual components on the page will not work for my specific situation and need.

This is what I have so far, but it does not produce the result I am looking for.

SELECT 1
FROM TEAMMEMBERS
WHERE P31_ACCESS = :P31_ACCESS
AND upper(username) = upper(:APP_USER);

I'm sure there are many problems with my query, but I'm just not sure exactly how to write it. Does anyone have any ideas? I'm wondering if a PLSQL that return a boolean would be a better set up for this authorization scheme, but I'm not sure.

Thank you in advance.

What does "it does not produce the result I am looking for" mean? Have you loaded the page in debug mode and looked at the debug log? — eaolson
Well, for example, in the row in the TEAMMEMBERS table that contains my username if I leave the cell in the "P31_ACCESS" column blank or null then I still have access to page 31, even though the text field page item on page 31 actually contains a value. I only want to be able to have access to page 31 if the value in the cell in the TEAMMEMBERS table that I just mentioned contains the same value that is in that text field page item on page 31. How do I load the page in debug mode? I've never done that before. — Katherine Reed
For Debug mode, when you're logged in to the designer as a developer, run the page, there should be a toolbar at the bottom. Click Debug and the page will reload. Then click View Debug, which will pop up a window with the page views for which debugging information is available, then click the one that corresponds to your page load. You can also do this in the URL, see docs.oracle.com/database/121/HTMDB/concept_url.htm#HTMDB03017 — eaolson

eaolson eaolson · Accepted Answer · 2018-05-02T00:57:01

My first guess is that P31_ACCESS is not being set in session state. Since you're running a process not on your page, it needs to be set on the server and it needs to be set early on in the page generation process. So you'll probably need a Computation at the Before Header step to set its value.

Make sure your authorization process is not Once Per Session. You probably want Once Per Page View.

Based on your description it also sounds like you want your query to be this, and make sure it is a Exists SQL Query type:

SELECT 1
FROM TEAMMEMBERS
WHERE ( P31_ACCESS = :P31_ACCESS OR P31_ACCESS IS NULL )
AND upper(username) = upper(:APP_USER);

Oracle Apex authorization scheme based on page item value

1 Answers