8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | 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 18.1 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. This functionality is now included in the 18.3 documentation.

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. If it is

cd /u01/ords

# Default for all pools.
$JAVA_HOME/bin/java -jar ords.war set-property jdbc.auth.enabled true

# Specific pool.
$JAVA_HOME/bin/java -jar ords.war set-property --conf pdb1 jdbc.auth.enabled true

Alternatively, manually edit the "/u01/ords/conf/ords/defaults.xml" file, or one of the pool-specific config files, adding the following entry.

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

The PL/SQL gateway can use a validation function to enforce an allow-list of procedures that can be called from the gateway. If you've configured the gateway to front APEX, you will have the APEX validation function in the "/u01/ords/conf/ords/defaults.xml" file. You either have to use a modified validation function, which is a good idea, or remove this entry.

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

You can reset this parameter value using the following command. For the this example we will assign a blank value, which is functionally the same as removing it from the file.

$JAVA_HOME/bin/java -jar ords.war set-property security.requestValidationFunction ""

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 || '"}' || CHR(10));
  END display_user;

END user_api;
/

Create users for the database authentication, which have 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_1 IDENTIFIED BY my_password1;
GRANT CREATE SESSION TO my_api_login_1;
GRANT EXECUTE ON api_owner.user_api TO my_api_login_1;

CREATE SYNONYM my_api_login_1.user_api FOR api_owner.user_api;


CREATE USER my_api_login_2 IDENTIFIED BY my_password2;
GRANT CREATE SESSION TO my_api_login_2;
GRANT EXECUTE ON api_owner.user_api TO my_api_login_2;

CREATE SYNONYM my_api_login_2.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" or "--user" 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. We are using a self-signed certificate for the HTTPS examples, so the "-k" options prevent curl from complaining about a weak certificate.

$ curl -s -u "my_api_login_1:my_password1" http://localhost:8080/ords/user_api.display_user 
{"remote-user":"MY_API_LOGIN_1"}
$ curl -s -u "my_api_login_2:my_password2" http://localhost:8080/ords/user_api.display_user 
{"remote-user":"MY_API_LOGIN_2"}
$

$ curl -ks -u "my_api_login_1:my_password1" https://localhost:8443/ords/user_api.display_user
{"remote-user":"MY_API_LOGIN_1"}
$ curl -ks -u "my_api_login_2:my_password2" https://localhost:8443/ords/user_api.display_user
{"remote-user":"MY_API_LOGIN_2"}
$

Accessing Services Using the Schema Alias

If you want to access services using a schema alias, you can only use the schema alias associated with login user credentials.

Connect to each login user and REST enable the schema, setting a unique schema alias for each.

CONN my_api_login_1/my_password1@pdb1

BEGIN
  ORDS.enable_schema(
    p_enabled             => TRUE,
    p_schema              => 'MY_API_LOGIN_1',
    p_url_mapping_type    => 'BASE_PATH',
    p_url_mapping_pattern => 'customer1',
    p_auto_rest_auth      => FALSE
  );
    
  COMMIT;
END;
/

CONN my_api_login_2/my_password2@pdb1

BEGIN
  ORDS.enable_schema(
    p_enabled             => TRUE,
    p_schema              => 'MY_API_LOGIN_2',
    p_url_mapping_type    => 'BASE_PATH',
    p_url_mapping_pattern => 'customer2',
    p_auto_rest_auth      => FALSE
  );
    
  COMMIT;
END;
/

We can now access the services using the schema alias that matches the login details.

$ curl -ks -u "my_api_login_1:my_password1" https://localhost:8443/ords/customer1/user_api.display_user
{"remote-user":"MY_API_LOGIN_1"}
$ curl -ks -u "my_api_login_2:my_password2" https://localhost:8443/ords/customer2/user_api.display_user
{"remote-user":"MY_API_LOGIN_2"}
$

The following will fail, as the schema alias doesn't match the login credentials.

$ curl -iks -u "my_api_login_1:my_password1" https://localhost:8443/ords/customer2/user_api.display_user | grep HTTP
HTTP/1.1 404
$ curl -iks -u "my_api_login_2:my_password2" https://localhost:8443/ords/customer1/user_api.display_user | grep HTTP
HTTP/1.1 404
$

For more information see:

Hope this helps. Regards Tim...

Back to the Top.