Creating and publishing RESTful APIs from the database is pretty common and fairly simple with Oracle REST Data Services (ORDS). In most cases those exposed APIs would be protected with an API Gateway or the built-in protection of ORDS.
If you are unfamiliar with ORDS, check out the About Oracle REST Data Services as it fronts the APEX environment in all deployments and architectures.
In a current project my customer wanted to secure the services but wanted the option to have some public and others secured. Of course we could use separate modules to achieve this but both of us prefer to have all related APIs under the same module as per the documentations → Resource module: An organisational unit that is used to group related resource templates.
We’ll get into what that is in a bit but the end goal is to only protect the URIs that we want.
About this Post
Today’s post we’ll explore the configuration and pitfalls of setting up resource protection in ORDS.
Creating Database RESTful Services
APEX developers may create REST services via the APEX RESTful Services
or navigate to SQL Web Developer REST option
The typical ORDS developer however may prefer using the ORDS PL/SQL package or options in SQL Developer.
There are many examples out there but the main steps are:
REST Enable the Schema with a schema alias or its URL mapping pattern
Create a Module and it’s base URI
Create Templates and its URI pattern
Lastly, create Handlers with the operations code to be executed
All of these will combine to become the REST API’s endpoint as defined in the RESTful Services Terminology
https://<HOSTNAME:PORT>/<CONTEXT>/<DATABASE_SCHEMA_ALIAS>/<MODULE_BASE_URI>/<TEMPLATE_URI>/
Here is a sample
BEGIN
ORDS.ENABLE_SCHEMA(
p_enabled => TRUE,
p_schema => 'APPSAMPLS', -- Update to match local schema
p_url_mapping_type => 'BASE_PATH',
p_url_mapping_pattern => 'APPSAMPLS', -- Update to match local schema
p_auto_rest_auth => FALSE);
ORDS.DEFINE_TEMPLATE(
p_module_name => 'priv_test',
p_pattern => 'client/regions',
p_priority => 0,
p_etag_type => 'HASH',
p_etag_query => NULL,
p_comments => NULL);
ORDS.DEFINE_HANDLER(
p_module_name => 'priv_test',
p_pattern => 'client/regions',
p_method => 'GET',
p_source_type => 'json/collection',
p_mimes_allowed => NULL,
p_comments => NULL,
p_source =>
'begin
OWA_UTIL.PRINT_CGI_ENV;
End;');
ORDS.DEFINE_TEMPLATE(
p_module_name => 'priv_test',
p_pattern => 'event/speakers',
p_priority => 0,
p_etag_type => 'HASH',
p_etag_query => NULL,
p_comments => NULL);
ORDS.DEFINE_HANDLER(
p_module_name => 'priv_test',
p_pattern => 'event/speakers',
p_method => 'GET',
p_source_type => 'json/collection',
p_mimes_allowed => NULL,
p_comments => NULL,
p_source =>
'begin
OWA_UTIL.PRINT_CGI_ENV;
End;');
ORDS.DEFINE_TEMPLATE(
p_module_name => 'priv_test',
p_pattern => 'client_regions',
p_priority => 0,
p_etag_type => 'HASH',
p_etag_query => NULL,
p_comments => NULL);
ORDS.DEFINE_HANDLER(
p_module_name => 'priv_test',
p_pattern => 'client_regions',
p_method => 'GET',
p_source_type => 'json/collection',
p_mimes_allowed => NULL,
p_comments => NULL,
p_source =>
'begin
OWA_UTIL.PRINT_CGI_ENV;
End;');
COMMIT;
END;
Securing ORDS RESTful Services
When Configuring Secure Access to RESTful Services it is essential to understand that the end goal is to protect a resource that has a unique URI. Resources are protected by a defined privilege which is granted to a Role which in turn are assigned to Users.
ORDS users are typically not database users or APEX accounts, but ORDS clients managed by the application server.
ORDS does provide a File Based repository (for testing purposes, not recommended for production use)and the procedure to create OAuth Clients.
Create a Role
ORDS provides a straight forward procedure toe create Roles, the only requirement is the name
ORDS.CREATE_ROLE(p_role_name => 'Client Services Role');
Creating a Privilege
Creating a privilege is more involved as it contains references to the role, modules, and/or patterns we want to protect.
-- Format to protect modules and patterns
ORDS.DEFINE_PRIVILEGE(
p_privilege_name IN sec_privileges.name%type,
p_roles IN owa.vc_arr,
p_patterns IN owa.vc_arr,
p_modules IN owa.vc_arr,
p_label IN sec_privileges.label%type DEFAULT NULL,
p_description IN sec_privileges.description%type DEFAULT NULL,
p_comments IN sec_privileges.comments%type DEFAULT NULL);
-- or only patterns
ORDS.DEFINE_PRIVILEGE(
p_privilege_name IN sec_privileges.name%type,
p_roles IN owa.vc_arr,
p_patterns IN owa.vc_arr,
p_label IN sec_privileges.label%type DEFAULT NULL,
p_description IN sec_privileges.description%type DEFAULT NULL,
p_comments IN sec_privileges.comments%type DEFAULT NULL);
-- or only the privilege definition
ORDS.DEFINE_PRIVILEGE(
p_privilege_name IN sec_privileges.name%type,
p_roles IN owa.vc_arr,
p_label IN sec_privileges.label%type DEFAULT NULL,
p_description IN sec_privileges.description%type DEFAULT NULL,
p_comments IN sec_privileges.comments%type DEFAULT NULL);
The Roles, Modules and Patterns are arrays of values, so a single privilege can be granted to many roles, protecting many modules or patterns.
Pattern Matching Explained
Patterns are used to match URIs using the following format: base path**[template uri][wildcard]**. The Template URI and Wildcard (* - asterisk) are optional.
After reading the documentation you could think that any regular expression or wildcard placement could be used. This is not the case, it simply states the p_patterns is a list of patterns of varchar array and the examples only provide a list of patterns all ending with ‘/*’.
Following the format provided, let’s use the sample we created
Module: priv_test: base path = /priv_test/
template: event/speakers
template: client/regions
template: client_regions
Matching patterns
Pattern to match all templates under the priv_test would be /priv_test/*
Pattern to match only the client_regions would be /priv_test/client_regions
Pattern to match and template uri that starts with client would be /priv_test/client/*
Let’s continue with the sample, if we create this privilege it will protect these resource patterns:
/priv_test/client/*
/priv_test/client_regions
DECLARE
l_roles OWA.VC_ARR;
l_modules OWA.VC_ARR;
l_patterns OWA.VC_ARR;
BEGIN
l_roles(1) := 'Client Services Role';
l_modules(1) := 'priv_test';
l_patterns(1) := '/priv_test/client/*';
l_patterns(1) := '/priv_test/client_regions';
ORDS.DEFINE_PRIVILEGE(
p_privilege_name => 'client.services.privilege',
p_roles => l_roles,
p_patterns => l_patterns,
p_modules => l_modules,
p_label => 'client Reference Data',
p_description => 'Provides the ability to read, client reference data',
p_comments => NULL);
l_roles.DELETE;
l_modules.DELETE;
l_patterns.DELETE;
COMMIT;
END;
The event/speakers will be open for anyone to access the API.
Pitfalls
APEX developers creating RESTful Services in AppBuilder, the add pattern currently does not support underscores. If your naming convention does not include them, then no worries, otherwise, use the ORDS SQL Developer Web of APIs to create the privilege protecting a resource.
Patterns must include the full URI match from the starting Base Path including the starting forward slash. In my test, attempting to match parts of the URI yielded unexpected results and the URI was unprotected.
Using ‘client/*’ or even ‘priv_test/* as a pattern will not match as it is only a part of the whole URI.
The wildcard is placed after the slash, '/', this will match anything after the slash but attempting to use this to match all templates starting with client like /priv_test/client* does not work and results in a HTTP 500 .
The use of the wildcard is limited and must be preceded by a forward slash, , '/*'. The procedure and Web interface will allow for the placement of the wildcard anywhere in the pattern such as: ‘/*/client/*’ as this is a valid string.
Create an OAuth Client
The ORDS documentation Tutorial: Protecting and Accessing Resources still references the OAUTH PL/SQL package but this has been deprecated as of ORDS release 24.3 and due to be desupported by October 2025.
It has been replace by ORDS_SECURITY PL/SQL Package and the ords_security.register_client should be used instead of the oauth.create_client procedure.
The signatures are similar but one key difference is that the client secret is not generated in the new procedure. If we use the older package we get both the client id and client secret.
Using OAUTH.CREATE_CLIENT
BEGIN
OAUTH.CREATE_CLIENT(
p_name => 'MyClient1',
p_grant_type => 'client_credentials',
p_owner => 'REST.USER',
p_support_email => 'myclient1.user@oracle.com',
p_description => 'Invoke APEX CICD Agent Rest APIs',
p_privilege_names => 'client.services.privilege',
p_origins_allowed => '',
p_redirect_uri => NULL,
p_support_uri => 'https://apex.oracle.com',
p_token_duration => NULL,
p_refresh_duration => NULL,
p_code_duration => NULL
);
COMMIT;
ORDS_SECURITY.GRANT_CLIENT_ROLE(
p_client_name => 'MyClient1',
p_role_name => 'Client Services Role'
);
COMMIT;
END;
select name, client_id, client_secret from user_ords_clients where name ='MyClient1';
/* To Delete the client
BEGIN
OAUTH.DELETE_CLIENT(
p_name => 'MyClient1);
END;
*/
OR
Using ORDS_SECURITY.REGISTER_CLIENT
DECLARE
l_client_cred ords_types.t_client_credentials;
BEGIN
l_client_cred := ORDS_SECURITY.REGISTER_CLIENT(
p_name => 'MyClient2',
p_grant_type => 'client_credentials',
p_support_email => 'myclient2.user@oracle.com',
p_description => 'Invoke APEX CICD Agent Rest APIs',
p_privilege_names => 'client.services.privilege',
p_origins_allowed => '',
p_redirect_uri => NULL,
p_support_uri => 'https://apex.oracle.com',
p_token_duration => NULL,
p_refresh_duration => NULL,
p_code_duration => NULL
);
ORDS_SECURITY.GRANT_CLIENT_ROLE(
p_client_name => 'MyClient2',
p_role_name => 'Client Services Role'
);
COMMIT;
sys.dbms_output.put_line('Client ID:' || l_client_cred.client_key.client_id);
sys.dbms_output.put_line('Secret:' || l_client_cred.client_secret.secret);
sys.dbms_output.put_line('slot:' || l_client_cred.client_secret.slot);
sys.dbms_output.put_line('Issued On:' || l_client_cred.client_secret.issued_on);
END;
-- Client is registered but does not have a secret
-- Use rotate client secret to generate one
DECLARE
l_client_cred ords_types.t_client_credentials;
BEGIN
l_client_cred.client_key.name := 'MyClient2';
l_client_cred := ORDS_SECURITY.ROTATE_CLIENT_SECRET(
p_client_key => l_client_cred.client_key
);
-- No Commit Required
sys.dbms_output.put_line('Client ID:' || l_client_cred.client_key.client_id);
sys.dbms_output.put_line('Secret:' || l_client_cred.client_secret.secret);
sys.dbms_output.put_line('slot:' || l_client_cred.client_secret.slot);
sys.dbms_output.put_line('Issued On:' || l_client_cred.client_secret.issued_on);
END;
/* To Delete the Client
DECLARE
l_client_cred ords_types.t_client_credentials;
BEGIN
l_client_cred.client_key.name := 'MyClient2';
ORDS_SECURITY.DELETE_CLIENT(
p_client_key => l_client_cred.client_key
);
END;
*/
In Conclusion
If all goes well and I’ve not made many typos, you can run the tests, validating the patterns that work, and those that do not.
The patterns are straight forward and simple to construct but do not support complex matching syntax.
There are some pitfalls but these would be identified quickly while running tests.
If you have any feedback, similar or different experiences, let me know, I’d like to test out those scenarios as well.