Can OAUTH2 be used to Authenticate ORDS REST Enabled SQL?

User_T2LL6

Can OAUTH2 be used to Authenticate ORDS REST Enabled SQL (for use with APEX)? Our REST enabled SQL only works with basic auth. When we create an OAUTH2 client, assign it our ords database role, and give it the SQL Developer privilege, but it won't work.

We use OAUTH2 Authentication for our REST web service APIs developed in ORDS, so this is puzzling. The documentation for REST enabled SQL seems conflicting. Any help would be appreciated.

Paavo

Hi, its friendly to edit your profile so that you have prettier nick :)

https://docs.oracle.com/en/database/oracle/application-express/21.2/htmdb/rest-enabled-sql-creating.html#GUID-0906921E-AF79-49D4-B909-1C090F805D9D

https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/21.1/qsord/get-started-with-oracle-rest-data-services.html

Maybe you find these helpful, at least oracle-base is worth of bookmarking:

https://oracle-base.com/articles/misc/oracle-rest-data-services-ords-authentication

https://oracle-base.com/articles/misc/oracle-rest-data-services-ords-custom-authentication-schemes

And https://www.youtube.com/watch?v=iT-ehrNKq_M

And see this too :)

https://www.jmjcloud.com/blog/simplifying-apex_web_service-oauth2-client-credentials

rgrds Paavo

User_T2LL6

Hi Paavo,

You're right; I should update my username. I updated it in my profile under Community display name, but it is not displaying the updated value. My new username is: WHEN rachel THEN. 😂

I should clarify my question. I was able to get it to authenticate successfully in APEX using OAUTH2, but it showed no tables. When I tried to run a SQL query it gave me ORA-20001 which was something about the wrong method for the call I think. I would assume it is calling the wrong handler. When I use basic auth for it then everything works fine. When I use my ORDS APIs OAUTH2 clients work fine. I know the manual says it is supposed to work, but I was asking in general if anyone was able to get it working or had any issues.

Thank you so much for helping me, Paavo. I have read the first four of those articles, but I will take a look at the last one and the video. Maybe there is some trick to get it working or maybe it has a WHEN rachel THEN exception. A possible step would be to review the documentation and find out which API it is calling, read the code if it isn't wrapped, and try create a manual workaround.

All the best,

Rachel

Paavo

Hi Rachel,

it is easy to get confused, especially if you have several authentication methods for various url's in your setup.

Can you share setups versions ords, apex, db and the code to used to create example api with oauth2 authentication?

Do you have a separate httpd frontend before ords doing some url rewrites or so?

Did you try with curl that api works?

Does your apex have the necessary network acl's to access the api's?

What you mean with "it showed no tables" ?

rgrds Paavo

ps. it is wise to have the most recent versions in place for Apex and ORDS