ORDS 101: Enabling Oracle Schemas for HTTPS/REST

BEFORE you can start using REST APIs OR login with SQL Developer Web, you need a REST Enabled Schema.

Oracle REST Data Services (ORDS) is a java application, running in your mid-tier, that uses Oracle JDBC and our Universal Connection Pool (UCP) tech to ‘talk to your Oracle Database.’

ORDS only uses 1 database user account, in general, to fill the pools with connections, for handling database requests [ORDS_PUBLIC_USER].

Exceptions:

• you’re using an older copy of ORDS v3-21.4
• you’re using the PL/SQL Gateway (not talking about APEX)

So anytime ORDS is about to do anything in a database, it always starts it’s life out as ‘ORDS_PUBLIC_USER.’

What do we mean by ‘Enable’ or ‘REST Enable?’

When ORDS gets a request, it has to figure out a few things.

1. Which database (pool!) is the request getting routed to?
2. Which user in that database is going to do the work?
3. What is the work to be done in the database?

‘Enabling’ a schema for REST or for ORDS, is all about that second item – the USER that is doing the work.

If Oracle database user ‘HR4’ is REST Enabled, then that means the ‘ORDS_PUBLIC_USER’ account has the necessary privileges to PROXY CONNECT to ‘HR4.’

How to enable a schema

Scenario #1: Enable your own account.

If I’m “H4′, I can simply login to the database and simply run this –

BEGIN
   ORDS.ENABLE_SCHEMA(p_enabled => TRUE,
                      p_schema => 'HR4',
                      p_url_mapping_type => 'BASE_PATH',
                      p_url_mapping_pattern => 'hr4',
                      p_auto_rest_auth => FALSE);
END;
/

Or, I could instead run –

exec ords.enable_schema();

This works, because all of the parameters have ‘acceptable’ default values. The alias will default to the schema name, and the rest auth will default to false.

ORDS PL/SQL API Docs

Scenario #2: Enabling someone else’s account.

This requires either the DBA role, or the ORDS_ADMINISTRATOR_ROLE. If you have either of these roles, you can use your account to enable someone else’s.

-- I am not logged in as Bob
BEGIN
   ORDS.ENABLE_SCHEMA(p_enabled => TRUE,
                      p_schema => 'BOB',
                      p_url_mapping_type => 'BASE_PATH',
                      p_url_mapping_pattern => 'bob',
                      p_auto_rest_auth => FALSE);
END;
/

About P_URL_MAPPING_PATTERN

It defaults to the schema name.

You may want to consider not using the default. Doing so basically advertises to anyone using your APIs that there is a database user named, ‘BOB’ in a database, somewhere. That’s a potential attack vector.

It needs to be ‘lowercase.’ Actually, that’s not strong enough. It must be in ‘lowercase.’

About P_AUTO_REST_AUTH

If set to TRUE, the metadata-catalog and open-api-catalog endpoints will require authorization. FALSE would mean that anyone that can reach ORDS would be able to ask for a list of APIs in your schema, and their associated documentation pages.

P_AUTO_REST_AUTH Set to TRUE, you’ll see this if you don’t provide credentials –

This setting has no bearing on access to objects/apis IN the schema, those will be protected (or won’t be) by privileges tied to the modules, autorests, or URI mapping patterns.

But I don’t want people to be able to enable their own schemas

I understand, and the solution is quite simple.

That means the system’s DBA will be responsible for enabling schemas and publishing REST APIs, and hopefully via some sort of CI/CD or automated process in your product environments.

The ORDS package is also used for creating and managing the REST APIs, so in development environments where you’re asking developers to…develop, leave the PUBLIC grant intact. Or at least, that’s my advice.

This also gates access to SQL Developer Web

If I try to login to a database using ORDS/SQL Developer Web, I can only do so via REST enabled schemas.

If ORDS doesn’t recognize the schema, it won’t even try to login.

SQL Developer Web and aliased schemas

If you followed my advice above, when you enabled the ‘HR3’ schema, maybe you also aliased it as ‘threepo’

BEGIN
   ORDS.ENABLE_SCHEMA(p_enabled => TRUE,
                      p_schema => 'HR3',
                      p_url_mapping_type => 'BASE_PATH',
                      p_url_mapping_pattern => 'threepo',
                      p_auto_rest_auth => TRUE);
END;
/

When you go to login to SQL Developer Web, you’ll need 3 pieces of information:

• the mapping pattern, or alias
• username
• password

That looks like this –