I recently started looking into a problem at work with getting security added to our setup with views making REST-calls to work as if they were served with a database link. In that work I started from scratch adding one piece at a time to make sure I could troubleshoot it. In doing so I realised that we often talk about the amazing magic of ORDS, but how does one break it down to understand and troubleshoot the smoke and mirrors. What I needed to break down the security setup ended up being what I needed to show my colleagues about what makes ORDS work. It also lends itself to a step by step series on blog-form.

For the blog I’ll convert the on-prem demo to use OCI as everyone can have a free database and ORDS setup there with just a couple of clicks. Installing and getting ORDS working is easy, but not having to do it is easier.

I’m using my free Autonomous Database for the examples. You can do that same with other OCI DB services or on-prem / VM /Docker but you may need to make some adjustments here and there.

You will need to get your base endpoint for the tests.

• On cloud.oracle.com, navigate to your Autonomous database.
• Open Database Actions.
• Click on the hamburger menu and click on RESTful services and SODA under related services.
• Copy and save somewhere the URL that pops up. The one I get looks like “https://ioxxxxxxxxxxxss-evilatp.adb.eu-frankfurt-1.oraclecloudapps.com/ords/”

No, that is not really the name of my tenacy, but close enough. You can verify that it works by issuing this cURL-command.

curl -v https://ioxxxxxxxxxxxss-evilatp.adb.eu-frankfurt-1.oraclecloudapps.com/ords/

You’d of course paste the URL you got there instead of my doctored one. It should return quite a bit of text and among other things some lines looking like these towards the end.

< Set-Cookie: ORA_WWV_USER_9274378038389796=ORA_WWV-YbsoJGtYZdGRRlWv-eFSHkQB; path=/ords/; samesite=none; secure; HttpOnly
< Set-Cookie: ORA_WWV_RAC_INSTANCE=2; path=/ords/; samesite=none; secure; HttpOnly
< Location: https://ioxxxxxxxxxxxss-evilatp.adb.eu-frankfurt-1.oraclecloudapps.com/ords/f?p=4550:1:102000128053637:::::

If you get those cookies and a location looking much like APEX it worked and we can move on with ORDS and REST.

Now let’s set up the base part of getting data from a view in the database via REST. Begin with setting up the user logged in with a privileged user.

create user rest_demo identified by Demo_Rest_1234
  default tablespace data
  temporary tablespace temp
  quota unlimited on data;

grant create session   to rest_demo;
grant create procedure to rest_demo;
grant create view      to rest_demo;

begin
    ords.enable_schema( p_enabled => true
                      , p_schema => 'REST_DEMO'
                      , p_url_mapping_type => 'BASE_PATH'
                      , p_url_mapping_pattern => 'rest_demo_schema'
                      , p_auto_rest_auth => false);
    commit;
end;

With that we have a user rest_demo that has access to create procedures and views and the schema is enabled for REST-access.

Now, log in with the user REST_DEMO and create a rest-enabled view.

create view vw_rest as
  select object_id
       , owner
       , object_name
    from all_objects;

begin
    ords.enable_object(p_enabled => true,
                       p_schema => 'REST_DEMO',
                       p_object => 'VW_REST',
                       p_object_type => 'VIEW',
                       p_object_alias => 'vw_rest_svc',
                       p_auto_rest_auth => false);
    commit;
end;

We have now set up a view that we can access with rest. To wrap up this blog post, let’s just try it by adding to the cURL-command we used before.

curl https://.../ords/rest_demo_schema/vw_rest_svc/

{"items":[{"object_id":134,"owner":"SYS","object_name":"ORA$BASE"},
<... snip ...>
{"object_id":1795,"owner":"PUBLIC","object_name":"PLITBLM"}]
,"hasMore":true,"limit":25,"offset":0,"count":25
,"links":[< ... snip ... >]} 

That is it, we have a rest service we can call from any environment that can issue a REST-call to get to our data. Pretty impressive to get REST with just a few lines of DDL. The cURL-call has rest_demo_schema and vw_rest_svc to identify schema and view. It is not the name of either but rather aliases to not expose the real names in the database. You can see in the commands above where we gave them alternative names.

That is it for this post. In the next post we’ll look at more ways to make the same rest call and to see the response in a more readable format.