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
The pieces:
- 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.
- 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
- 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
- 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.
- 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)
- 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.
- The connection pool HR session is ‘closed’, and returned to the pool to be used by a subsequent request.
- 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)
- 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.
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.
44 Comments
I am new to all these, is there any chance to get explanation on each slides in the presentation?
Try this video
Many Thanks
Hi Jeff!
you wrote:
>An example of a SSO…we have Weblogic going at Oracle with our 140,000 users or so, all hitting pages provided by ORDS, all authenticated via our corporate SSO. But that required a lot of work on the WLS side to get those things talking to each other. Once that was done, there was little or no work other than a single flag to set to make it work.
We have standalone ORDS (Jetty) and Oauth2 up and running.
Do you have a step-by-step manual how to move that from Jetty to Weblogic?
Hi , I hava a question regarding OAUTH2 implmentation.
When creating insert , I follow your tutorials by making a POST request that redirects after commit to a GET request.
Now the POST end point is protected and the client successfully uses access token in the request, in fact the record is inserted to the database.
However the GET redirect request results in an unauthorized response.
How can I pass the access token from the first POST request to the next GET request ?
thank you in advance.
Hi Jeff,
My question is the result of a lack of knowledge about the architecture of ORDS. Hopefully you can hint as towards a solution.
In our database we have several schema’s which are REST enabled (visible in table ORDS_METADATA.ORDS_SCHEMAS). Each of those schema’s holds several REST Data Services. Now let’s say I have SCHEMA1 and SCHEMA2. SCHEMA1 holds the service EMPLOYEE and SCHEMA2 holds the service DEPARTMENT.
Is it true, each time the service EMPLOYEE or DEPARTMENT is consumed, there will be a logon to the database with SCHEMA1 or SCHEMA2?
The reason behind my question, a specific service we created is consumed thousands times a day. We can see in tremendous increase in logons to the database for the schema which contains the service. If so, is there away to avoid this?
Thx in advance,
Frank.
Yes, the service, if defined in schema A, will be executed as schema A.
Who do you want to do the work, if not your schema 1 or 2?
Thank you for your quick answer.
Of course we want the logic to be handled by the owner of the service, but we also want to reduce the amount of logons of the schema owner (there is a after logon trigger in effect) each time the service is consumed.
Is this possible?
I think you need to lessen the impact of the trigger, have it do less, or move your services where there is no trigger in play.
I think you’re going to need to do connection pooling on the mid-tier to lessen the performance issues.
“satisfied with ORDS, just can’t get that authentication/authorization to work”
Exactly! ORDS is awesome EXCEPT that the authentication and authorization piece is only half there. All the examples use “ords.war” to create users and you’re told “don’t use this in production”. I have yet to find ANY examples of how to use the mid-tier to assign roles or put users into the database. I ask Oracle support and they go silent on the issue. Apparently no one has actually done this. Jeff, would LOVE to hear from anyone who has. Got a client? How are you doing it for your Services?
Now, I would think, that many customers out there are using SSO, whether it be PING or LDAP or other methods. There is supposed support for OAUTH, but, again, no examples on how to actually get these users loaded into the ORDS metadata tables so that you can assign roles/privileges to them.
REST is completely token based for authorization and authentication. Ping will give me a token, I need to be able to pass this to ORDS and have it know who the user is. Is this possible? The only examples I find involve getting a token from ORDS. Of course, not sure how ORDS is authenticating if you’re not using db users.
So, exactly how to I take a token that I get from SSO and tell ORDS who it is and how to I let ORDS know that I have that users ahead of time so that I can assign their privileges? If this is possible, the documentation doesn’t seem to be out there.
ORDS sits below your mid tier where authentication happens
>>All the examples use “ords.war” to create users and you’re told “don’t use this in production”
I wrote one example of how to do this? It’s not recommended b/c that’s a Jetty user and basic authentication means you’re passing credentials on each request, so probably not a good idea, but it is supported.
What you need is a way to tie your mid tier, say Tomcat or WLS, to hook up with PING or LDAP. Those users will come over and into ORDS, ORDS will check to see if they have a ROLE assigned to them that’s required to access the protected end point. If they have it, ORDS will authorize the request.
There are no users in the database if you have your system integrated with an existing identify store…the users are THERE.
The OAUTH2 functionality is not setup to provide OAUTH2 integration with other places like Facebook…it’s ORDS itself providing it’s own OAUTH2 setup, in case you don’t have access to any other identity solution.
If you have PING going with WLS or Tomcat, when they are authenticated, the user is included along with the request to ORDS…which then can be accessed inside a REST Service via the :current_user bind variable.
Also, I’m assuming you’ve seen this?
The only way I know to assign a role to a user is to execute the
OAUTH.create_client command. I can do this without the user already being created? So, I can just *know* who the actual users and assign them without first creating them (e.g. with the ords.war user command)?
“If you have PING going with WLS or Tomcat, when they are authenticated, the user is included along with the request to ORDS…which then can be accessed inside a REST Service via the :current_user bind variable.”
This doesn’t appear to be the case. The only way this could happen is for a token to be passed. Even though I authenticate myself via Ping, current_user is always null. Do I need to add that token to my rest calls?
Ping is well understood in our org, just not how it interfaces with ORDS.
the ORDS OAUTH2 code is not in play here
You have to use the tomcat sign in pages Tomcat users for ords to automatically get the user name when the request is passed through…if you’re using something else, apparently there’s some tomcat config required to have it pass the user along to ords
you’re the only person i know using ping, so we have zero experience with it in our org…doesn’t mean it won’t work though
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 🙁
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]
So, what you’re saying is that unless you set up your users in tomcat, it’s not automatic? That’s a tough way to do things. You’re having to execute code against mutiple mid-tier servers EVERY SINGLE NIGHT (at least) to update your users and passwords. That’s what SSO is all about. Obviating that need.
ORDS has to provide a different way.
Give me an example of ANY SSO (doesn’t have to be Ping) clearly many organizations are using such tools.
If I’m not using oath pl/sql, how am I assigning privileges/roles to my users? Every example I can remember seeing shows using the OAUTH code. Can I protect my end-points without using OAUTH?
you supply a credential to tomcat, it automatically gets that list of users out of your store…after you’ve integrated it with PING. I don’t believe there’s any ‘every night update list’…it’s a live integration point.
An example of a SSO…we have Weblogic going at Oracle with our 140,000 users or so, all hitting pages provided by ORDS, all authenticated via our corporate SSO. But that required a lot of work on the WLS side to get those things talking to each other. Once that was done, there was little or no work other than a single flag to set to make it work.
this is beyond my experience level but I think this doc set describes your options for assigning roles to users, when hooking up to things like an ldap server to source users
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.
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.
yes, you’re right, using Ping obviates the need for nightly updates of tomcat. My point was that ORDS seems to only want to operate against Tomcat users. If that’s not the case. Great! Just need to know how to pass the user info to ORDS.
So, great, you’ve done SSO! That’s awesome. How is WLS sending along the the user information? Are you doing authorization also?
I don’t have a problem of tomcat talking to Ping, I have a problem of getting that information to ORDS. How are you getting the information from your SSO to ORDS?
Jeff, I don’t think it makes a lot of sense to assign my roles in the mid-tier. Why would I not use what you created in ORDS for authorization? Otherwise you’re creating two ways of protecting things (three if you consider db roles)
ORDS does not want to do authentication, that is not it’s role. That’s the mid tier’s job. Our job it to deliver the REST/HTTP interface to the database, protected by roles/privs as assigned to authenticated users coming from your mid tier.
If that’s not possible for you, then we offer the OAUTH2 feature as a fall back.
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…
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
>>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 🙂
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
>>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.
Thanks Vanja, I’ll talk to the developer and get some information for you first thing tomorrow.
Ok, cool. So I create roles/privileges and put them on my ORDS end points. How do assign those roles/privileges to the users coming from the mid-tier?
Ok, great! So, I protect my end points using ORDS roles/privs. NOW, how to I assign those roles/privs to the users “coming from the mid-tier”?
That’s a tomcat question, I think I already answered that to the best of my ability.
send me an email [email protected]
I’ll setup a call with my ords auth expert who can better explain this. We can aim for next week as we’re all on the road this week.
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.
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?
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…
>>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
That would be so awesome. I have been beating my head against this wall for months. I will send you some availability and see if we can find a match.
In the meantime, I guess, I’m supposed to assign roles at the mid-tier instead of what ORDS provides. That’s sort of weird, but I see what I can figure out on that before we talk. Thanks so much for your replies! 🙂