Introduction

In version 18.3, Oracle REST Data Services (ORDS) introduced a new configuration file parameter called procedure.rest.preHook. This parameter allows developers to identify a PL/SQL procedure that ORDS calls before every web service call in a particular connection pool. This post will describe how to use this parameter and several reasons why you might find it helpful.

What Does an ORDS PreHook Do?

Once configured, every REST API call is routed to the PreHook function after authentication but before the REST API is executed. If the PreHook function returns TRUE, then ORDS continues with executing the REST API; if the function returns FALSE, then ORDS returns an HTTP 403 ‘forbidden’ error.

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.”

Configuration

From the command line on the ORDS server, you can set up the PreHook function as follows.

ords --config ${ORDS_CONFIG} config set procedure.rest.preHook demo.ords_utl_pk.pre_hook

This adds the following entry in the file: ords_config/databases/default/pool.xml

<entry key="procedure.rest.preHook">demo.ords_utl_pk.pre_hook</entry>

The package spec looks like this:

CREATE OR REPLACE PACKAGE "ORDS_UTL_PK" AS 

-- ORDS Pre-Hook Function.
FUNCTION pre_hook RETURN BOOLEAN;

END XXAZZ_ORDS_PK;

The package body looks something like this:

CREATE OR REPLACE PACKAGE BODY ORDS_UTL_PK AS 

FUNCTION pre_hook RETURN BOOLEAN IS

  l_method   VARCHAR2(10)  := owa_util.get_cgi_env('REQUEST_METHOD');
  l_api_path VARCHAR2(500) := owa_util.get_cgi_env('X-APEX-PATH');

BEGIN

  IF l_method = 'GET' AND l_api_path LIKE 'module1/%/orders%' THEN
    -- Code for specific URL path gooes here.
    -- Retun FALSE to block the request.
    -- Call from any other paths (e.g. SQL Developer Web) are ignored.
  END IF;

  RETURN TRUE;

EXCEPTION WHEN OTHERS THEN
  -- Log error and notify admin here.
  RAISE;
END pre_hook;

END ORDS_UTL_PK;

Grants

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

From APEX 24.1 onward, you must also issue a grant to APEX_PUBLIC_ROUTER. Otherwise, SQL Developer Web will stop working!

GRANT EXECUTE ON DEMO.ORDS_UTL_PK TO APEX_PUBLIC_ROUTER;

Use Cases

Logging Calls to ORDS Services

​Logging ORDS activity to a database table is probably the subject of a longer post. In short, you may want to log invocations of your ORDS services to track usage. Knowing what methods and resources are being utilized is extremely helpful. You can track which services are not being used, which clients are using which services, etc.

In some cases, we could already do this by adding the log table insert to our ORDS handler logic. This does not, however, work for GET services, where your handler was an SQL statement.

Let’s look at how we could go about adding usage logging to a table for all our ORDS services using the PreHook function.

​First, create a logging table with columns to capture service invocation information. Let’s consider the following table called rest_log. In this example, we will capture all the valuable information in a JSON document stored in a CLOB column.

CREATE TABLE REST_LOG
 (log_id     NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY CONSTRAINT REST_LOG_PK PRIMARY KEY,
  cgi_vars   CLOB CONSTRAINT REST_LOG_JSON CHECK (INTERNAL_JSON IS JSON),
  log_date   TIMESTAMP WITH LOCAL TIME ZONE NOT NULL)

Our PreHook function to populate this table could look something like this:

FUNCTION pre_hook RETURN BOOLEAN IS

  l_method   VARCHAR2(10)  := owa_util.get_cgi_env('REQUEST_METHOD');
  l_api_path VARCHAR2(500) := owa_util.get_cgi_env('X-APEX-PATH');
  l_obj      json_object_t := json_object_t();  

BEGIN

  IF l_method = 'GET' AND l_api_path LIKE 'module1/%/orders%' THEN
    -- Capture the CGI Environment, Headers, etc. in a JSON document.
    FOR i in 1 .. owa.num_cgi_vars LOOP
      l_obj.put(owa.cgi_var_name(i), owa.cgi_var_val(i));
    END LOOP;
    INSERT INTO rest_log (cgi_vars, log_date) 
      VALUES (l_obj.to_clob, current_timestamp);
    COMMIT;
  END IF;
  RETURN TRUE;

EXCEPTION WHEN OTHERS THEN
  -- Log error and notify admin here.
  RAISE;
END pre_hook;

​Capturing all of the CGI Environment variables gives you information about the web service that was called and all the HTTP headers and URL parameters passed to the REST API.

Setting Session Context

​Setting database session context is an excellent use of PreHooks. Let’s say you operate 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 are 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 or JSON_OBJECT.

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 the EBS world where you need to set session information, especially if you use VPD (Virtual Private Database) or something similar to perform row-based security on your data.

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.

For example, you may want client X to access 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 X-APEX-PATH to determine the resource/web service being called. If you use OAuth to authenticate your ORDS service, you can access the OAuth Client ID in the ORDS PreHook function using the X-ORDS-HOOK-USER HTTP header.

This gives you everything you need to determine if the client is allowed to call a particular method on a specific resource.

Feature Request

​I would also like to have the ability to configure a postHook function; this would ideally 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 is an excellent 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 use cases out there.

An entire section of the ORDS documentation is dedicated to this feature, which includes examples. There is also additional information available in the pre_hook folder contained in the ORDS download zip.

P.S. It will be interesting to see if and how the ORDS development team plans to expose parameters like these to the OCI Autonomous Database. This may prove a challenge, given the parameter is currently set in a configuration file on the server.