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:

   Example:

   SQL> @install <password>

   install.sql
   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');
   begin
    htp.prn('<h1>Hello ' || l_user || '!</h1>');
   end;
   /
    
   -- 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;
    
   quit

   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

Hello EXAMPLE_USER1!