A Setting-up a PL/SQL Gateway User

This section explains how to set-up a PL/SQL gateway user.

To set-up a PL/SQL gateway user, perform the following steps:
  1. Unzip the ords*.zip file.
  2. Execute the script that provides the password:


    SQL> @install <password>
    set define '^'
    set termout on
    define PWD       = '^1'
    -- Create the schema to hold the stored proc. This account is not directly accessible
    create user sample_plsql_app identified by L0ck3dAcc0unt password expire account lock;
    -- create the application users
    create user example_user1 identified by ^PWD;
    create user example_user2 identified by ^PWD;
    grant connect to example_user1;
    grant connect to example_user2;
    alter session set current_schema=sample_plsql_app;
    -- define the stored procedure
    create or replace procedure sample_proc as
     l_user varchar(255) := owa_util.get_cgi_env('REMOTE_USER');
     htp.prn('<h1>Hello ' || l_user || '!</h1>');
    -- authorize users to access stored proc
    grant execute on sample_plsql_app.sample_proc to example_user1;
    grant execute on sample_plsql_app.sample_proc to example_user2;
    Preceding sample creates three database users:
    • SAMPLE_PLSQL_APP - A database schema where the protected SAMPLE_PROC is installed
    • EXAMPLE_USER1 - A database user granted with execute privilege on SAMPLE_PLSQL_APP.SAMPLE_PROC
    • EXAMPLE_USER2 - A second database user granted with execute privilege on SAMPLE_PLSQL_APP.SAMPLE_PROC

    Use the non-interactive install command and include the options --gateway-user <database user> and --gateway-mode proxied to indicate that the PL/SQL gateway user is a proxied user.

Configuring a PL/SQL Gateway User

Non-Interactive Example:

./bin/ords --config /path/to/test/config install-cli --db-pool pdb2 --admin-user SYS --proxy-user --db-hostname localhost --db-port 1521 --db-servicename pdb1 --feature-sdw true --gateway-user EXAMPLE_USER1 --gateway-mode proxied --log-folder /path/to/logs < password.txt

Assuming ORDS is running in a standalone mode on localhost on port 8080, access the following URL in your web browser: http://localhost:8080/ords/pdb2/sample_plsql_app.sample_proc. The browser displays the following text