We are after a little bit of product knowledge and best use of ORDS and connecting to the database.
We have an existing simple Client Server application, with an Oracle database back end, with users connecting under their account with database authentication.
We are working on a modernization program of our application using new technologies, but we wish to maintain the existing Oracle database back end, as we have a lot of intrinsic value in our back end database that is critical to our business.
The new application is being developed with React UI and .NET middleware, which will issue REST API calls. We’ve identified ORDS as a key element to the stack as a way to consume the APIs and convert to database actions. User authentication would be via the use of Entra.
One key element to our back end database is the existing use of Virtual Private Database. This is currently based on the SESSION_USER context.
We have found documentation that has pointed us towards the Pre Hook function to set session context, but it appears that this does not extend to setting SESSION_USER (or USER, or CURRENT_USER) which is what our existing functions work off, so would need a re-engineering of our VPD logic. There is also other functionality that would require re-engineering (eg. updating “Last Amended” columns based on USER)
Our more technical colleagues have asked the question of ChatGPT, which suggested Proxy Authentication as a potential solution to allow connectivity as individual user accounts, but it doesn’t supply a working example, and we are unsure whether this is a feasible option with ORDS.
We’ve noted it looks like ORDS already does a proxy connection via ORDS_PUBLIC_USER to the account that we’ve enabled and are setting up the module/template/handler endpoints under. We’ve used this to try out a proof of concept, where we have enabled the schema for multiple user accounts, and duplicated the same endpoints to each of them. It works, but does feels like it would be impractical to maintain and I’m not sure that it should be our long term solution.
What are our options? Would the concept I just described be feasible in a production system with several hundred user accounts? Is Proxy Authentication an option and if so where do we look for guidance? Or should we be looking at the PreHook function setting a context, and re-engineering our existing functionality?
Regards Iain.