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.
This folder contains some sample database scripts that create a basic demo scenario for database authentication.
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 184.108.40.206.0 - 64bit Production SQL> @install <chosen-password>
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 220.127.116.11.0 - 64bit Production SQL> @uninstall
Database authentication is disabled by default. To enable database
configuration setting needs to be set to
setting can be configured per database pool, or if preferred it can be
so that it is enabled for all pools. Note that the setting only has
applicability to PL/SQL Gateway pools (e.g.
), it does not apply to other pool types such as the
In the following example
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
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:
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:
and remove that setting, then save the modified
After updating the configuration, ORDS must be restarted, if it is running.
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:
The browser will prompt you to enter credentials, enter
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