Some rules apply to every REST call: log it, check a maintenance switch, or reject requests from somewhere. Instead of repeating that code in every handler, ORDS can call one PL/SQL function before each REST request: the pre-hook. When it returns TRUE the request goes on; when it returns FALSE, ORDS answers 403 Forbidden. This guide logs every call and adds a maintenance mode.
Before You Start
You need ORDS installed and running against your database, and a schema to work in. The examples use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder of the Oracle Database 26ai code repository on GitHub. The schema comes from Oracle Database 26ai SQL and PL/SQL Book.
ORDS in the examples answers at https://localhost:8443/ords/, and NIMBUS is REST-enabled with the URL alias nimbus. Replace the host and port with your own ORDS address. The curl commands use -k because the test server has a self-signed certificate; leave it out when your certificate is trusted. JSON responses are formatted for reading; ORDS returns them on one line.
Setting the pre-hook changes the ORDS configuration and needs an ORDS restart, so try it on a test installation first.
Syntax
create or replace function schema.hook_name return boolean is ... end; ords config --db-pool default set procedure.rest.preHook schema.hook_name ords config --db-pool default delete procedure.rest.preHook
Inside the function, OWA_UTIL.GET_CGI_ENV reads request details such as REQUEST_METHOD, SCRIPT_NAME, PATH_INFO, and QUERY_STRING.
The Hook Function
API_GATE writes each request to API_LOG in an autonomous transaction, so the log entry stays even if the request fails, and refuses requests while API_SWITCH says maintenance.
Example:
create table api_log (
logged_at timestamp default systimestamp,
method varchar2(10),
path varchar2(400),
allowed varchar2(1)
);
create table api_switch (maintenance varchar2(1) default 'N' not null);
insert into api_switch values ('N');
commit;
create or replace function api_gate return boolean is
pragma autonomous_transaction;
v_maintenance api_switch.maintenance%type;
begin
select maintenance into v_maintenance from api_switch;
insert into api_log (method, path, allowed)
values (owa_util.get_cgi_env('REQUEST_METHOD'),
owa_util.get_cgi_env('SCRIPT_NAME') || owa_util.get_cgi_env('PATH_INFO'),
case v_maintenance when 'Y' then 'N' else 'Y' end);
commit;
return v_maintenance <> 'Y'; -- FALSE stops the request with 403
end;
/SCRIPT_NAME holds the URL up to the template and PATH_INFO the rest; a first version that logged only PATH_INFO recorded paths such as /7 and /summary.
Register the Hook
Run the ORDS command line with your configuration folder, then restart ORDS:
Example:
ords --config /path/to/ords-config config --db-pool default set procedure.rest.preHook nimbus.api_gate
Output:
The setting named: procedure.rest.preHook was set to: nimbus.api_gate in configuration: default
Calls Are Logged
After the restart, two ordinary calls succeed as before. Then maintenance is switched on, and the next call is refused:
Example:
update api_switch set maintenance = 'Y'; commit;
Output (HTTP 403 Forbidden):
{
"code": "Forbidden",
"title": "Forbidden",
"message": "The user is not authorized to access the requested resource ",
"type": "tag:oracle.com,2020:error/Forbidden",
"instance": "tag:oracle.com,2020:ecid/5at2ofxpcz4kFgK9fbqx9w"
}With maintenance off again, calls succeed. The log shows all four requests:
Example:
select to_char(logged_at, 'HH24:MI:SS') as at, method, path, allowed from api_log order by logged_at;
Output:
AT METHOD PATH ALLOWED ___________ _________ ________________________________________ __________ 04:33:09 GET /ords/nimbus/network/routes/7/summary Y 04:33:09 GET /ords/nimbus/airports/ Y 04:33:11 GET /ords/nimbus/network/routes/7/summary N 04:33:13 GET /ords/nimbus/network/routes/7/summary Y
Remove the Hook
Example:
ords --config /path/to/ords-config config --db-pool default delete procedure.rest.preHook
Output:
The setting named: procedure.rest.preHook was removed from configuration: default
Restart ORDS again for the change to take effect.
Things to Know
- The hook runs for every REST request of the database pool, so keep it fast, and make sure it never raises an error.
- Every REST-enabled schema must be able to execute the function: grant EXECUTE on it to those schemas.
- Restart ORDS after setting or removing the hook.
Related Guides
Conclusion
An ORDS pre-hook runs one PL/SQL function before every REST request: TRUE lets the request through, FALSE returns 403. Use it for logging and global switches, register it with procedure.rest.preHook, restart ORDS, and keep the function fast and error-free.
