Skip to Main Content

ORDS, SODA & JSON in the Database

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

ORDS returning 301, and Catalina LockOutRealm.filterLockedAccounts errors.

Jon FinkeMar 8 2024

Running ORDS 22.3.0.r2781755 on PDB running Version 21.12.0.0.0. We recently upgraded from from APEX 22.2 to 23.2, but these problems may have predated that upgrade.

We have an ORDS app that returns a username to be disabled (as part of an adminstrative action). This is used by our Shibboleth server to stop automatic authentication/token renewals. There are other actions also triggered by the admin action (password change, Microsoft Tenant token refresh cleared) so this is not a critical function. When we first set this up, it worked - the rest call worked, return a JSON payload with a status code of 200 until all cases were handled, then it would return a 204.

The Resource handler is for a “GET”, and source type is PL/SQL. The handler Source is:

begin
   :status := Simon.Request_Disable_Json.get_next('ShibORDS');
end;

Status is set up as a bind OUT variable named “X-APEX-STATUS-CODE”

In the Get_Next function, if there is work to be done,

trace(login.json, process_result.debug_record);
htp.p(login.json);
return 200;

And once there are no more cases, that function does a “Return 204”.

Each REST call appears to generate 3 lines in the Tomcat logs -

128.113.X.X - - [07/Mar/2024:14:52:43 -0500] "POST /apex/simon_ords/oauth/token HTTP/1.1" 200 92
128.113.X.X - - [07/Mar/2024:14:52:44 -0500] "GET /apex/simon_ords/siem/next_disable HTTP/1.1" 301 5
128.113.X.X - - [07/Mar/2024:14:52:44 -0500] "GET /apex/simon_ords/siem/next_disable/ HTTP/1.1" 204 -

Which leads me to my first question - where is the 301 status coming from? My code doesn't generate it (not directly anyway). The last successful call (with a code of 200) was about 4 weeks BEFORE I upgraded APEX - I can't imagine we were not disabling accounts for 3 weeks (Spam, Physh are the most common reasons).

ORDS Clients - problem part 2

When first setting up ORDS, I created an app that allowed folks with the appropriate credential to create a Client assigned to a particular module. My underlying code would use ORDS.Define_Privilege, Oauth.Create_Client and poke around in the USER_ORDS_xxxx views to keep things organized. My target users are system admins who need a feed of data for their system from mine - somewhat limited, but solves a bunch of my problems. At present, I have about a dozen modules set up, each with their own “clients” - one or two per module.

This has worked pretty well - between my app providing some URLs and a few paragraphs of text, these folks have been able to get things online. But the above problem is very disturbing - things have stopped working (not all modules/handlers use that handler type), and the nature of the interfaces, might be overlooked for a while.

The other thing that has shown up in the catalina logs are lines like:

08-Mar-2024 00:09:03.389 WARNING [https-jsse-nio-8443-exec-39] org.apache.catalina.realm.LockOutRealm.filterLockedAccounts An attempt was made to authenticate the locked user [5pwuF_uZEr-QDlcqXWgPCw..]
08-Mar-2024 00:15:09.056 WARNING [https-jsse-nio-8443-exec-32] org.apache.catalina.realm.LockOutRealm.filterLockedAccounts An attempt was made to authenticate the locked user [AfMHdXSbC0QAYCzM8ZYViQ..]

Each of the “Clients” in [], are one of the clients set up via my tool. I am unable to find anything in the USER_ORDS_xxx views that would show a “locked” status, nor any way to “unlock” a client. I have gotten these errors for at least half of my “clients”, athough until this latest case, no one has mentioned anything. Not sure if this is clearing itself or not. Oh, apache-tomcat-9.0.71.

These errors definately predate the APEX upgrade.

Comments
Post Details
Added on Mar 8 2024
1 comment
133 views