Setting-up a PL/SQL Gateway User

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:

Unzip the ords*.zip file.

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 onSAMPLE_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 --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!

Configuring Multiple PL/SQL Gateway Proxied Users
This section describes how to configure multiple PL/SQL gateway proxied users.

A.1 Configuring Multiple PL/SQL Gateway Proxied Users

This section describes how to configure multiple PL/SQL gateway proxied users.

In prior ORDS releases (21.4 and earlier), ORDS administrators could configure multiple connection pools for a single database. This was enabled by using different PL/SQL gateway users while sharing the same ORDS runtime user (typically ORDS_PUBLIC_USER).

In newer ORDS releases (22.1 and later), the approach for supporting similar deployment model has changed. This section describes how to achieve the same behavior in newer versions of ORDS.

Multiple PL/SQL Gateway Users in One PDB (ORDS 21.4 and Earlier)
This section provides an example of configuring multiple PL/SQL gateway users within the same PDB in ORDS 21.4 and earlier.
Multiple PL/SQL Gateway Users in One PDB (ORDS 22.1 and Later)
This section describes how to configure multiple PL/SQL gateway users in the same PDB in ORDS 22.1 release and later.

Parent topic: Setting-up a PL/SQL Gateway User

A.1.1 Multiple PL/SQL Gateway Users in One PDB (ORDS 21.4 and Earlier)

This section provides an example of configuring multiple PL/SQL gateway users within the same PDB in ORDS 21.4 and earlier.

Note:

This configuration is no longer supported and is provided only for reference purposes.

config_folder/ords/
+-- conf/ 
|   +-- main_pu.xml
|   +-- main.xml
|   +-- test_pu.xml
|   +-- test.xml
+-- url-mapping.xml

Main ORDS Pool

File: config_folder/ords/conf/main_pu.xml

<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
<comment>Saved on Mon Jan 09 18:03:11 CST 2023</comment>
<entry key="db.username">ORDS_PUBLIC_USER</entry>
</properties>

Main PL/SQL Gateway Pool

File: config_folder/ords/conf/main.xml

<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
<comment>Saved on Mon Jan 09 18:03:11 CST 2023</comment>
<entry key="db.username">MAIN_PLSQL_USER</entry>
</properties>

Test ORDS Pool

File: config_folder/ords/conf/test_pu.xml

<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
<comment>Saved on Mon Jan 09 18:03:11 CST 2023</comment>
<entry key="db.username">ORDS_PUBLIC_USER</entry>
</properties>

Test PL/SQL Gateway Pool

File: config_folder/ords/conf/test.xml

<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
<comment>Saved on Mon Jan 09 18:03:11 CST 2023</comment>
<entry key="db.username">TEST_PLSQL_USER</entry>
</properties>

Pool Mappings

File: config_folder/ords/url-mapping.xml

<?xml version="1.0" encoding="UTF-8"?>
<pool-config xmlns="http://xmlns.oracle.com/apex/pool-config">
    <pool name="main" base-path="/main" updated="2023-07-17T20:52:29.045Z" />
    <pool name="test" base-path="/test" updated="2023-07-17T20:52:29.045Z" />
</pool-config>

With the preceeding configuration:

ORDS requests whose URL includes /main are served using ORDS_PUBLIC_USER
PL/SQL gateway requests whose URL includes /main execute as MAIN_PLSQL_USER
ORDS requests whose URL includes /test are served using ORDS_PUBLIC_USER
PL/SQL gateway requests whose URL includes /test executed as TEST_PLSQL_USER

Parent topic: Configuring Multiple PL/SQL Gateway Proxied Users

A.1.2 Multiple PL/SQL Gateway Users in One PDB (ORDS 22.1 and Later)

This section describes how to configure multiple PL/SQL gateway users in the same PDB in ORDS 22.1 release and later.

Current versions of ORDS enhances the performance by reusing database pools through proxy connections. To replicate this behavior, additional runtime users must be created.

The following example assumes that there is an existing pool named main that is functioning and it walks through creating a second pool named test that uses PLSQL_USER as the PL/SQL gateway user:

ords_conf/
+-- databases/
|   +-- main/
|   |   +-- wallet/
|   |   +-- pool.xml
|   +-- test/
|       +-- wallet/
|       +-- pool.xml
+-- globals/

Steps to Achieve the Configuration
Generated Configuration
This section shows an example of the configuration files generated after you perform the steps in the preceeding section.

Parent topic: Configuring Multiple PL/SQL Gateway Proxied Users

A.1.2.1 Steps to Achieve the Configuration

Perform the following steps to configure multiple PL/SQL gateway users in the same PDB for ORDS 22.1 and later releases:

Create the new user to be the new ORDS runtime user:

CREATE USER "ORDS_PUBLIC_USER2" IDENTIFIED BY <secure_password>; 
GRANT "CONNECT" TO "ORDS_PUBLIC_USER2";

Grant the ORDS runtime role to the new user:

BEGIN 
     ORDS_ADMIN.PROVISION_RUNTIME_ROLE( 
           p_user => 'ORDS_PUBLIC_USER2', 
           p_proxy_enabled_schemas => TRUE); 
END; 
/

Grant proxy connection privileges for each PL/SQL user to its corresponding runtime user:

ALTER USER MAIN_PLSQL_USER GRANT CONNECT THROUGH ORDS_PUBLIC_USER;
ALTER USER TEST_PLSQL_USER GRANT CONNECT THROUGH ORDS_PUBLIC_USER2;

Configure the new ORDS runtime users to use their corresponding PL/SQL users:

BEGIN 
    ORDS_ADMIN.CONFIG_PLSQL_GATEWAY( 
        p_runtime_user => 'ORDS_PUBLIC_USER', /* when using this user */ 
        p_plsql_gateway_user => 'MAIN_PLSQL_USER' /* run OWA things as this user */ 
   );  
   ORDS_ADMIN.CONFIG_PLSQL_GATEWAY( 
        p_runtime_user => 'ORDS_PUBLIC_USER2', /* when using this user */ 
        p_plsql_gateway_user => 'TEST_PLSQL_USER' /* run OWA things as this user */ 
   ); 
END; 
/

Configure pool test to use ORDS_PUBLIC_USER2:

$ ./ords config --db-pool test set db.username ORDS_PUBLIC_USER2
$ ./ords config --db-pool test secret db.password 
*Enter ORDS_PUBLIC_USER2 password when prompted

Set plsql.gateway.mode to proxied in pool main and test:

$ ./ords config --db-pool main set plsql.gateway.mode proxied
$ ./ords config --db-pool test set plsql.gateway.mode proxied

Restart ORDS.

Parent topic: Multiple PL/SQL Gateway Users in One PDB (ORDS 22.1 and Later)

A.1.2.2 Generated Configuration

This section shows an example of the configuration files generated after you perform the steps in the preceeding section.

ords_conf/
+-- databases/
|   +-- main/
|   |   +-- wallet/
|   |   +-- pool.xml
|   +-- test/
|       +-- wallet/
|       +-- pool.xml
+-- globals/

Main Pool

File: ords_conf/databases/main/pool.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
<comment>Saved on Thu Feb 29 22:29:08 UTC 2024</comment>
<!-- Connection settings -->
<entry key="db.username">ORDS_PUBLIC_USER</entry>
<entry key="plsql.gateway.mode">proxied</entry>
<!-- ... other settings ... -->
</properties>

Test Pool

File: ords_conf/databases/test/pool.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
<comment>Saved on Thu Feb 29 22:29:08 UTC 2024</comment>
<!-- Connection settings -->
<entry key="db.username">ORDS_PUBLIC_USER2</entry>
<entry key="plsql.gateway.mode">proxied</entry>
<!-- ... other settings ... -->
</properties>

Configuration in the Database

If you run following query:

select runtime_user, plsql_gateway_user from ords_metadata.plsql_gateway_config;

The query displays the mapping between the PL/SQL gateway user and its ORDS runtime user in each of the pools:

For example:

RUNTIME_USER        PLSQL_GATEWAY_USER    
___________________ _____________________ 
ORDS_PUBLIC_USER    MAIN_PLSQL_USER   
ORDS_PUBLIC_USER2   TEST_PLSQL_USER

Parent topic: Multiple PL/SQL Gateway Users in One PDB (ORDS 22.1 and Later)