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


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.

I’ve create a PRIV that’s required to access the ’emps’ module.

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 –

    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);

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.

xyz123 <> HR, so no access.

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

the password’s right, but ORDS isn’t configured to try authenticating against a database user

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

The schema username isn’t case sensitive, but the password probably is!

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.


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


  1. Hello Jeff,

    Thanks for introducing World of ORDS; with such self-explanatory posts and examples to support.
    I have a query around DB authentication. Would like to get your insight.

    Objective – Consume E Business Suite Data exposed as REST API through ORDS directly in Microsoft Excel using Get-n-Transform.

    What is achieved – Without enabling any authentication, I am able to expose Oracle DB views created in APPS schema as REST and consume in excel w/o any issues. Works perfectly!

    What’s not working – We have enabled DB Authentication and defined roles(SQL Developer) & privileges on APPS schema. It works fine, if we provide apps user/pwd to connect. But, we cannot expose apps user/pwd in excel. Instead we want users to use their individual DB user/pwd and connect.

    Q – Is it possible to create an ORDS role and/or privilege and assign to DB users, which enables their access to endpoint URL? Or in another words – How to give access to endpoint URL to different DB users controlled by Roles/Privilege.


    • I would not be using database authentication – it’s the least desirable way to authenticate.

      Better to create users in your web tier, say in Tomcat or WLS, then give them the roles required to access those end points.

Write A Comment