1 2 3 Previous Next 33 Replies Latest reply on Jun 5, 2019 8:46 PM by Joe the Jet

    ORDS Security and Ping

    Joe the Jet

      Ok,  so I get how to assign roles to privileges and then to a REST enabled table. What I don't get is how to assign the privilege to a user.

       

      It seems that these roles/privileges are NOT the same as database roles that we have had for so long and I don't get how to grant them to a user.

       

      So, we're using PING and AD for our authentication and connection pooling such that no one is actually logging into the DB as themselves. The apps have an Angular front-end accessed via a web browser.

       

      How do I grant these permissions to the users who authenticate through PING? I assume I can get the userid from the header, BUT if I'm using the REST enabled tables, I don't have any pl/sql with which to check/set permissions.

       

      Does the generated code somehow use the "implicit parameters" to check security?

       

      The examples I find all seem to use the java "ords.war user" command to set up users. Our users would exist in AD (they probably also have DB users but I don't think we want to use that)

       

      I just don't understand how I map privileges to the user that gets authenticated via Ping/AD

       

      Similarly how do I get the userid for the user so that I can set auditing columns in a trigger?

        • 1. Re: ORDS Security and Ping
          thatJeffSmith-Oracle

          we're looking for the web server authenticated user to have that same role, i'm not sure how this would work with PING/AD if that would require SAML support or not

          • 2. Re: ORDS Security and Ping
            Joe the Jet

            So, the generated code looks for a db user to have a role assigned to them?  How do I assign a privilege to a db user?

             

            Is there a variable set somewhere that holds the userid so I can use it in a trigger?

            • 3. Re: ORDS Security and Ping
              thatJeffSmith-Oracle

              We don't look for a database user to do authentication.

               

              We care about the database user when you run a restful service. The schema that contains the service determines the database user that will be used to execute the code.

               

              If your authenticated web user has the right roll, then they get to expect the service.

              • 4. Re: ORDS Security and Ping
                Joe the Jet

                Ok, so HOW do you create users and how do you assign a role?  I didn't think I should be using DB users.

                 

                The only documentation I can find on creating users is something like this:

                java -jar ords.war user "hr_admin" "HR Administrator"

                which "isn't recommended for production".

                 

                I could be just really bad at finding things, but the documentation seems to be completely missing on how you can grant permissions to users with out using the ords.war.

                 

                I cannot find any documentation on the recommended method for equating my Active Directory (or any other authentication service) user to a "user" who can be assigned a role. I cannot even figure out how to create a user (I don't have unix access to the database to use the ords.war).

                 

                The short and long of it is that I need to be able to assign roles to everyone at my company, ~7000 people. And, since they are all in Active Directory and LDAP and that's what we use to authenticate, this should easily and in a documented way, integrate which such a service. I simply cannot be the only one that is doing this.

                 

                I'm guessing that it can and either it's not documented very well or I'm just can't find it. Trust me, I spent most of yesterday trying to find anything that would give me a clue on this.

                 

                Thanks.

                • 5. Re: ORDS Security and Ping
                  thatJeffSmith-Oracle

                  ords doesn't create a user, your web server has users

                   

                  ords relies on the webserver to handle user authentication - so setup tomcat and hook it up with your AD/LDAP system - you'll be able to tie into those users. But that's a Tomcat question, not an ORDS one. Or it's a WebLogic question, not an ORDS one.

                   

                  If you're using ords in standalone mode, then you're left to first party auth and ords users or oauth2

                  • 6. Re: ORDS Security and Ping
                    Joe the Jet

                    Ok I see what you mean with the web servers, but I still don't understand how to assign a privilege to a user.

                     

                    Right now we're running in standalone mode and oauth2 where do the users come from using oauth2?

                    • 7. Re: ORDS Security and Ping
                      Joe the Jet

                      Ok, here's a picture of how I think this is supposed to work

                       

                      1. User Makes Request

                      2. Web Server/ORDS determines user is unauthenticated and sends that info back to the browser

                      3. Browser collects the login info and passes to PING (or whatever SSO server you have)

                      4. Ping validates and, assuming success, sends back a token

                      5. Browser forwards the token to ORDS

                      6. Token is sent to the PING server for validation.

                      7. Ping sends back response (yay or nay)

                      8. ORDS parses the token, if valid, and sets user info.

                      9. The data requested (or a 401) is sent back to the browser

                       

                      OK, the parts I don't know how to do are steps 6 and 8.

                       

                      From what I can tell, Oracle wants to always generate the client_id and secret. Near as I can tell, these *should* be coming from an enterprise's Authentication Server (in whatever form that takes), not from the database/ORDS.

                       

                      I still do not understand how the user info entered at login are assigned privileges that go with the REST enables tables.

                      • 8. Re: ORDS Security and Ping
                        Joe the Jet

                        I have, of course, read the documentation. As I stated multiple times, it doesn't tell you how to use something like PING, it wants to generate the clientid and secret in the ORDS I think this is problematic. I believe that those normally come from a third-party.

                         

                        In addition the ONLY way I have seen to generate "users", and to assign them roles, is to use the "ords.war user" command which, correct me if I'm wrong, is not supposed to be used for production environments nor would that easily integrate into a company's SSO. I don't see any documentation on creating or using users from a third-party authentication (e.g. PING, LDAP, Active Directory). The documentation does say that the App Server needs to create users but how ORDS gets these users or how you assign roles to them is super ambiguous.

                         

                        I have not checked out the hands-on lab, but I'm guessing that it works just like the link you sent me which means that the id/secret are generated from ORDS.  I will, however, take a look. Do you have a URL for that?  Or is it something I have to install locally?

                         

                        It needs to work like in the diagram that I gave above. Does Oracle support anything like that?

                         

                        Maybe it's in the Oracle Installation manuals?  I need to be able to do two things

                        1. validate a token from PING

                        2. Have ORDS use the user in that token to know what REST services they can access. Hopefully this is available with the Auto-REST tables/procedures.

                         

                        I don't know, maybe no one outside of Oracle is actually using ORDS so you guys don't have any experience with this. Seems like a super normal way for companies to want to use ORDS.

                        • 9. Re: ORDS Security and Ping
                          thatJeffSmith-Oracle

                          your tomcat users will have roles

                           

                          assign them a role, call it whatever you want, say 'ORDS_USER'

                           

                          Then in ORDS, you want to create a role of the exact same name.

                           

                          Then in ORDS, you will assign the required privileges to your end points to that role.

                           

                          When the request comes down the line from tomcat to ords, we'll see the user and their role(s), if they match what we're looking for, they can use the service

                          • 10. Re: ORDS Security and Ping
                            Joe the Jet

                            Ok, great. That's super useful information!

                             

                            I still have the following questions:

                             

                            • Now, is there a document that tells me how "ORDS plugs into Tomcat"? Or does it just happen?

                             

                            • I'm assuming that somehow when the Tomcat/Ping validates the user it sends a token to ORDS and ORDS knows who the user is based on this token and thereby which roles have been assigned to it. This is the only way I can figure that the "Auto-Rest" code works with an external authentication service. Is this correct?

                             

                            • Does it also automatically set things like ":userid"?

                             

                            • Ok,, so, somehow via this "plugging in". ORDS knows the user and which roles have been assigned. So, do I need to do the equivalent of: "ords.war user emp_user emp_role" somewhere or does that just wire in?

                             

                            Thanks so much for your help.

                            • 11. Re: ORDS Security and Ping
                              thatJeffSmith-Oracle
                              • Now, is there a document that tells me how "ORDS plugs into Tomcat"? Or does it just happen?
                                It just happens.

                               

                              • I'm assuming that somehow when the Tomcat/Ping validates the user it sends a token to ORDS and ORDS knows who the user is based on this token and thereby which roles have been assigned to it. This is the only way I can figure that the "Auto-Rest" code works with an external authentication service. Is this correct?
                                I don't know about these 'tokens' - ORDS has access to the authenticated user and their roles via Tomcat and it's java servlet architecture...auto rest features like a table require a privilege. You then assign these privileges to roles, which your tomcat user will have to have

                               

                              • Does it also automatically set things like ":userid"?
                                Yes, but ords has an automatically defined bind variable called :current_user

                               

                              • Ok,, so, somehow via this "plugging in". ORDS knows the user and which roles have been assigned. So, do I need to do the equivalent of: "ords.war user emp_user emp_role" somewhere or does that just wire in
                                If it's just a first party tomcat user, yes, that's just defined in a tomcat xml file - see the link for an example from oracle-base

                               

                               

                               

                              • 12. Re: ORDS Security and Ping
                                Joe the Jet

                                I'm having trouble getting the :current_user thing to work with a trigger.

                                 

                                CREATE OR REPLACE TRIGGER JOE_BIR

                                BEFORE

                                INSERT

                                ON JOE

                                REFERENCING OLD AS OLD NEW AS NEW

                                FOR EACH ROW

                                BEGIN

                                    :NEW.userid := sys_context('USERENV','CURRENT_USER');

                                END;

                                /

                                 

                                This uses the name of the database user, not the user that I authenticated as. How does that get passed in?

                                • 13. Re: ORDS Security and Ping
                                  Adrian D

                                  Hi Joe

                                   

                                  In the handler code (ORDS.DEFINE_HANDLER) we pass the :current_user value to a parameter in our PL/SQL code called in the handler. This contains the authenticated user from ORDS.

                                   

                                  ORDS.DEFINE_HANDLER(

                                        p_module_name    => 'someexample',

                                        p_pattern        => '/',

                                        p_method         => 'POST',

                                        p_source_type    => 'plsql/block',

                                        p_items_per_page =>  0,

                                        p_mimes_allowed  => '',

                                        p_comments       => NULL,

                                        p_source         =>

                                  'BEGIN

                                          package_example.sample_insert(

                                                 p_auth_user => :current_user,

                                                 p_payload => :body,

                                                 p_forward_url => :forwardUrl,

                                                 p_status_code => :statusCode);

                                  END;'

                                   

                                  Then in the PL/SQL code we explicitly set the context (DBMS_SESSION.SET_CONTEXT) to this passed in user parameter.

                                  Then use this in triggers etc like you are trying to do.

                                   

                                  Adrian

                                  • 14. Re: ORDS Security and Ping
                                    Joe the Jet

                                    So how does this work with the auto generated code for a  table? I don't pass current_user there. What you're describing seems to indicate that you have to use the modules, not the autorest to get the current_user.

                                     

                                    And, there is a piece I'm missing. So, I wrote this and even after I use my authentication through Ping, current user returns null.

                                     

                                    ORDS.DEFINE_MODULE(

                                          p_module_name    => 'current.user.example',

                                          p_base_path      => '/greetings/',

                                          p_items_per_page =>  25,

                                          p_status         => 'PUBLISHED',

                                          p_comments       => NULL);     

                                      ORDS.DEFINE_TEMPLATE(

                                          p_module_name    => 'current.user.example',

                                          p_pattern        => 'example',

                                          p_priority       => 0,

                                          p_etag_type      => 'HASH',

                                          p_etag_query     => NULL,

                                          p_comments       => NULL);

                                      ORDS.DEFINE_HANDLER(

                                          p_module_name    => 'current.user.example',

                                          p_pattern        => 'example',

                                          p_method         => 'GET',

                                          p_source_type    => 'json/collection',

                                          p_items_per_page =>  25,

                                          p_mimes_allowed  => '',

                                          p_comments       => NULL,

                                          p_source         =>

                                    'select ''Hello '' || :current_user "greeting" from dual'

                                          );

                                     

                                    What am I missing here?

                                    1 2 3 Previous Next