8i | 9i | 10g | 11g | 12c | 13c | 18c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » Misc » Here

Oracle REST Data Services (ORDS) : Database Authentication

One of the new features of ORDS 18.1 is you can now use database authentication to provide basic authentication for your calls to PL/SQL. You won’t see that in the documentation though. It’s only present in the examples under the “/path/to/ords/examples/db_auth” directory when you unzip the ORDS media. This article is a slight variation of the example provided by the ORDS media.

Using this type of authentication is not advisable, it’s much better to use OAuth2 authentication, but for people that have a lot of applications and web services still using mod_plsql, this is a really handy feature, and certainly eases the transition from mod_plsql to ORDS.

All paths mentioned relate to my installation on Tomcat. You will need to adjust as necessary.

Related articles.

Enable Database Authentication

Database authentication is not enabled by default. To enable it as part of the ORDS installation process add the following line to the "/u01/ords/params/ords_params.properties" file.

jdbc.auth.enabled=true

For an existing ORDS installation, issue the following command.

cd /u01/ords
$JAVA_HOME/bin/java -jar ords.war set-property jdbc.auth.enabled true

Alternatively, manually edit the "/u01/ords/conf/ords/defaults.xml" file, adding the following entry.

<entry key="jdbc.auth.enabled">true</entry>

If it is present, remove the following from the "/u01/ords/conf/ords/defaults.xml" file.

<entry key="security.requestValidationFunction">wwv_flow_epg_include_modules.authorize</entry>

You will need to restart ORDS for this to take effect. The method will vary depending on if you are running ORDS under Tomcat, WebLogic or in standalone mode. For a Tomcat installation you might do this.

$CATALINA_HOME/bin/shutdown.sh
$CATALINA_HOME/bin/startup.sh

Application Setup

Create a user that owns an API we want to expose.

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER = pdb1;

CREATE USER api_owner IDENTIFIED BY api_owner ACCOUNT LOCK;


CREATE OR REPLACE PACKAGE api_owner.user_api AS
  PROCEDURE display_user;
END user_api;
/


CREATE OR REPLACE PACKAGE BODY api_owner.user_api AS

  PROCEDURE display_user AS
   l_remote_user  VARCHAR2(256);
  BEGIN
    l_remote_user := OWA_UTIL.get_cgi_env('REMOTE_USER');
  
    OWA_UTIL.mime_header('application/json');
    HTP.prn('{"remote-user":"' || l_remote_user || '"}');
  END display_user;

END user_api;
/

Create a user for the database authentication, which has execute permissions on the API. We've also created a synonym to the API, so we don't have to include the API_OWNER user in the URL.

CREATE USER my_api_login IDENTIFIED BY my_password1;
GRANT CREATE SESSION TO my_api_login;
GRANT EXECUTE ON api_owner.user_api TO my_api_login;

CREATE SYNONYM my_api_login.user_api FOR api_owner.user_api;

Test It

The following two curl commands call the stored procedure using HTTP and HTTPS respectively. The "-u" option allows you to pass a the database credentials. Although it's possible to use HTTP, you should never pass plain text passwords over HTTP.

$ curl -s -X "POST" "http://localhost:8080/ords/user_api.display_user" -u "my_api_login:my_password1"
{"remote-user": "MY_API_LOGIN"}
$


$ curl -ks -X "POST" "https://localhost:8443/ords/user_api.display_user" -u "my_api_login:my_password1"
{"remote-user": "MY_API_LOGIN"}
$

For more information see:

Hope this helps. Regards Tim...

Back to the Top.