1 2 Previous Next 15 Replies Latest reply on Jun 30, 2017 11:53 PM by Mahmoud_Rabie

    Apply Authentication on RESTful (GET, POST) resource handlers

    Mahmoud_Rabie

      Hello everybody,

       

      Given:

      - Apex 5.1

      - Mobile Application which would work as REST client to make GET and POST requests to resource handlers defined in a workspace.

      - GET is simple without authentication.

      - In addition, without authentication, tutorials like this one are very helpful to do APEX REST POST.

       

      Question:

      - What are steps to add authentication to GET and POST resource handlers to be able to test them with REST clients like POSTMAN ?

       

      Useful resources

      Restful webservice with basic authentication

      https://ruepprich.wordpress.com/2016/03/22/apex-rest-post/

       

      I would appreciate any help.

       

      Christoph

      Pavel

       

      Regards

      Mahmoud

        • 1. Re: Apply Authentication on RESTful (GET, POST) resource handlers
          Pavel_p

          Hi Mahmoud,

          unfortunately I have absolutely no idea how authentication is supposed to work (if it's even supposed to work). There is a complete lack of documentation and examples, from the few bits of docs I read about it I would expect that it's possible to create a RESTful Service Privilege, assign it some group(s) and if the APEX user is a member of this group, he should be able to invoke the service. The sad truth is that once I assign a required privilege to a RESTful module, I'm no longer able to invoke it no matter what I try.

          I would be really happy if someone could shed some light into it, probably I'm just missing something obvious but after few questions asked in REST forum with absolutely no response I'm not going to waste my time with APEX REST services, at least not until  some decent documentation is available. It would be possible to implement some custom authentication like send either username/pwd in request headers (or a security token obtained from some other separate service), verify it in a pl/sql block and accordingly return some response.

          So after many trials and errors my universal advice is to get familiar with REST services in Java https://netbeans.org/kb/docs/websvc/rest.html. It's way more flexible, you can find examples and tons of documentation everywhere, you can simply debug your services, you can "talk" with your service both in JSON and XML...just to name few benefits.

          Regards,

          Pavel

          • 2. Re: Apply Authentication on RESTful (GET, POST) resource handlers
            Bas de Klerk

            Hi Mahmoud,

             

            I found this cookbook to be very usefull. https://oracle-base.com/articles/misc/oracle-rest-data-services-ords-authentication

            All examples are oracle/sql or curl but it's a great start for exploring the authentication methods etc.. If you want to know about mobile development you should probably explain which platform you're using for development.

            I'm in the process of exploring this for JS (oraclejet etc.) but the problem is in the details, which kind of authentication you choose etc.

             

            regards

            Bas

            • 3. Re: Apply Authentication on RESTful (GET, POST) resource handlers
              Mahmoud_Rabie

              Pavel,

               

              Thanks for your reply.

               

              The sad truth is that once I assign a required privilege to a RESTful module, I'm no longer able to invoke it no matter what I try.

              Did you try with Postman like I did. No way

               

              It would be possible to implement some custom authentication like send either username/pwd in request headers (or a security token obtained from some other separate service), verify it in a pl/sql block and accordingly return some response.

              Could you please, provide some threads or articles.

               

              Regards

              Mahmoud

              • 4. Re: Apply Authentication on RESTful (GET, POST) resource handlers
                Mahmoud_Rabie

                Hi Bas

                 

                Thanks for your response.

                 

                I found this cookbook to be very usefull. https://oracle-base.com/articles/misc/oracle-rest-data-services-ords-authentication

                All examples are oracle/sql or curl but it's a great start for exploring the authentication methods etc..

                Let's take this example module:

                https://apex.oracle.com/pls/apex/ralab/emp/employees

                Without any authentication, the curl is working perfectly. However, If I apply the protection with assigned privileges for user test/test, it is not working. According to the cookbook which you provide, I tried to use the curl for the first-party authentication (basic authentication) like the following:

                curl  -i  -k  --user test:test  https://apex.oracle.com/pls/apex/ralab/emp/employees
                

                Sadly, it is giving 401 Unathorized

                 

                On apex.oracle.com:

                Workspace: ralab

                User          : test

                Password  : test

                 

                If you want to know about mobile development you should probably explain which platform you're using for development.

                I am using IONIC platform to create hybrid mobile applications.

                 

                Regards

                Mahmoud

                • 5. Re: Apply Authentication on RESTful (GET, POST) resource handlers
                  Pavel_p

                  Hi Mahmoud,

                  unfortunately I didn't even get to the point where I could try other REST clients like Postman (I use https://www.soapui.org/ but the client does not matter). I secured the service and then tried to invoke it from within the APEX application (the logged in APEX user was a member of a required group but no luck - still 401 unauthorized). Then I wanted to further analyze what is being sent but since I was not able to accomplish even the most basic scenario, no further analysis was possible. So I simply gave it up - too much time wasted with absolutely no results.

                  Custom authentication without any security considerations:

                  You have a table with username-password pairs and you want to send username and password in request headers. So you can create a web service as follows

                  Source Type: PL/SQL

                  Source something like:

                  begin
                  select 1 from my_user_auth_table where user = :user_name and password = :usr_password;
                  --if something is returned, continue with logic
                  for each r_emp in (select * from emp) loop
                    htp.p(r_rmp.empno || ',' || r_emp.ename);
                  end loop;
                  exception
                  when no_data_found then
                    null; --logic for unauthenticated users
                  end;
                  

                  And add two input parameters to the service:

                  1) User name

                  Name: username

                  Bind Variable Name: user_name

                  Acccess Method: IN

                  Source Type: HTTP header

                  2) Password

                  Name: password

                  Bind Variable Name:usr_password

                  Acccess Method: IN

                  Source Type: HTTP header

                  I know it's pretty clunky but unfortunately I don't know about anything more straighforward.

                  Regards,

                  Pavel

                  1 person found this helpful
                  • 6. Re: Apply Authentication on RESTful (GET, POST) resource handlers
                    Bas de Klerk

                    Hi Mahmoud,

                     

                    I cannot imagine you followed the entire cookbook for setting up a user on apex.oracle.com since you need to add the users to the ords config. It should be done like below and I don't think you have access to the ords config on apex.oracle.com ( but I might be wrong ).

                    $ cd /u01/ords
                    $ $JAVA_HOME/bin/java -jar ords.war user emp_user emp_role
                    Enter a password for user emp_user:
                    Confirm password for user emp_user:
                    Jun 29, 2016 11:52:42 AM oracle.dbtools.standalone.ModifyUser execute
                    INFO: Created user: emp_user in file: /u01/ords/conf/ords/credentials
                    

                     

                    when I follow the steps to setup the user account via the ords config I can acces the url via curl easily with a username and password. To test this I have a local install of oracle, apex, ords (and tomcat but you can skip that one) on my laptop. You should also be able to get that rest data also from ionic using cordova (which usually whitelists all kinds of domains). If you access it from ionic in your browser ( so none hybrid but web setup ) you might run into CORS issues since the browser does not like you to fetch the rest data from another host/domain etc. Anyway, that's another issues and a few steps further down the road, let's first try to get the curl command working which is the easiest config since curl does not have cross origin issues.

                     

                    So I think you need to set this up on an environment where you can config the ords.jar.

                     

                    Regards,

                    Bas

                    1 person found this helpful
                    • 7. Re: Apply Authentication on RESTful (GET, POST) resource handlers
                      Mahmoud_Rabie

                      Hi Pavel and Bas,

                       

                      Thanks again.

                       

                      Pavel,

                       

                      I have created a web service with two input parameters with this PL/SQL

                      DECLARE
                         CURSOR cur_emp
                         IS
                            SELECT *
                            FROM emp;
                         r_emp   emp%ROWTYPE;
                      Begin
                          if( instr(APEX_UTIL.GET_GROUPS_USER_BELONGS_TO(p_username => :user_name),'test')>0
                              AND   APEX_UTIL.IS_LOGIN_PASSWORD_VALID (p_username=> :user_name, p_password=> :user_password) ) THEN
                             /*For Authenticated Users*/
                             for r_emp in cur_emp loop 
                                htp.p(r_emp.empno || ',' || r_emp.ename); 
                             end loop;
                          ELSE
                             /*For Non-Authenticated Users*/
                             htp.p('NO DATA!');
                          END IF;
                      End;    
                      

                       

                      The Resource Handler:

                      https://apex.oracle.com/pls/apex/ralab/emp/empplsql

                       

                      Workspace : ralab

                      User: test

                      password: test

                       

                      Sadly, using Postman of chrome, it is not working. I would appreciate your help.

                       

                      Regards

                      Mahmoud

                      • 8. Re: Apply Authentication on RESTful (GET, POST) resource handlers
                        Pavel_p

                        Hi Mahmoud,

                        I don't have much time today, so I took a brief look at your service (but later we can investigate it deeper). I think the main mistake there is the method - it's GET (and I agree, it should be GET) but then it cannot be PL/SQL as the documentation says:

                        Executes an anonymous PL/SQL block and transforms any OUT or IN/OUT parameters into a JSON representation. Note that this option is only available when the HTTP method selected is one of the following: DELETE, PUT, POST.

                        so I suggest to change it to POST. Also I'm not sure how this line of code is supposed to work and what groups you created

                        if( instr(APEX_UTIL.GET_GROUPS_USER_BELONGS_TO(p_username => :user_name),'test')>0 
                        

                        but I expect it should return non-zero values for certain groups your user belongs to. So for example your test user is a member of following groups "SQL Developer, RESTful Services, OAuth2 Client Developer, MyTeachers", so I would try to change the line of code to

                        if( instr(APEX_UTIL.GET_GROUPS_USER_BELONGS_TO(p_username => 'TEST'),'MyTeachers')>0 

                        just for debugging purposes and send a following JSON object in the request body

                        {
                            "user_name": "TEST",
                            "user_password": "test"
                        }
                        

                        and let's see what happens (or you can keep your current input parameters and send both values as HTTP headers).

                        Regards,

                        Pavel

                        1 person found this helpful
                        • 9. Re: Apply Authentication on RESTful (GET, POST) resource handlers
                          Mahmoud_Rabie

                          Pavel,

                           

                          I did the following steps:

                          1) Change the GET to POST.

                           

                          2) Write a PL/SQL function called MYREST_AUTH

                          create or replace function "MYREST_AUTH"(p_user in VARCHAR2 default '50',p_pass in VARCHAR2 default '50')
                          return NUMBER
                          is
                             result NUMBER;
                          begin
                             If( APEX_UTIL.IS_LOGIN_PASSWORD_VALID (p_username=> p_user, p_password=> p_pass) ) THEN
                               result := 1;
                             ELSE
                               result := 0;
                             END IF;
                             return result;
                          end;
                          

                           

                          3) Re-write the PL/SQL of the service

                          DECLARE
                             CURSOR cur_emp
                             IS
                                SELECT *
                                FROM emp;
                             r_emp   emp%ROWTYPE;
                          Begin
                              if( MYREST_AUTH(:user_name,:user_password) = 1 ) THEN
                                 for r_emp in cur_emp loop 
                                    htp.p(r_emp.empno || ',' || r_emp.ename); 
                                 end loop;
                              ELSE
                                 htp.p('NON-AUTHORIZED');
                              END IF;
                          End;    
                          

                           

                          However, it always gives me "NON-AUTHORIZED" !! Why?

                           

                          Inside RESTful POST's PL/SQL, is there any problem with function APEX_UTIL.IS_LOGIN_PASSWORD_VALID?

                          fac586

                          Patrick Wolf-Oracle

                          joelkallman-Oracle

                           

                          Inside the IF statement, If I replace the function with any plain true condition like :user_name = 'TEST', it would work!

                           

                          With Postman, you could check

                          https://apex.oracle.com/pls/apex/ralab/emp/empplsql

                          TEST/test

                          Capture.PNG

                           

                          I would appreciate your help.

                           

                          Regards

                          Mahmoud

                          • 10. Re: Apply Authentication on RESTful (GET, POST) resource handlers
                            fac586

                            Mahmoud_Rabie wrote:

                             

                            Pavel,

                             

                            I did the following steps:

                            1) Change the GET to POST.

                             

                            2) Write a PL/SQL function called MYREST_AUTH

                            1. createorreplacefunction"MYREST_AUTH"(p_userinVARCHAR2default'50',p_passinVARCHAR2default'50')
                            2. returnNUMBER
                            3. is
                            4. resultNUMBER;
                            5. begin
                            6. If(APEX_UTIL.IS_LOGIN_PASSWORD_VALID(p_username=>p_user,p_password=>p_pass))THEN
                            7. result:=1;
                            8. ELSE
                            9. result:=0;
                            10. ENDIF;
                            11. returnresult;
                            12. end;

                             

                            3) Re-write the PL/SQL of the service

                            1. DECLARE
                            2. CURSORcur_emp
                            3. IS
                            4. SELECT*
                            5. FROMemp;
                            6. r_empemp%ROWTYPE;
                            7. Begin
                            8. if(MYREST_AUTH(:user_name,:user_password)=1)THEN
                            9. forr_empincur_emploop
                            10. htp.p(r_emp.empno||','||r_emp.ename);
                            11. endloop;
                            12. ELSE
                            13. htp.p('NON-AUTHORIZED');
                            14. ENDIF;
                            15. End;

                             

                            However, it always gives me "NON-AUTHORIZED" !! Why?

                             

                            Is there any problem with function APEX_UTIL.IS_LOGIN_PASSWORD_VALID?

                            fac586

                            Patrick Wolf-Oracle

                            joelkallman-Oracle

                             

                            Inside the IF statement, If I replace the function with any plain true condition like :user_name = 'TEST', it would work!

                             

                            With Postman, you could check

                            https://apex.oracle.com/pls/apex/ralab/emp/empplsql

                            TEST/test

                            Capture.PNG

                             

                            I would appreciate your help.

                             

                            Regards

                            Mahmoud

                            Mahmoud_Rabie wrote:

                             

                             

                            I did the following steps:

                            1) Change the GET to POST.

                             

                            2) Write a PL/SQL function called MYREST_AUTH

                            1. createorreplacefunction"MYREST_AUTH"(p_userinVARCHAR2default'50',p_passinVARCHAR2default'50')
                            2. returnNUMBER
                            3. is
                            4. resultNUMBER;
                            5. begin
                            6. If(APEX_UTIL.IS_LOGIN_PASSWORD_VALID(p_username=>p_user,p_password=>p_pass))THEN
                            7. result:=1;
                            8. ELSE
                            9. result:=0;
                            10. ENDIF;
                            11. returnresult;
                            12. end;

                            3) Re-write the PL/SQL of the service

                            1. DECLARE
                            2. CURSORcur_emp
                            3. IS
                            4. SELECT*
                            5. FROMemp;
                            6. r_empemp%ROWTYPE;
                            7. Begin
                            8. if(MYREST_AUTH(:user_name,:user_password)=1)THEN
                            9. forr_empincur_emploop
                            10. htp.p(r_emp.empno||','||r_emp.ename);
                            11. endloop;
                            12. ELSE
                            13. htp.p('NON-AUTHORIZED');
                            14. ENDIF;
                            15. End;

                            However, it always gives me "NON-AUTHORIZED" !! Why?

                            ORDS != APEX. Look at the documentation for apex_util.is_login_password_valid:

                            This function returns a Boolean result based on the validity of the password for a named user account in the current workspace.

                            The web service request is not an APEX request (you are calling /pls/apex/ralab/... not /pls/apex/f), it is not in an APEX session context, and therefore there is no "current workspace".

                             

                            ORDS web services are not intended to be used solely from within APEX applications.

                            1 person found this helpful
                            • 11. Re: Apply Authentication on RESTful (GET, POST) resource handlers
                              Pavel_p

                              The web service request is not an APEX request (you are calling /pls/apex/ralab/... not /pls/apex/f), it is not in an APEX session context, and therefore there is no "current workspace".

                              Yes, you are right. I was thinking about input and output parameters and so on and not about the logic in context.

                              • 12. Re: Apply Authentication on RESTful (GET, POST) resource handlers
                                Mahmoud_Rabie

                                Paul and Pavel,

                                The web service request is not an APEX request (you are calling /pls/apex/ralab/... not /pls/apex/f), it is not in an APEX session context, and therefore there is no "current workspace".

                                 

                                ORDS web services are not intended to be used solely from within APEX applications.

                                 

                                If we are integrating ORDS web services with an external framework such as IONIC, what is the recommended way to exchange data securely between the two frameworks?

                                 

                                Regards

                                Mahmoud

                                • 14. Re: Apply Authentication on RESTful (GET, POST) resource handlers
                                  Pavel_p

                                  Hi Mahmoud,

                                  sorry, I have absolutely no idea how to achieve it. Since creating secured REST services in APEX seems to be a "bit" problematic, I would probably recommend to forget about it and start reading here Getting Started with RESTful Services .

                                  Personally I don't care too much about ORDS no matter if in APEX or "standalone" (I was just curious how it works in APEX) because ORDS shows no love for xml anymore. Unfortunately in the world I live in is still xml the preferred format, also my questions on ORDS forum are being ignored, so I just stay with writing REST services in Java https://netbeans.org/kb/docs/websvc/rest.html which is not that hard and brings a lot of advantages (flexibility, debugging, documentation, tons of examples...).

                                  Also I have no experience with IONIC but I just wonder why to use it at all. APEX (desktop themes) run just fine on my lowend cellphone, so I see no reason why to use something else. I mean it makes sense to use either APEX or some other framework of your choice but why to use both or even more technologies? What is it that APEX cannot do that you decided to use IONIC instead?

                                  Regards,

                                  Pavel

                                  1 2 Previous Next