In previous blogs we have created REST APIs using SQL Developer & ORDS without any authorization for learning. These APIs are accessible without any authorization. In real world scenarios this is not the case, we have to secure our rest services APIs, so that only authorized person/application can access. Refer below links for previous blog: (without any authorization)

In this blog we will explore how we can secure our Oracle REST Services APIs for database tables using BasicAuth (username & password). Refer below video.


When you enable the schema for REST. Go to SQL Developer. Connect your schema. Right click on connection name-> Rest Services -> “Enable Rest Service”. Check the ‘Authorization required’.

Same thing you have to enable for the object level. Enable the Rest Services on the object level using the ‘Authorization required’.

You will notice some Roles & Privileges got automatically created by the REST services.

If you explore these Privileges, you will get to know that be default some pattern got restricted. It means now you cannot access the REST service url normally. You will got 401 status as pattern got protected. You need to create the user with some role. Assigned that role to these privileges in order to access the REST services. See below example protected resources i.e. URI pattern.

User – Roles – Privileges

See below reference image. How user, roles & privilege are connected. See below reference – User is assigned to roles. You can assign roles to privileges. Privileges actually protects the patterns & modules.

I have created the role API_ROLE & assigned to privileges for Customer Table.

###Creating User with role "API_ROLE"
cd C:\ords20\ords-20.2.0.178.1804
java -jar ords.war user api_user "API_ROLE"
Check here user & roles in credential files.

User can access the APIs using BasicAuth (username & password) if its role is assigned to right privileges.

Accessing the protecting REST service without any authorization keys.

Accessing the REST Service with BasicAuth (username & password).

References: https://www.thatjeffsmith.com/archive/2018/10/ords-the-easy-way-to-protect-all-services-for-a-rest-enabled-schema/


Thanks!

Happy Learning! Your feedback would be appreciated!

7 thoughts on “Securing Oracle Database REST APIs using BasicAuth

  1. Hello Shobhit,

    One Query – Under User, Roles and Privilege, a statement is made “User is assigned to roles”; same depicted in diagram.
    Can you please specify is it a DB user or WLS User. If DB user, how can we assign ORDS roles to it.

    I am working on a POC using ORDS and Microsoft Excel’s Get-n-Transform and would appreciate getting your insights.

    Thanks,
    Shobhit

    Liked by 1 person

    1. Check here user & roles in credential files. I have updated screenshot in blog. ORDS configuration : In my case here is the location of ords config
      C:\ords20\conf\ords\credentials

      admin;{SSHA-512}yrZy..YF0n;SQL Developer,Listener Administrator
      api_user;{SSHA-512}uEft8…..hUqtPx127b;API_ROLE
      developer_user;{SSHA-512}sOUy..KjWkD;API_ROLE,SQL Developer

      Like

      1. Thanks Shobhit.

        Is it a fair assumption to say – ORDS cannot be authorized & authenticated using DB Users, but always needs ORDS Users. Reason, I am emphasizing on using DB Users is, in real world, especially enterprises, DB users are audited and monitored for access and work seamlessly with APPS, DB roles, in a very controlled environment. Each person is authenticated with it’s specific user/password.

        ORDS users are generic and shares passwords and may need separate maintenance. ORDS being an Oracle DB product should have provisioned for validating through DB users too 😦

        Thanks,
        Shobhit

        Like

      2. This blog is just one simple way to secure & access APIs using basic authentication. Just one example. There are many ways to authenticate the APIs.

        Ideally in production when rest APIs exposed – it’s better to use OAuth based authentication. (Preferred)

        Refer this blog:

        https://oracle-base.com/articles/misc/oracle-rest-data-services-ords-database-authentication

        https://oracle-base.com/articles/misc/oracle-rest-data-services-ords-authentication#oauth2

        Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s