12 Replies Latest reply on Sep 27, 2016 2:45 AM by Hawk333

    How to implement OAuth2.0 of the type "Resource Owner Password Credentials"

    Hawk333

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

       

      Basically, I have a hybrid mobile App that requires to access REST modules. However, since I am protecting these modules, and want only validated users to access them, I am looking into best way to do that.

      Therefore, I want to implement OAuth2.0 of the type "Resource Owner Password Credentials". This manual, covers only two types that are supported by APEX I believe ("Authorization Code" & "Implicit Grant"). Implicit grant redirect the user to ords login page. I do not want that. I want the user to insert the username/password at the client (Mobile App), and then receive a session token without seeing ords login page. It is basically the same problem mentioned by Terry in the this blog questions:

       

      1.JPG

       

      But I could not find any manual or guidelines for doing that without APEX. So my question, how can I implement OAuth2.0 of the type "Resource Owner Password Credentials" using PL/SQL. Is there any references, tips?

      Resource Owner Password Credentials
        • 1. Re: How to implement OAuth2.0 of the type "Resource Owner Password Credentials"
          odie_63

          Hawk333 wrote:

           

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

          ORDS, SODA & JSON in the Database

          1 person found this helpful
          • 2. Re: How to implement OAuth2.0 of the type "Resource Owner Password Credentials"
            Hawk333

            Thank you for redirecting me to the right space

            • 3. Re: How to implement OAuth2.0 of the type "Resource Owner Password Credentials"
              Jon Dixon

              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

              • 4. Re: How to implement OAuth2.0 of the type "Resource Owner Password Credentials"
                Hawk333

                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.

                • 5. Re: How to implement OAuth2.0 of the type "Resource Owner Password Credentials"
                  Jon Dixon

                  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.

                  1 person found this helpful
                  • 6. Re: How to implement OAuth2.0 of the type "Resource Owner Password Credentials"
                    Hawk333

                    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

                    • 7. Re: How to implement OAuth2.0 of the type "Resource Owner Password Credentials"
                      Jon Dixon

                      Hi,

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

                      • 8. Re: How to implement OAuth2.0 of the type "Resource Owner Password Credentials"
                        Hawk333

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

                        1.JPG

                        2.JPG

                        3.JPG

                        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.

                        • 9. Re: How to implement OAuth2.0 of the type "Resource Owner Password Credentials"
                          Jon Dixon

                          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;

                          IDNAMEDESCRIPTIONAUTH_FLOWAPEX_APPLICATION_IDRESPONSE_TYPECLIENT_IDCLIENT_SECRETREDIRECT_URISUPPORT_EMAILSUPPORT_URIABOUT_URLLOGO_CONTENT_TYPELOGO_IMAGESCHEMA_IDCREATED_BYCREATED_ONUPDATED_BYUPDATED_ONALLOWED_ORIGINS
                          10583ABC INC Sales SystemSales System for ABC INC.CLIENT_CREDTOKENKYH5zQtfA6FEvAmtbbmU1g..WkajKXpE4hjwLBYz8pbljQ..fred@abcinc.com10001ORDSTEST16-SEP-16ORDSTEST16-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:

                          Screen Shot 2016-09-16 at 8.34.05 AM.png

                           

                           

                          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"

                          1 person found this helpful
                          • 10. Re: How to implement OAuth2.0 of the type "Resource Owner Password Credentials"
                            Hawk333

                            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

                            • 11. Re: How to implement OAuth2.0 of the type "Resource Owner Password Credentials"
                              Jon Dixon

                              You can also create a user using java -jar ords.war user. There is a lot more on this in the security guide which ships with ORDS. Unzip ORDS and look for javadoc/plugin-api/security-guide.html

                               

                              Next steps would be to turn debugging on and see if you can see anything there. As you are using GlassFish, you should add 'oracle.dbtools' as a logger and set it to finest. The ORDS log messages will then show in your domain logs:

                              AddingLoggertoGF.png

                              • 12. Re: How to implement OAuth2.0 of the type "Resource Owner Password Credentials"
                                Hawk333

                                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.