It’s easier to run things unsecured.

It’s unconscionable to run things unsecured.


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:

 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):= '/*';
      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);      

This looks like this in the GUI:

I am explicitly listing the protected modules.

So I’ve EXPLICITLY said which modules need protecting – 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.

NOTHING under HR goes through w/o the proper privs.

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.

Boo, but good.

Now with the ‘keys.’

I could hit peeps/105, peeps/227, peeps/whatever and BE GOOD.

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 a Jetty user, and I created it the normal way.

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

Don’t Forget APEX!

As of version 18.1 and now improved even more in 18.2, you can do all of your RESTful Service development in APEX. Click the pretty picture to get Doug’s introduction to these new APEX REST development pages.

It was Doug actually who helped me get this going today – I kept trying to secure /hr/* vs /*. Thanks Doug!

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.


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

1 Comment

  1. Alonso perez Reply

    Hi Jeff.
    I have a doubth using patterns in proivileges.
    Are URL parameters supported on patterns for privileges?

    I have this service

    And i want to portect only /tags endpoint
    I’ve tried following patterns but do not seem to work:


    What is the pattern needed to protect just /posts/:id/tags endpoint?

Write A Comment