Thursday, 21 April 2016

Setting up and consuming your data securely with ORDS

Setting up

So, you have some data you want to expose to third parties with a REST API, but you want to do so in a secure manner. This is all possible with ORDS using the OAuth2 mechanisms built in. To keep things simple, I will use the sample data (emp, dept) REST functions - and this has all be done in a workspace named "company_reports". And the set up will be done from within APEX - there is an alternative approach of setting everything up using the ORDS API, a subject for another day.

So, go to SQL Workshop and RESTful Services. If you don't already have the module "", click on the "Reset Sample Data" link in the task list:

Now, what we want to do is secure this so that anybody with the URL can't just come in and access the data. In the same task list as above, go to the "RESTful Service Privileges" link and create a new privilege protecting the module "".

At this point, if you now go into your module and try to test access to the data, you will receive an error (401 Forbidden): "Access to this resource is protected. Please sign in to access this resource.".

Before moving onto 3rd party clients, you may want to actually access this data in your APEX application from within the same workspace. We first need to make sure the designated users have been assigned the privilege "RESTful Services".

To access the from the same workspace, in an authenticated APEX session, you need the header set: Apex-Session, with the value being the application id and session id, separated by a comma (e.g.: Apex-Session: 114,16530545902770). If this is not possible, you can pass a query string parameter _apex_session so the URL becomes like:,16530545902770.

Now that we have secured our REST API, how do we set it up so that it can be consumed in a third party application? You need to set up a client, but to be able to do that, you need a user with the role of "OAuth 2.0 Client Developer" (note: this role alone isn't enough to consume the data)

To register a client, you need to go the URL: /ords/workspace/ui/oauth2/clients/ - replacing workspace with your actual workspace name. So, in this example case: Here you need to log in with the user you assigned the OAuth2 Client Developer role.

If this is your first time registering a client, you will be presented with an empty page with a button to register a client:

So, let's go ahead and set up a client. Click the Register Client button filling out all the fields. For this example, I'm going to go with the Code response type. After accepting or denying access to your data, you are re-directed to the URL specified in the Redirect URI field. This URL has some query string parameters, so in your consuming application you need a URL that will be able to interpret the query string parameters to respond to the received code. More on that in the consuming section.

After registering the client, go back into it to get the URL you need to request a token. In this case, it becomes:


So, now this is all set up, it's time to use it in our third party application. For this, I've made a workspace named "employee_consumer". The first thing we need to do here, is set up our callback URI as set up when registering the client. It's at this point we will need to get our access_code.

The two handler cases are:
  1. Success - returns access_code and state
  2. Error - returns error and state
So, set up a handler for the two cases


The source will be PL/SQL code with the following logic (this example has client id and secrets hard coded - ideally, you will encapsulate these better):

    l_access_token_resp CLOB;
    l_resource_resp CLOB;
    l_success_vals apex_json.t_values;
    l_access_token varchar2(50);
    l_error varchar2(50);
    l_resource_url varchar2(200);
    procedure print(p_in varchar2)
        htp.p(p_in || '<br />');
    end print;

    l_resource_url := '';

    apex_web_service.g_request_headers(1).name := 'Content-Type';
    apex_web_service.g_request_headers(1).value := 'application/x-www-form-urlencoded';

    l_access_token_resp :=
            p_url => ''
            , p_http_method => 'POST'
            , p_username => 'cnnD6yQud4G11bM7XtUYog..'--client id
            , p_password => '6tfLKgRat7At3OM2BRhqCQ..'--client secret
            , p_parm_name => apex_util.string_to_table('grant_type:code')
            , p_parm_Value => apex_util.string_to_table('authorization_code:' || :code)
        p_values => l_success_vals,
        p_source => l_access_token_resp
    l_access_token :=
        apex_json.get_varchar2 (
            p_values => l_success_vals,
            p_path   => 'access_token'
    l_error :=
        apex_json.get_varchar2 (
            p_values => l_success_vals,
            p_path   => 'error'
    print('Access token: ' || l_access_token);    
    print('Error: ' || l_error);
    print('Requesting: ' || l_resource_url);
    print( ' ');
    apex_web_service.g_request_headers(1).name := 'Authorization';
    apex_web_service.g_request_headers(1).value := 'Bearer ' || l_access_token;
    l_resource_resp :=
            p_url => l_resource_url
          , p_http_method => 'GET'  



htp.p('Error: ' || :error);
htp.p('State: ' || :state);


So, now we can test all this out. In our application, we add a button with the action to redirect to URL. We specify the URL as that what was given to is during the set up of the OAuth client.

When clicking on the button, I'll be taken to a login prompt

If I enter the credentials of someone without access to the data (the user needs the role of RESTful Services) over REST, I get taken to the redirect URI.

On the other hand, if I enter login of someone with valid credentials, I will be (initially) taken to a page to give access to the data.

So, when clicking Allow access, we get taken to the callback URI, with the code query string parameter.

This code string is then used to request an access token that will be used to access the secured resources. The access token is requested from /ords/company_reports/oauth2/token (e.g.

The GET handler I set up earlier is just set up to add some debug information, and output the resource to the page.

(note: In these examples, on my workstation I set up to point to - which is why most examples use, but the above output is using the actual servers IP address)

In your actual set up, you would probably want to store the access/refresh tokens somewhere and redirect back to APEX - hopefully this gives a good starting point.

Also, refer to ORDS docs giving an example:


  1. Thanks,
    Can we use customs authentication instead of Apex users ?
    Also, is there API for the login ?

    1. Hi, I can't currently see any way that you can specify it to use an external user repository.

  2. Thanks for your reply,
    How can we pass the username, who logged in, to the third party App ? In other words, how the third party App can know who logged in. Namely, what is the alternative of :APP_USER in OAuth ?

    1. What ever you pass in the state query string parameter, gets passed around as a query string parameter in each step, when getting a token. Not sure if that helps.

  3. Hello,
    To retrieve the USER_NAME, you can make the redirect URI something similar to:

    my_template is the URI Template.
    get_user is the Resource Handler: GET.
    Method: GET.
    Source Type: Query.
    Source: select :current_user user_name from dual

  4. Dear Trent,
    In ORDS, are you aware of any OAuth2 API for authentication ?
    ** It is not proper to show the end user ORDS login screen.

  5. Hi Trent,
    I working on Oracle Jet as 3rd party application and using APEX for Restful service.
    Now I facing OAuth issue is, when I call

    It request login ORDS. Is that anyway to passby this ORDS login? I dont want my web let user see ORDS login page XD

    1. Hi Trent,

      I have found the solution to passby ORDS login. i read on ords 3.0 documentation, there has provide grant type with client credential (2 legged auth).

      But APEX no such feature yet, it recommend us to use sql developer to build it.

      anyway, thanks so much your sharing.

  6. Hi Trent

    When I try to register a client for then first time, I get the following message:

    403 Forbidden
    This resource must be accessed over HTTPS only

    URL: dev-2:9090/ords/wstest/ui/oauth2/clients

    what am I doing wrong?

    Thank you


    1.4.5 Using OAuth2 in Non HTTPS Environments

    RESTful Services can be protected with the OAuth2 protocol to control access to nonpublic data. To prevent data snooping, OAuth2 requires all requests involved in the OAuth2 authentication process to be transported using HTTPS. The default behavior of Oracle REST Data Services is to verify that all OAuth2 related requests have been received using HTTPS. It will refuse to service any such requests received over HTTP, returning an HTTP status code of 403 Forbidden.

    This default behavior can be disabled in environments where HTTPS is not available as follows:

    Locate the folder where the Oracle REST Data Services configuration is stored.

    Edit the file named defaults.xml.

    Add the following setting to the end of this file just before the tag.

    Save the file.

    Restart Oracle REST Data Services if it is running.

  8. Hi,
    can u please explain it how to do it using java code and ords api.. Thanks in advance

  9. Hi Trent

    Thanks a lot for this excellent tutorial. Could you please demonstrate on