Thanks to John Jacob who helped me understand how to call OAuth2 from Postman.
Issue:
With APEX REST API, any operation is exposed internally or externally if not properly secured. APEX REST API could be secured using OAuth2 however if used from Clover ETL it requires extra steps.
Solution:
This article explains how to get the OAuth token and use it from Clover ETL to get and post requests to Oracle APEX REST API.
Resources:
- https://www.cloverdx.com/
- https://apex.oracle.com/en/
- https://oracle-base.com/articles/misc/oracle-rest-data-services-ords-authentication#oauth2-client-credentials
- https://blog.cloverdx.com/accessing-google-api-google-analytics-using-cloveretl
Clover Side
To call a secured REST API (Oracle APEX in my case) by OAuth, an initial token call should be made, the token extracted and then the next Http requests could be made.
Here are the few components to perform these:
- Reader > Data Generator to simply initiate the workflow
- Transformer > Reformat
- Transform attribute:
function integer transform() {
$out.0.grant_type = “client_credentials”;
return ALL;
}
This provides the grant type method used by OAuth. The metadata that outputs from this is simply one string grant_type.
- Others > HTTPConnector: this will push the Client Id and Secret to the OAuth service and get the token.
- URL: the base path of your APEX module, adding /oauth/token
- Request method: POST
- Add input fields as parameters: true
- Send parameters in: Body
- Output mapping:
function integer transform() {
$out.0.content = $in.1.content;
return ALL;
}
The metadata that outputs from this component is simply one string content.
- Authentication Method: HTTP_BASIC
- Username: ${CLIENT_ID}
- This variable is defined in a parameter file in my case. It could also be hardcoded here.
- Password: <your client secret>
- Readers > JSONExtract: this will extract from the content string the OAuth token
- File Url: port:$0.content:discrete
- Mapping (Source Tab):
<Mappings>
<Mapping element=“json_object” outPort=“0”
xmlFields=“{}access_token”
cloverFields=“access_token”>
</Mapping>
</Mappings>
Again, the output metadata is simple a string access_token.
- SimpleCopy and UniversalDataReader are 2 components used to write the token down into a local file in data-out directory
- Others > HTTPConnector: this will do the standard request to the APEX REST API, passing the token as parameter.
- URL: the path to your APEX REST API
- Request method: GET or POST
- Add input fields as parameters: false
- Send parameters in: QUERY (for GET)
- Input mapping:
function integer transform() {
map[string,string] headers;
headers[“Authorization”] = “Bearer ” + $in.0.access_token;
$out.0.additionalHTTPHeaders = headers;
return ALL;
}
The remaining setup will depend on your requirements.
APEX Side
In APEX, it’s easy to protect the REST API from the user interface. You can define a privilege to protect your REST API resources as:
Select at least one role that will be used later in the security setup.
Once this is defined, your module will appear with a green checkmark:
To get the client ID and secret, you need few extra steps described in this article in the SQL Commands part of APEX:
- Create a client with the grant type of “client_credentials”, enter in p_privilege_names the privilege you’ve created in APEX (See above)
OAUTH.create_client(
p_name => ‘Emp Client’,
p_grant_type => ‘client_credentials’,
p_owner => ‘My Company Limited’,
p_description => ‘A client for Emp management’,
p_support_email => ‘tim@example.com’,
p_privilege_names => ’emp_priv’
);
- Associate the client with the role that holds the correct privileges for the resources it needs to access.
OAUTH.grant_client_role(
p_client_name => ‘Emp Client’,
p_role_name => ’emp_role’
);
- Get the client ID and secret from this SQL:
SELECT id, name, client_id, client_secret
FROM user_ords_clients;
Thomas' Blog 