ORDS, SODA & JSON in the Database

Hawk333 wrote:
If this is not the best space for this question, appreciate it if you could refer me to the right one.

Thank you for redirecting me to the right space

Hi,

I wrote a post on Client Credentials Security using ORDS:

Blog - Cloud Nueva, INC.

Hope this helps. Let me know if you have any questions.

Hi,

At a high level, once you have a web service created in ORDS, you would do the following:

• Create a privilege using  ords.create_privilege
• Create a privilege mapping from your privilege to your web service using ords.create_privilege_mapping
• Create a client using oauth.create_client
• Get the client id and client token using:
  • select client_id,client_secret from user_ords_clients;

At this point you need to get an access token from ORDS using the client id and client secret (you can provide your own user name and password). Your mobile application would then use this token to access your REST service. The mobile app would need to get a new token every 1 hour (default expiration). If you want to know specifically which client is accessing you can create a client (oauth.create_client) for every user and tie the client_id to the user.

I put together a detailed post with step by step instructions for implementing client credentials in ORDS (and extending the default token lifespan) here:

http://www.cloudnueva.com/blog/archives/08-2016

Thank you for your reply. I tried to follow the instructions. But I got stuck at ORDS.DEFINE_MODULE. ORDS package does not have such procedure. Although I can find it in the documentation. But the actual package installed (on ORDS_METADATA schema) does not have such procedure. Other procedures such as ORDS.ENABLE_SCHEMA exist.

What could be wrong here?

Also, I tried to create REST module via Oracle APEX. And I could access it successfully from the browser. But the following URL returns here 'not found': 'https://example.com/ords/rest/oauth/token'

And if I replace it with 'https://example.com/ords/rest/oauth2/token', I get Unauthorized (using postman) - ('rest' here I replaced it with workspace name, if I used DB user/schema name I get 'not found').

I am at loss here. It would be helpful to show some debugging steps.

Hi,

Can you confirm which version of ORDS you are using?

The following works for me and I am using V3.0.5:

BEGIN

ORDS.DEFINE_MODULE(

   p_module_name    => 'v3',

   p_base_path      => 'v3',

   p_status         => 'PUBLISHED',

   p_comments       => 'Module for all Services');

   COMMIT;

END;

You can also do DEFINE_SERVICE which will create the module and handler in one shot.

I would not create services through APEX if you are using ORDS 3.X. I doubt that route will be supported long term.

Thank you for your help. You are right about the ords version. I've upgraded it to 3.0.6, and followed the instructions up to 'Obtaining An Authorization Token'. Here are my steps:

--Enable schema:

BEGIN

ORDS.ENABLE_SCHEMA(

   TRUE,

   'AUDIT');

END;

/

--Define module

begin

ORDS.define_module(

   'v1',

   'v1/');

END;

/

--define template

BEGIN

ords.define_template(

      'v1',

      'customer/');

END;

/

--define handler:

BEGIN

ORDS.define_handler(

      'v1',

      'customer/',

      'GET',

      ords.source_type_collection_feed,

      'SELECT 1 from dual');

      commit;

END;

/

I can call the service successfully using 'https://example.com/ords/audit/v1/customer'

Then I continue with the steps as follows:

--Create privilege:

begin

  ords.create_privilege(

      p_name => 'audit.customer',

      p_role_name => NULL,

      p_label => 'Customer Service Privilege',

      p_description => 'Provide access to the customer service');

  COMMIT;

END;

/

--Create validation mapping:

begin

ords.create_privilege_mapping(

      p_privilege_name => 'audit.customer',

      p_pattern => '/v1/customer');   

  commit;

end;

/

I run validation queries on both 'user_ords_privileges' and 'user_ords_privilege_mappings', and they return correct result

--Create new client:

begin

oauth.create_client(

      p_name => 'ABC INC Sales System',

      p_description => 'Sales System for ABC INC.',

      p_grant_type => 'client_credentials',

      p_privilege_names => 'audit.customer',

      p_support_email => 'fred@abcinc.com');

commit;

END;

/

I ran the query below, and got the client id and secret:

select client_id,client_secret from user_ords_clients;

Now when I try to obtain a session token, I keyed in the client ID in the username field. And the client secret in the password field. I also added the pair (grant-type/client_credentials) to the body. When I try to send POST request, I get '401 Unauthorized'.

What could be wrong here? And is there any way to debug this?

Thank you

Hi,

Can you post a screen shot of your request to get the token?

Hi Jon, Thank you for trying to help on. I really appreciate it. Here are the screenshots:

kirk is the name of our 3.0.6 ords. jti_audit is the schema name (I replaced it with 'audit' in my code above for simplicity. But the actual code used 'jti_audit'.

I also tried oauth, and oauth2 in the url. There is no difference.

Looking forward to hear your comments on this.

Hi,

It looks ok from screen shots.

I belive you have the correct URL etc. When I play with my working instance, the only way I get a 401 is if I change the client_id or client_secret which makes sense. I believe the issue is related to Username and password of the client or the client configuration.

In the event this helps here are some details of a client I just created:

select * from ORDS_METADATA.oauth_clients where id = 10583;

| ID | NAME | DESCRIPTION | AUTH_FLOW | APEX_APPLICATION_ID | RESPONSE_TYPE | CLIENT_ID | CLIENT_SECRET | REDIRECT_URI | SUPPORT_EMAIL | SUPPORT_URI | ABOUT_URL | LOGO_CONTENT_TYPE | LOGO_IMAGE | SCHEMA_ID | CREATED_BY | CREATED_ON | UPDATED_BY | UPDATED_ON | ALLOWED_ORIGINS |
| 10583 | ABC INC Sales System | Sales System for ABC INC. | CLIENT_CRED | | TOKEN | KYH5zQtfA6FEvAmtbbmU1g.. | WkajKXpE4hjwLBYz8pbljQ.. | | fred@abcinc.com | | | | | 10001 | ORDSTEST | 16-SEP-16 | ORDSTEST | 16-SEP-16 | |

One other think to check is if you can access the OAuth UI.

In your case it should be https://kirk/kirk/jti_audit/oauth

You should get something like this:

Finally, here is the curl code generated from my Postman call. Might be worth checking yours against this:

curl -X POST -H "Authorization: Basic S1lINXpRdGZBNkZFdkFtdGJibVUxZy4uOldrYWpLWHBFNGhqd0xCWXo4cGJsalEuLg==" -H "Cache-Control: no-cache" -H "Postman-Token: 3a3ffa80-1cb6-7c58-5849-448b854e7da0" -H "Content-Type: application/x-www-form-urlencoded" -d 'grant_type=client_credentials' "https://example.com:443/ords/ordstest/oauth/token"

Thank you very much again for your help.

I ran the query 'select * from ORDS_METADATA.oauth_clients where id = 10583;', and I getting the exact result as you showed me.

However, I could not access the OAuth UI. I can see the page you showed in the screenshot, but when I try to access 'Clients' or 'Approvals' I am getting 'Unauthorized'

May I know what username and password I should use here "https://kirk/kirk/jti_audit/oauth "?

I tried to use credentials of the OAuth user I created, the Oracle APEX admin credentials, the server (local) admin credentials, but I am getting Unauthorized for all!

What account should I use here?

update:

I managed to login using a user used for an oauth authentication against Glassfish. I am not sure if I am heading in the right direction. I could not see the client I created earlier under clients page

Thanks in advance

I do not want to forget to thank you among the excitement of getting it working eventually. Also, someone one day might stumble upon this and find it helpful.

The only thing I found it different between the documentation you referred me to (javadoc/plugin-api/security-guide.html) and my earlier post is assigning a role:

begin

  ords.create_role('HR Administrator');    

  ords.create_privilege(

      p_name => 'example.employees',

      p_role_name => 'HR Administrator',

      p_label => 'Employee Data',

      p_description => 'Provide access to employee HR data');

  commit;

end;

I continued the steps, and it worked just fine.

Thank you again for all your help, and guidance. I cannot thank you enough.