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.

ApEx runtime installation

I found many blogs about installing ApEx in a runtime environment, but alway missing some in the scripts or having errors in it.

These scripts are test for a customer and I’m sure that this works:-)

To create a workspace:

Login to Oracle as sysdba and run this script to create a workspace:

First make sure that you database schemas create in the database; If not just create it with (create user MY_SCHEMA identified by some_password;)

( p_workspace_id => null,
p_workspace => ‘MY_WS’,
p_primary_schema => ‘MY_SCHEMA’,
p_additional_schemas => ‘MY_SCHEMA2′ );

This scripts adds to schemas to my workspace, If you want to have only one schema, just  make sure that you have both p_primary_schema and p_additional_schemas the same.

spool install_my_application.log;


l_workspace_id NUMBER;
l_app_alias varchar2(100) :=’MYAPP_200′;
l_app_schema varchar2(100) :=’MY_SCHEMA’;
— an application number of an existing application in the workspace.
l_existing_app NUMBER := 200;
— the “new” application number, an existing number will be dropped first.
l_new_app NUMBER := 200;

— set workspace id
SELECT workspace_id
INTO l_workspace_id
FROM apex_workspaces
WHERE workspace = ‘MY_WS’;

(SELECT app.application_id
FROM apex_applications app
WHERE app.application_id = l_existing_app
— generate offset for this application
— set application workspace
apex_application_install.set_workspace_id( l_workspace_id );
— set application ID

— force reset application schema
— Set the application alias




spool off;

You can also create Dev and Admin users via command line

l_workspace varchar2(100) :=’TS’;
l_app_schema varchar2(100) :=’TS’;
l_workspace_id number;


SELECT workspace_id
INTO l_workspace_id
FROM apex_workspaces
WHERE workspace = l_workspace;

apex_util.set_security_group_id (p_security_group_id => l_workspace_id);

p_user_name => ‘ADMIN’,
p_first_name => ‘First’,
p_last_name => ‘Last’,
p_description => ‘Description…’,
p_email_address => ‘admin@admin.com’,
p_web_password => ‘mypassword!’,
p_default_schema => l_app_schema,
p_change_password_on_first_use => ‘N’,
p_attribute_01 => ‘123 456 7890’);

That is all.