# 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:

- SAMPLE_PLSQL_APP - A database schema where the protected SAMPLE_PROC will be installed
- EXAMPLE_USER1 - A database user granted execute privilege on SAMPLE_PLSQL_APP.SAMPLE_PROC
- EXAMPLE_USER2 - A second database user granted execute privilege on SAMPLE_PLSQL_APP.SAMPLE_PROC

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>

- You may need to adjust the SQLcl connect string and user credentials to suit your envionment. In this case SQLcl will connect to the database with service name `orcl`
- <chosen-password> is the password you wish to assign the EXAMPLE_USER1 and EXAMPLE_USER2 database users. Make a note of this value

### 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 $ 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

#### 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` ).