I love APEX. At least, 99% of the time that is. Sometimes, though, things just don't work the way you expect them to, and then things get…confusing. Last week, I ran into one of those moments, and although I found a workaround, I don't understand why what I tried didn't work.
First, the problem I'm trying to solve: We use Oracle E-Business Suite for our ERP data, but there are many people at the office (generally, people working out on the manufacturing floor) who don't have Oracle accounts because they don't need them. So, most of our tools will be using Oracle EBS authentication so we can properly populate the “who did what when” columns. But we're also going to be building applications which aren't specific to EBS, and which people without EBS accounts may need to use. So, for those, we're using their Windows AD accounts. And this presents a problem: do we maintain two different login pages and application menus, or can APEX be told which authentication method to use dynamically?
I didn't want to maintain two different menus, as I felt that would just be confusing to the users, so I built a table that maps applications to the correct authentication method. Our central login page then calls a single process which looks to see where the user is trying to go, and authenticates properly based on that. Well and good. If the user switches applications and they land in one that uses the same authentication method, they stay logged in, as you'd expect. The problem comes up when they switch to a different authentication method–we need to catch that, log them out, and take them to the login screen.
My first attempt was to create a “page sentry” function. Unfortunately, there doesn't seem to be much documentation on this. I started by creating a table to map the current session ID to the authentication method used and the current username; if either of those don't match, the session should be invalidated. Easy, right? Unfortunately, no. The big problem I ran into–and the one I don't understand–is that I could not consistently get the current username. I tried APEX_APPLICATION.G_USER, APEX_CUSTOM_AUTH.GET_USER(), APEX_CUSTOM_AUTH.GET_USERNAME(), and V('APP_USER'), but none of them worked all the time. The best was GET_USERNAME(), as it returned a non-null value more often than the others, but it still returned null or 'nobody' to my sentry function regularly, even when I was properly logged in. And failing to check the logged-in username would just be a security hole, so I kept looking.
My second attempt was to create a branch on page 0 that would conditionally route the user back to the login page. Turns out, though, that even though you can create branches on page 0, they don't actually do anything.
Then I found this (four-year old) post on Scott Spendolini's blog, which gave me an idea for a third route to attempt. I created an Application Process that runs before headers with the same condition* my page 0 branch had. Here's the code for the process:
htp.init; owa_util.redirect_url('wwv_flow_custom_auth_std.logout?p_this_flow=10000&p_next_flow_page_sess=&APP_ID.:1'); apex_application.g_unrecoverable_error := true;
Very basic. If the condition fires, stop everything and pop over to the login app (100000); once the user authenticates, bring them back to page 1 of this application. And it works, every time.
* The condition checks APEX_APPLICATION.G_USER and APEX_APPLICATION.G_INSTANCE (session). No need to use the APEX_CUSTOM_AUTH wrapper functions.