Introduction

This document provides an overview of using Database Authentication for PL/SQL Gateway calls. This feature provides functionality equivalent to mod_plsql's dynamic basic authentication, whereby the user is prompted by a Web Browser to provide their database credentials and those credentials are used to authenticate and authorize access to PL/SQL stored procedures.

About this example

This folder contains some sample database scripts that create a basic demo scenario for database authentication.

Getting Started

The first step is to install the sample database schema used for this example. The sample will create 3 database users:

Throughout this example Oracle SQLcl is used to perform database operations:

db_auth $ cd sql/
sql $ sql sys as sysdba

SQLcl: Release Release 18.1.1 Production on Fri Mar 23 14:03:18 2018

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Password? (**********?) ******
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production


SQL> @install <chosen-password>
     

Uninstall

When you are done with the sample you may remove it as follows:
db_auth $ cd sql/
sql $ sql sys as sysdba

SQLcl: Release Release 18.1.1 Production on Fri Mar 23 14:03:18 2018

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Password? (**********?) ******
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production


SQL> @uninstall
	

Enabling database authentication

Database authentication is disabled by default. To enable database authentication the jdbc.auth.enabled configuration setting needs to be set to true .

The jdbc.auth.enabled setting can be configured per database pool, or if preferred it can be configured in defaults.xml so that it is enabled for all pools. Note that the setting only has applicability to PL/SQL Gateway pools (e.g. apex.xml ), it does not apply to other pool types such as the ORDS_PUBLIC_USER pool (e.g. apex_pu.xml ).

Example

In the following example jdbc.auth.enabled is enabled for all pools:

ords $ java -jar ords.war set-property jdbc.auth.enabled true
Mar 23, 2018 2:23:49 PM oracle.dbtools.rt.config.setup.SetProperty execute
INFO: Modified: /tmp/cd/ords/defaults.xml, setting: jdbc.auth.enabled = true	
	

Configure Request Validation Function

It is typical to configure ORDS to use a request validation function (especially when using Oracle Application Express) to ensure that only a whitelisted set of stored procedures in the database may be invoked via the PL/SQL Gateway. The request validation function to use is identified by the setting named: security.requestValidationFunction.

The sample procedure used in this example will not be in the whitelist so it is important to temporarily disable the request validation function for the purposes of trying out this example. For production scenarios you should use a custom request validation function that whitelists the stored procedures that your application needs to access.

To disable the request validation function look for the setting named: security.requestValidationFunction in defaults.xml and remove that setting, then save the modified defaults.xml.

After updating the configuration, ORDS must be restarted, if it is running.

Trying Database Authentication

Start up ORDS as usual.

Assumming ORDS is running in Standalone mode on localhost on port 8080, then access the following URL in your web browser:

http://localhost:8080/ords/sample_plsql_app.sample_proc

The browser will prompt you to enter credentials, enter example_user1 for the user name along with the password value you noted above when installing the sample schema.

Assuming you entered the correct credentials the browser will show a HTML page with the text: 'Hello EXAMPLE_USER1!'. This demonstrates that the database user was authenticated and the user's identity was propagated to the database (via the OWA CGI variable named REMOTE_USER ).