ORDS-secure restful service using Command line (OAuth 2 : Authorization Code)

I was inspired by others when looking for this subject, but for me in very important to have a blog from a developer prospective and not only to show something which I not have test myself. I use my blog mostly as documentation for myself.

ORDS can be used to define database API’s. You can give third party “controlled” access to the database via REST-API’s. ORDS is a layer above your database, you can use it inside APEX or SQLDeveloper. I prefer using APEX because it has integrated ORDS GUI, which makes it easier developers to define API’s and also to secure it.
BASIC Authentication.

This part of security is not APEX related, it is ORDS related.
In this blog I will show how to define API’s in command line and also how to secure these API’s with OAuth2 method.  I have used Oracle documentation where this is described properly.
I assume your HR schema is enabled and the EMP table is there.
The first thing you need to do is enabling the ORDS for the HR schema.

 p_schema => 'HR',
 p_url_mapping_type => 'BASE_PATH',
 p_url_mapping_pattern => 'hr',
 p_auto_rest_auth => TRUE);

Define a basic webservice we can use for the test.

p_module_name => 'hrmodule',
p_base_path => 'api/hrschema/',
p_pattern => 'emp?{empno}',
p_method => 'GET',
p_source_type => ORDS.source_type_query,
p_source => 'SELECT * FROM emp WHERE empno = :empno',
p_items_per_page => 0);

You can test the service in POSTMAN by using the URL as GET method.


Response will be a JSON

"items": [
   "empno": 7788,
   "ename": "SCOTT",
   "job": "ANALYST",
   "mgr": 7566,
   "hiredate": "1987-04-18T22:00:00Z",
   "sal": 3000,
   "comm": null,
   "deptno": 20

Now we will secure the service, we will create a Role and privilege, them map these together.

– Create a ROLE

 p_role_name => 'hr_role' );

– Create a new privilege called “hr_priv”, which is associated with the role.

l_arr OWA.vc_arr;
l_arr(1) := 'hr_role';
ORDS.define_privilege (
p_privilege_name => 'hr_priv',
p_roles => l_arr,
p_label => 'HR Data',
p_description => 'Allow access to the HR data.');

To protect the web service, we associate the privilege directly to a URL pattern.

p_privilege_name => 'hr_priv',
p_pattern => '/hr/api/emp/*'


OAuth 2: Authorization Code

The authorization code flow is a three-egged process.

  • The user accesses a URL in a browser, which prompts for credentials
  •  The browser is redirected to a specified page with an authorization code as one of the parameters in the URL.
  •  The authorization code is used in a call to generate an access token


Create a client using the grant type of “authorization_code”. The redirect and support URLs are not real

p_name            => 'HR-client',
p_grant_type      => 'authorisation_code',
p_owner           => 'Tester',
p_description     => 'HR management client',
p_redirect_uri    => 'http://localhost:8080/ords/hr/redirect',
p_support_email   => 'mazin@example.com',
p_support_uri     => 'http://localhost:8080/ords/hr/support',
p_privilege_names => 'hr_priv');

You can get the client_id and client_secret

SELECT id, name, client_id, client_secret
FROM   user_ords_clients;

Associate the client with the role that holds the correct privileges for the resources it needs to access.

p_client_name => 'HR-client',
p_role_name   => 'hr_role'

Got to the browser with the below URL, replace endpoint and Client ID with yours.


You will receive the below message, you need to sign in.

You can create a ORDS use and add this to the Role by running this command where orgs.war file is.

java -jar ords.war user api_user hr_role

Login with the hr_user you created before and the approve the access.


After log in you need to approve the access. you will receive the 404 Not Found. this because the redirect page does not exist. From this step we need the URL only.

You need the code from the URL


With the code and Client ID plus the secret ID you can get a token. (You can use POSTMAN as well)

curl -i --user client_id:client_secret --data "grant_type=authorization_code&code=J0GYiIew9ZLMB6HUkjlvrA.." http://localhost:8080/ords/hr/oauth/token

The response will look like


from this point you are done. You can use the refresh token get a new token inside your external application, this after the token is expired within 3600 seconds.

curl -i --user client_id:client_secret--data "grant_type=refresh_token&refresh_token=8wnzi9WZ00CkkZnjzJLkug.." http://localhost:8080/ords/hr/oauth/token

If you have any question, please leave comment..


ORDS secure restful service (with SSL and without SSL)

With thank to Tim Hall from whom I learn a lot.  here is his detailed blog in this regard. https://oracle-base.com/articles/misc/oracle-rest-data-services-ords-authentication#

ORDS secure restful service (with SSL and without SSL)

Working with rest services is the way of building SOA environment with Oracle ApEx.  In this blog I will show step by step how to create a rest service and also how to secure this rest service using a token. (I’m using DHC chrome extension to test).

– Create a rest service

1- Create a GET request to get all employees from the employee table.

  • goto SQL Workshop–> RESTful Service


Test rest service to see the results; the rest is a Json document. (for now make it sure the Requires Secure Access is set to NO).


To POST, create an new Resource Handler: with POST as method.

Put this quest to insert a record into the emp table.


About the above part you can find a lot of blog’s and Now the most important part: Secured rest service.

To let a 3rd party application access the API of your rest service you have to secure them. ORDS lets 3rd party applications get access by registering itself in ORDS and ORDS provides a secure token for it.
– you need two users: One to create the rest service client oauth2 reference and one to login as 3rd party application user.

Assign OAuth2 Client Developer group to the first user, and assign RESTful service role to the second user

4 5

  • create a security group from the REST service page and add your “employees” module to the protected modules.


NOTE: now if you request the GET service the response will be “401 Unauthorized”

  • Now you can start the ORDS client authentication page to generate a code for secure access.
  •  login into http://server.com:port/workspace/ui/oauth2/clients
  • fill in the info as in the screen below. MAKE SURE that you have CODE selected and not TOKEN


  • After clicking “Register” you will get a URL just like in the below screenshot.


NOTE: remember the “Client Identifier” and the “Client Secret” you will be needing them to get the TOKEN later on.

  • Click the the URL, you will be redirected to login with second created user.


  • Allow access, the you will be redirected to another page with a URL with: &code=gjcMeQGYxBnQOVotKOM71A..” including the .. at the end. you will get the URL even if the page display says “Not Found” (check the URL).

Now start the command line. type the following in terminal to get the access TOKEN.

curl -i -d “grant_type=authorization_code&code=code_from_the_URL” –user Client Identifier:Client Secret  http://localhost:8081/apex/lab/oauth2/token

curl -i -d “grant_type=authorization_code&code=gjcMeQGYxBnQOVotKOM71A..” –user 1xBmPezTADp5YJYMr8kGFw..:tyNSwyO-o5GQ009M7SxyAA.. http://localhost:8081/apex/lab/oauth2/token

  • The response will be like :


  • From now on, you use the refresh_token to get new token and not the code anymore. this because the code gets expired if the server is restarted, but the refresh token not.
  • in this URL the validity of the token is 3600 sec (1 hour). after it is expired you have to request a new one. The expiration can be changed in the default.xml of your ORDS config.
  • The request with refresh_token should be like :

curl -i -d “grant_type=refresh_token&refresh_token=hbtsEx9vsBFIdZwPjME44A..” –user OAWGPQXol6Kr3GAQgTe4Gg..:_WYbjFH2gTsB7ycgN_HSrw.. http://localhost:8081/apex/lab/oauth2/token

  • response will be exactly the same as the first one, with a new TOEKN and a new refresh_token.

In a HTTPS (SSL) environment, the only change in the request will be curl -k -i -d instead of curl -i -d and of course https in the token URL.

That is it. If you don’t get it and have any question, please let me know. I have an environment up and running on this.