Database Authentication and REST Data Services

We don’t recommend database authentication for protecting your RESTful Services in ORDS.

Why?

A few reasons, but often because database accounts aren’t always treated as securely as they SHOULD BE, and also by giving this set of credentials to someone, you’re giving them access to REST services AND to the source database.

That being said, we found enough customers that really needed this to be implemented due to existing app stacks (MOD_PLSQL) that relied on database authentication. And towards the end of this article you’ll see another reason why supporting DB Auth became relevant.

ORDS Setup

Database Authentication isn’t enabled out-of-the-box. One way to get it going is to add this line to your default.xml file:

<entry key="restEnabledSql.active">true</entry>

This will also enable access to the REST Enable SQL end points…which has it’s own set of caveat emptors, but let’s continue.

Start ORDS up, and now Schema Authentication is enabled.

But we have more to do.

Privilege Setup

The important bit here is in RED – the ‘SQL Developer’ role.

How does this work?

When you authenticate via a database user, ORDS automatically assigns that user for their session, the ‘SQL Developer’ role. This isn’t a database role, it’s an ORDS role. I’ve talked about this role before for using Basic Auth with the Jetty Standalone setup.

Now, there are a few things to know about how one authenticates via the Database User.

1. no can do with common users (think Multitenant/CDB)
2. the schema associated with the user must ALSO be REST enabled
3. the user is only allowed to access end points for their schema URL mapping pattern

So I have REST enabled HR –

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

The only services I’ll be able to access as DATABASE USER HR will be the ones under /ords/hr

I can REST enable user SCOTT, and SCOTT will have the ‘SQL Developer’ role once they’re authenticated, but I try to do a GET on http://server:port/ords/hr/emps/ – it’s going to fail because I can’t authenticate as SCOTT on an /hr/ URI pattern.

Also if I forget to enable Database Auth…I’ll get this, EVEN IF I supply a correct username and password.

Now if I use my REST client to access the resource:

If I ‘log in’ with my browser to access the end point, I’ll get an access cookie that’s good for 30 minutes, then I’ll be prompted to login again.

This becomes important for Autonomous Database Cloud Services.

ORDS and SQL Developer Web will be available in both Autonomous Data Warehouse and Transaction Processing soon.

You’ll be able to publish and use RESTful Services AND use SQL Developer Web. Both of those activities will be limited to REST Enabled Schemas AND Database Authentication. So you’ll need to mind the URI you’re trying to access and the database user account you’ll need the keys for.

More, MUCH MORE on this later.