I have a slidedeck for ORDS that I use for customer and conference presentations. My co-worker @sqlmaria remarked that it was a bit confusing, and I had to agree.

I tend to skim over this slide, because I’m all excited to get to the fun, sexy SQL – JSON bits, but this is important. So let’s take a look at the BIG picture, and then let’s break down all the components, and see how they fit back together.

The Big Picture

Yes, Virginia – you can have more than one database serviced by one ORDS.

The pieces:

  1. the consumer – a person or automated process, originating from a device with access to HTTPS, somewhere. But we often imagine a very smart person clicking or browsing around on their phone or laptop.
  2. a webserver listening for HTTPS, accepts a request from our consumer…some of the things being requested are JavaScript to be ran in the browser, or static files to be delivered
  3. other things belong to something called /ords .. which is known to be served by our Java Servlet living in Tomcat. It’s /ords because the servlet is defined by an ords.war file
  4. ORDS is a java program, and it has a set of configuration files. These files include definitions of JDBC Connection Pools to one or more databases. The URI of the request tells ORDS which pool to send the request to.
  5. A connection from the appropriate pool is borrowed. The database user, also specified in the URI, is used to Proxy Connect from the connection pool user (so start as ORDS_PUBLIC_USER and end up as HR)
  6. The next part of the URI tells ORDS how to find the RESTful Service that is going to be executed. That service has a SQL statement or block of PL/SQL code attached to be ran. It executes, and results are returned to ORDS.
  7. The connection pool HR session is ‘closed’, and returned to the pool to be used by a subsequent request.
  8. ORDS, if necessary, transforms the JDBC resultset and/or standard output to JSON. RESTful Service Handlers also support Resource Media types, where the results are returned raw along with the proper Mime/Content types (image/png)
  9. The webserver delivers the payload back to the HTTPS requester

But Wait, What About Security?

Ok, that’s a whole other topic, but, up at the very front, when the request is received by Apache/Tomcat, the first thing that will happen is that you will be Authenticated. Yes, you are who you say you are.

Then, the request will continue as previously described, except when the RESTful Service definition is defined in the database, we will also see what privileges are required. At that point, ORDS will AUTHORIZE the request based on the USER who was previously Authenticated based on if they have the required Roles or not.

This gets even more fun when we’re talking about OAUTH2 – tokens!

But the important thing to remember here is that the Web Tier handles Authentication.

Ok, But What About this Standalone Stuff?

It gets a bit simpler. In your midtier instead of having Apache/Tomcat or says WLS running, you JUST have ORDS. ORDS handles itself AND the Webserver bits.

This is how I run my ORDS on my laptop for demos, blogs, etc.

What to you need to know about running ORDS in standalone? Probably the biggest thing that comes into play is that pesky Authentication stuff. Remember how that happens with the webserver, before your request even makes it to ORDS? Well, in Standalone mode, ORDS is the webserver. So if you’re not OK with database user authentication or first party user authentication – neither of which are recommended by us, tying into your corporate application auth schema might get very hard, if not impossible if you don’t pick up Tomcat or WLS – which already have a rich library for plugging into things your LDAP and SSO interfaces.

Good news though – OAUTH2 will still work just fine in Standalone, and we have a Hands-On Lab in our DevDay VirtualBox appliance showing how to use this.

What about Docker? Or What if I want to run ORDS on the same box as my database?

That’ll work too. You just have to decide if it makes sense. For example, if your primary database server goes down, then you just also lost your webserver and ORDS too.

It’s much better to have this running in the mid tier, then if the primary database server goes down, you can then fail-over to your standby database.

And even better, have ORDS deployed on two mid-tiers, for load balancing and failover.

So what should you do? What are your up-time requirements, your load requirements, and how much money do you have?

More Pictures and Bullet-points: the Slides!

That talk I referred to earlier? Here are the slides. If you have any questions, just ping me. And I’m always happy to do customer webinars, demos, or just Q&A’s via email if you’d like.

thatjeffsmith
Author

I'm a Senior Principal Product Manager at Oracle for Oracle SQL Developer. My mission is to help you and your company be more efficient with our database tools.

19 Comments

  1. Jeff

    You seem to be taking this all very personally.

    Vanja might only have been looking for a week but she/he is coming up with the same issues I hit over 3 years ago (great that what was called the procedure.preprocess hook 3 years ago is now working so I’ll take a look but its still a bit of a clunky workaround for what I actually want to do).

    I’d be surprised if most of your target audience isn’t in the same boat as Vanja and myself – upgrading business intranet legacy apps rather than writing the next Facebook or Instagram. It’s no good telling us that we shouldn’t be doing database authentication etc – we have to fit in with the way everything else works so if ORDS can’t enable us to do that then we need to find a tool that does. Changing the enterprise security system is way above our pay grade and likely to be accompanied by a wholesale review of the technology stack – and frankly if I was looking for a database which is simply used as a data dump then there’s a whole lot of cheaper options than Oracle – and I can run bog standard business rules PL/SQL on DB2 or Postgres these days ( hint: what I can’t do is run Apex or ORDS so the better they are, the more reason to stick with Oracle).

    There’s some fantastic stuff in ORDS and it wouldn’t take that much to make it usable – but I’ve been thinking that for several years now 🙁

    • thatjeffsmith

      Right, so let’s get back to what you need to be successful.

      If you want help with your GET handlers, please email me your requirements, and I’ll do up a demo example – [email protected]

  2. Mark Stephens Reply

    Jeff

    I meant exactly what I said – lots of very clever stuff which turns out to be almost unusable in the real world. I’m an Oracle database developer. One of the main selling points of Oracle databases is the amount stuff that CAN be done at the database level – PL/SQL is miles ahead of most other database procedural languages. ORDS just seems to have gone off track with half of it working one way (via a middle java layer) and the rest being based on mod_plsql. What I actually want is everything available at the database level so I can do anything I might feel like doing – I don’t really want someone telling me that something I know I could do quite easily in the database needs to be worked around within a different server further up the calling chain which I don’t have access to.

    Simple scenario:

    I’ll set up an Apache server to do NTLM authentication (or any sort of authentication I want because Apache is good at that) and make it reverse proxy any requests to my ORDS server.

    Set up a ORDS SQL GET service handler with loads of good stuff built in – pagination, links, the clever query block which knows to use bind variables with the actual query etc.

    Aaargh! – all the useful links refer to the ORDS server which the end user shouldn’t know about – OK I’ll spend a few hours messing about adding more stuff into the reverse proxy configuration to sort it out – and I’ll need to do that with every Apache server I set up as a reverse proxy.

    And I’d really like to log IN THE DATABASE ( because relying on flat log files in the Apache server is a bit rubbish) exactly who is making the requests – at which point I start to lose the will to live because all the database ever sees is the query generated by the middle layer.

    Now if someone had taken the clever Java stuff in the middle layer which generates the results and put it in a package on the database instead …

    And Vanya’s use case of database authentication is hugely common in the real world where most development is based on stuff that’s been around for years – and very probably uses other Oracle products like Apex, Forms, Reports etc.

    I’ve got a job at the moment where I’d really love to recommend that they rip out a whole load of horrible .NET middle layer stuff which has C# calling pl/sql procedures on the database via ODP to get ref cursors which are then translated into JSON objects via C# datatypes and replace it with straightforward calls to ORDS but that’s not going to happen any time soon.

    • thatjeffsmith

      Your logging scenario, assuming database auditing doesn’t give you what you want, can be accommodated by a new feature in 18.3.

      You setup a prehook which causes a stored procedure to fire before every call…do your table insert there. See the ords appendix in the docs folder for examples.

      Also OWA IS avail for plsql based handlers.

  3. I agree with Mark’s post. I’m currently trying to get a, seemingly, very simple thing working, basic authentication to ORDS web services, with users definitions in db table. Everything I try doesn’t work, just stumble to more and more problems.
    I’m, more or less, satisfied with ORDS, just can’t get that authentication/authorization to work.
    Authentication with database users? Oh great, I’ll use that, it’s good enough for me. Wait, it just works for old PLSQL gateway procedures? ….
    Authentication with APEX users? Oh great, I’ll use that. Wait, how can I assign an ORDS role to that APEX user? Don’t know.
    And so on…

    • thatjeffsmith

      Where are your users and where are they coming from?

      You don’t want to use database authentication unless absolutely necessary – it’s there primarily to assist mod plsql folks.

      If your web tier already has an authentication system, you want to tie into THAT. ORDS comes into the picture after a user is authenticated.

    • Jeff,

      I want to keep users in my database, be it database users, or just “ORDS” users in some database table.
      I don’t care about the web tier, it’s just a technicality for me. I have a Tomcat just for the ORDS.
      Where I work, we’re using the Oracle Forms and Reports, so we’re more oriented to PLSQL and keeping everything in database.
      So to answer your question, we don’t have an authentication system on the web tier. Do you know, as we’re using Tomcat, can Tomcat be pointed to database user repository? I see Tomcat can be configured in that way, but doesn’t work due to a ORDS bug:
      https://community.oracle.com/thread/4084028
      , so i haven’t tried it.

      Kind regards,
      Vanja

    • thatjeffsmith

      >>I don’t care about the web tier, it’s just a technicality for me.
      You kind of need to care about the web tier. You’re in a new world now. FORMS and Reports are one world. ORDS and REST is a new world. Some things will need to change, it’s not a simple copy and paste to go from one to the other. Yes, we have done things to make this transition easier, but to truly modernize your app stack, you can’t ignore the web tier.

      Database users are often VERY insecure. Weak passwords are common. It’s one of many reasons we recommend you use this only as a last resort for securing database web services delivered by ORDS.

    • Also, one thing that’s confusing to me. There is an example(in the Oracle documentation) of implementing in database authentication using the ORDS prehook function. In that example you can also see that it’s important, after you authenticate the user in the prehook function, that you pass the user and his roles in the “OWA” headers(obviously to authorize the user using the ORDS privileges and roles right?). I thought great, this for sure is the thing that I need, and it must work as it’s an Oracle example. Sadly, when you protect an ORDS module with a privilege, the prehook function doesn’t get called at all, because the ORDS at the Java level rejects you because you’re not authenticated.
      Kinda funny and sad at the same time 🙂

    • thatjeffsmith

      I saw your post on the forums yesterday but you caught me while stuck on a plane, I’ve asked the devs to take a look. if you setup the prehook, it gets executed on EVERY call, so something is up here.

    • Jeff,
      I have a great respect of you and your work, but it looks like you’re not really answering to my questions, but rather defending the current state of things with ORDS 🙂
      Maybe I haven’t expressed clearly, it’s not that I don’t care about the web tier, it’s rather that I care more about the database. I mean, I’m using ORDS because I like it in the first place for being in database! I can always code a Java REST web service, and have everything on the web tier!
      As I see it, the Java ORDS layer, shouldn’t be in the way(as much as possible). It should be just a glue between web and the database.
      So, a simple question. Can you, or can you not, have ORDS users stored in database? To further simplify the question, let’s say I don’t want to use DB users, but rather have them stored in a database table.

      Thank you very much,
      Vanja

    • thatjeffsmith

      >>So, a simple question. Can you, or can you not, have ORDS users stored in database? To further simplify the question, let’s say I don’t want to use DB users, but rather have them stored in a database table.
      No.

      And I’m not trying to be defensive, but when you lead with an insult, it’s hard not to become defensive.

      APEX has a concept of putting users in a table, and using that for authentication, but ORDS does not support that for REST Services authentication.

      The ideal way to go forward is with web tier users, and the most popular route there is an OAUTH2 mechanism to tie into.

      Edit – I spoke too soon…the prehook feature allows for a table with users. See the Prehook Examples we ship on how to use this.

      This partner has shown examples of the additional logging and auth stuff using pre-hooks here.

    • Jeff,

      I initially went with the Oracle’s prehook examples. I also checked the link you sent me, about the prehook function.
      The conclusion is as before, with the prehook function you can authenticate the user, but you also have to handle the authorization manually. You can’t use custom authentication with the prehook function and then use ORDS roles and privs for authorization. The problem is that prehook function doesn’t get called if you don’t authenticate on the web tier side.

    • thatjeffsmith

      Thanks Vanja, I’ll talk to the developer and get some information for you first thing tomorrow.

  4. Unfortunately a lot of the cleverness in ORDS is rendered unusable by bad design/implementation decisions.

    e.g.
    the handlers which use PL/SQL can use the OWA utilities to pick up all manner of information whereas the ones which run an SQL statement can’t – basically because they do all their work in the middle layer and the OWA environment isn’t even initialised for them.

    … and all those nice URIs generated by an SQL GET are relative to the ORDS server and not any proxy / load balancer the request might have come through on its way – and you can’t do any code within the database layer to fix it because the database never sees them because they’re generated in the middle layer.

    … and the very useful forward facility isn’t nearly as useful as it might be because someone decided not to implement it for GETs – so you can’t do things like logging a GET request in the database using a PL/SQL get before forwarding it to another GET request which actually produces the output.

    Now that so much JSON stuff can be done within an Oracle database I’m really not sure what ORDS brings to the party. Its probably easier to write your own PL/SQL modules than struggle to work around shortcomings like the ones above. And once you go down that route, you might as well make your RESTful code agnostic as to what calls it.

    • thatjeffsmith

      Proxy stuff can be handled by url rewrites at the webserver level…tell me what you need to do with a SQL based GET and I’ll see what we can do.

      ORDS is so much more than the json stuff, in fact many people set up their responses as media with application/json as their code does the json itself.

      I would say thanks for calling us clever but you seem to be using it as an insult?

    • thatjeffsmith

      If you want help with your GET handlers, please email me your requirements, and I’ll do up a demo example.

    • Although I haven’t used ORDS much so far (just started reading the docs and playing with it this whole week) I see your point Mark.
      As a comparison with ORDS, at my company we have a simple SOAP framework which consists of a PHP script with roughly 200 LOC, which basically handles the whole raw HTTP request to a database plsql procedure. Its very primitive, but also very simple and powerful, you have complete http request and response logs, and access to every bit of http communication that you need.
      So what I see lacking so far is a way to have ORDS users stored in database, and of course connected with ORDS roles and privs. So you authenticate to ORDS via http basic auth against the users stored in database. Why would that be a bad authentication scheme? Our web services aren’t publicly exposed on the Internet, they are deployed to our users intranet and are used to integrate with other systems. For our usage, https + basic auth is good enough.
      The things I see lacking in ORDS are, as I explained before, having an option to have user repository in database.
      Second thing is to have complete http communication logs stored in database.
      Third thing is to have a better insight into ORDS internal mechanism. Jeff, I read all ORDS articles on this pages, you explained how ORDS works, but that’s a view from 10000 meters.

      I’m not saying ORDS is bad, I would like to use it, but some aspects of it aren’t to my pleasing.
      Maybe the ORDS team should concentrate more on getting the basic things polished, rather than working on more shiny features. Maybe I’m too used working with more popular open source frameworks.

      That’s just my opinion, maybe I’m wrong, but that my experience so far…

    • thatjeffsmith

      >>Maybe the ORDS team should concentrate more on getting the basic things polished, rather than working on more shiny features.
      This product is 8 years old. There’s millions of dev hours into this code base, making sure your code is safe, secured, and to the HTTP/OAUTH2 spec – and works efficiently with the Oracle DB.

      You’re a week into using, or trying to use ORDS. I’m happy to help, but let’s not START with insults just because you’re not understanding what we’re doing yet.

      Also, SOAP <> REST

      >>complete http communication logs
      You have database auditing to capture all of the database activity – and every ords call runs as the db user where the service is defined…plus you have the prehook stuff if you want to do additional logging, but you also have your apache/tomcat logging at the root

Write A Comment