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 12.2.0.1.0 - 64bit Production SQL> @install <chosen-password>
orcl
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
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
).
In the following example
jdbc.auth.enabled
is enabled for all pools:
ords $ ords config set 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: 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.
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
).