Using the ORDS Prehook Function

Introduction

​A new configuration file parameter showed up in Oracle REST Data Services (ORDS) version 18.3. The parameter is called ‘procedure.rest.preHook’. Whilst this may seem like a pretty innocuous change, I believe it opens up a number of new possibilities for ORDS, which I will explore in this post.

---

​Understanding the Parameter

​The parameter essentially allows you to identify a PL/SQL procedure which is called by ORDS prior to every web service call in a particular connection pool. This includes GET, POST, PUT and DELETE services. This gives you a spot where you can execute any code you want just before (and in the same database session as) every web service. ORDS expects the function to return a Boolean, and it will automatically return a HTTP status of 403 ‘forbidden’ and stop the execution of the handler code if the function returns a value of false.
 
Per the documentation, the parameter ‘procedure.rest.preHook’ : “Specifies the function to be invoked prior to dispatching each Oracle REST Data Services based REST Service. The function can perform configuration of the database session, perform additional validation or authorization of the request. If the function returns true, then processing of the request continues. If the function returns false, then processing of the request is aborted and an HTTP 403 Forbidden status is returned.”

ORDS Configuration

Adding the parameter to the ORDS parameter file is pretty straight forward. Add the following entry to your defaults.xml (or add it to a specific connection pool if applicable):
<entry key="procedure.rest.preHook">xxrest.rest_utl.pre_hook</entry>
 
Note: The above entry assumes I have a package called rest_utl with a function called pre_hook, which I created in the schema xxords. Here is the package specification for the above example:

​Grants
The procedure needs to be executable from all schemas that your ORDS services are run from. For example, if you create a Prehook function called REST_UTL_PK.PRE_HOOK in the schema XXORDS, and you are running ORDS services from schemas XXORDS and XXREST, then you must grant execute on XXORDS.REST_UTL_PK.PRE_HOOK to the schema XXREST. If you do not, you will get the following error when running services from XXREST:

​Use Case 1 – Table Logging Calls to ORDS Services

​Logging ORDS activity to a database table is probably the subject of a longer post. In short, there are two primary reasons you may want to log invocations of your ORDS services. The first is to track usage. Knowing what methods and resources are being utilized is extremely helpful. You can keep track of which services are and are not being used, which clients are using which services etc.
 
The second reason is to track, and trouble shoot errors that occur during web service execution. Obviously, the Prehook function won’t help us with logging errors raised by our web services because at the time it is called, the service has not yet run. It can, however, be used to perform all of our usage logging.
 
In some cases, we could already do this, by adding the log table insert to our handler logic. This did not, however, work for GET services where our handler was a SQL statement.
 
Let’s look at how we could go about adding table usage logging for all our ORDS services using the Prehook function.

​First you will need to create a logging table with columns that can capture information about the service invocation. Let’s consider the following table called rest_log:

​This table is by no means comprehensive but serves the purpose for this example. Our Prehook function to populate this table could look something like this:

​That’s not a lot of code to log every ORDS web service call!
 
You may notice in the above example that I am tracking a custom HTTP Header ‘X_USER_NAME’. This is to illustrate that you may want to introduce custom http headers so that you can track additional attributes about the client.

​Use Case 2 – Setting Session Context

​Setting database session context is a particularly good use of this parameter. Let’s say you are operating in an Oracle e-Business Suite (EBS) environment. Each call to a web service needs to set the EBS session context based on the user and responsibility. This ensures the correct user and operating unit is used when querying data or calling EBS public APIs to create and update records. This is typically performed using the EBS API fnd_global.apps_initialize.
 
Before now, we could do this for POST services by including a call to fnd_global.apps_initialize at the start of the handler logic. This was not possible, however, for a GET based on a SELECT statement. This meant we had to build GET services using a PL/SQL block and emit the JSON manually using APEX_JSON.
 
The Prehook function allows us to call fnd_global.apps_initialize at the start of the session and have that context applied when the web service is called. This is much cleaner.

Of course, there are use cases outside of the EBS world where you need to set session information especially if you are using VPD (Virtual Private Database) or something similar to perform row-based security on your data.

Use Case 3 – Custom Authorization

​As the documentation suggests, the primary use for the Prehook function is to perform custom authorization. All you have to do is have the function return false, and ORDS will automatically return a 403 ‘Forbidden’ HTTP response code to the caller. If you use a custom token for authorization, you can use this function to verify the token, user name and password etc.

For example, you may want client X to have access to the GET and POST handler for a resource but not the DELETE handler. In this example, you can use the ‘REQUEST_METHOD’ CGI environment variable to determine the method (GET, POST, PUT or DELETE) and a combination of ‘PATH_INFO’ and ‘SCRIPT_NAME’ to determine the resource. Assuming you pass an identifier for the client in a http header, based on these values you can then decide if the call should be allowed or not.
 
I still recommend you use ORDS standard OAUTH token security as your primary method of authorization. If, however, the client cannot support OAUTH, or you need to supplement standard OAUTH with some additional checks, the Prehook function is a great option.

Feature Request

​I would also like to see a postHook function parameter, this would hopefully give developers access to the resultant http status and allow us to log the final status of each web service call. Ideally, it would also have access to the :body_text and response payloads. This would allow developers to implement a complete table logging solution for ORDS.

Conclusion

​The Prehook function capability is a well thought out feature that will be a big help to ORDS web service developers. Hopefully this post gave you some ideas on how it could be used, although I have no doubt there are many more uses cases out there.

There is an entire section of the ORDS documentation dedicated to this feature which includes examples.
 
P.S. It will be interesting to see if and how the ORDS development team plans to expose parameters like these to multi-tenant PaaS offerings such as Exadata Express. This may prove a challenge given the parameter is currently set in the parameter file on the server.