It’s easier to run things unsecured.
It’s unconscionable to run things unsecured.
DON’T BE THIS GUY.
So Let’s Secure My Database and my Web Services
So I have a collection of REST enabled objects (tables, view, and procedures) and a SLEW of RESTful Service modules. I want ALL of them locked down.
So I need to create a Role and Privilege. And then I need to assign that privilege to what I want protected. Without said privilege, my user doesn’t get to use it (Authorized) even if I know who they are (Authenticated.)
I can do this GUI for PL/SQL, I’m going to show both.
The code:
BEGIN
ORDS.CREATE_ROLE(p_role_name => 'storyteller');
l_roles(1) := 'SQL Developer';
l_roles(2) := 'storyteller';
l_modules(1) := 'Banking';
l_modules(2) := 'abstract';
l_modules(3) := 'blind_body';
l_modules(4) := 'bool';
l_modules(5) := 'employee';
l_modules(6) := 'forums';
l_modules(7) := 'george';
l_modules(8) := 'norway';
l_modules(9) := 'otn';
l_modules(10) := 'package';
l_modules(11) := 'parameters';
l_modules(12) := 'performance';
l_modules(13) := 'plsql_table';
l_modules(14) := 'resources';
l_modules(15) := 'rpc';
l_modules(16) := 'sample_module';
l_modules(17) := 'simple';
l_modules(18) := 'test';
l_modules(19) := 'test2';
l_modules(20) := 'test3';
l_modules(21) := 'test4';
l_modules(22) := 'youtube';
l_patterns(1):= '/*';
ORDS.DEFINE_PRIVILEGE(
p_privilege_name => 'oracle.jeff.demo',
p_roles => l_roles,
p_patterns => l_patterns,
p_modules => l_modules,
p_label => '',
p_description => 'just something i made for giggles',
p_comments => NULL);
COMMIT;
END;
/
This looks like this in the GUI:
So I’ve manually listed modules to be protected – THIS IS THE HARD WAY.
What happens when I add a new module tomorrow, and I forget to update the priv?
Or what about all those REST enabled TABLEs and PL/SQL objects I’ve been working on?
Do THIS Instead
Let’s protect the URI pattern.
We are in the HR REST enabled Schema – so I can say starting from /hr/ (or whatever your schema alias is), protect these patterns that match. SO by putting /* – that ANTYHING after /ords/hr
No Go
Let’s make the call sans credentials.
Now with the ‘keys.’
So let’s go back to our PL/SQL code bit for defining the privilege:
l_patterns(1):= '/*';
This protects EVERYTHING under the schema where the privilege is defined. It doesn’t hurt to ‘double protect’ the modules by explicitly adding them, but there’s no need either.
The USER and the Role
This is an ORDS user, and I created it the normal way. The credentials are stored in an xml file under the ORDS configdir, and you’ll be using BASIC AUTH to authenticate. It’s not recommended in general, but it’s useful for demoing users and roles.
c:\ORDS\18.2>java -jar ords.war user ords_dev "storyteller" Enter a password for user ords_dev: Confirm password for user ords_dev: Oct 04, 2018 10:52:05 AM oracle.dbtools.standalone.ModifyUser execute INFO: Created user: ords_dev in file: c:\ords\config\ords\credentials
The REST Client I’m Using Today
A co-worker did a a demo playing with our new DB API (coming soon, stay tuned!), and they used this thing called Insomnia. I’ve been using it for two days, and so far, it’s great. They’re even on Twitter if you’re so obliged.
14 Comments
Same question as Alonso perez.
I can’t find any documentation anywhere that outlines what’s allowed for the privilege URI patterns. The allowed privilege URI patterns don’t seem to match the allowed template/handler URI patterns.
Tried Oracle Support and they couldn’t find any info either – in fact they referenced your site.
From trial and error it appears that the only allowed modifier is “*”.
In fact if you add in a rule like “something/:id” it negates the rule and allows all access.
It’s in the doc for the ORDS.CREATE_PRIVILEGE_MAPPING package SPEC
— Privilege Mappings
/**
*
Associate a privilege with a set of resources. Before servicing any
* resource matching the pattern, the authenticated user will be verified
* to ensure they possess at least one of the roles enumerated by the Privilege.
*
*
The pattern must conform to the following syntax:
*
*- The pattern may contain any printable character except those defined by
- The pattern must start with the
- The pattern may end with the * character. If present the * character
* RFC 3986 section 2.2 as ‘reserved’, however the / and *
* characters are permitted, subject to the rules below.
*
*
/character*
* must be immediately preceded by the / character
*
*
Each pattern is matched against the sub-portion of the path of a request
* URI immediately after the context root of the current request.
*
The pattern is matched against the non URL encoded form of the request
* path.
*
If a pattern ends with the
/*sequence, then the pattern* will match any path starting with the characters preceding the * character.
* Otherwise the pattern will match an exact character for character match.
*
*
Examples
* Assume ORDS schema deployed at
https://example.com/ords/scott/.*
* The pattern
/foo/bar/*will match the following request URIs:*
*
https://example.com/ords/scott/foo/bar/*
https://example.com/ords/scott/foo/bar/baz/resource.json*
* The pattern will not match the following request URIs:
*
*
https://example.com/ords/scott/foo/bar*
https://example.com/ords/scott/foo/barstool*
*
* @deprecated Use define_privilege procedure instead.
*
* @param p_privilege_name The name of the Privilege.
* This value must not be null.
*
* @param p_patterns The patterns of resources to match.
*/
Many thanks Jeff.
So there’s no way to protect a child resource?
I want to allow some clients access to a child resource whilst other clients access to everything.
In the example below Client2 can only POST new childobjects but can’t update the parentobject 1234 – they can’t even GET the parentobject details.
Client1
/api/parentobjects/*
Client2
/api/parentobjects/1234/childobjects
If I can’t do it using a privilege any other options you can suggest?
Or do I need to re-structire my URIs or setup some custom roles in my API to check client access?
Thanks
I didn’t say that, but maybe.
I need to see if there’s a way to have a mapping pattern that’s tied to priv protecting api/parent/:id/childobjects and another priv that covers api/parentobjects/*
I’m assuming you already tried that?
Yes I have tried putting various options in the child priv URI.
parent/:id/child
parent/{id}/child
parent/*/child
None work.
I also have a module level priv to protect everything. If I turn that off then I can access the /child endpoint and none of the above patterns stop that. So suggests that ORDS doesn’t seem to recognise anything other than /*.
No, any valid URI template/pattern should work, esp that 3rd one. I’ll try and test/confirm for you.
Has there been any more word on this issue?
We need to better document the supported wild card patterns for protecting the rest module/templates. I’m working on getting that nailed down and will share the results here (as we also update the docs).
Hi Jeff,
I followed the manual as is and I am receiving 403 Forbiden.
Even the 401 error that is described at the manual I coudn’t achieve. I am only getting 403.
Any idea?
Manual: https://oracle-base.com/articles/misc/oracle-rest-data-services-ords-authentication
That’s not the manual, that’s Tim’s blog.
Please share what you’ve done, step by step, on the forums.
The manuals are here
https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/22.3/index.html
I still have difficulty understanding how to “map” a user/client identity to a defined ORDS role. Setting OAuth aside: as I understand it, standard HTTP daemons will populate AUTHENTICATED_USER environment variable and then pass that to the application (in this case ORDS), so I guess I am expecting a way to let ORDS know what values of AUTHENTICATED_USER should be mapped to a particular ORDS role. I feel like maybe I’m confusing a couple of different concepts here.
when the webserver passes over the authenticated user, it’s also passing over their roles…those roles needs to match up with the roles defined in ords
Thanks, Jeff. I think I understand better now. I think for most practical purposes, an SPNEGO/SSO setup is going to be mutually exclusive with an OAUTH2 setup. I.e., it would will probably be easier and more reliable to have SPNEGO/SSO authentication /not/ apply to the RESTful services parts of ORDS, but then not have to worry about dealing with roles (outside of the built-in role management stuff).
Hi Jeff.
I have a doubth using patterns in proivileges.
Are URL parameters supported on patterns for privileges?
I have this service
/posts/:id/tags
And i want to portect only /tags endpoint
I’ve tried following patterns but do not seem to work:
/posts/:id/tags
/posts/*/tags
What is the pattern needed to protect just /posts/:id/tags endpoint?