A Setting-up a PL/SQL Gateway User
This section explains how to set-up a PL/SQL gateway user.
- 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 protectedSAMPLE_PROC
is installedEXAMPLE_USER1
- A database user granted with execute privilege onSAMPLE_PLSQL_APP.SAMPLE_PROC
EXAMPLE_USER2
- A second database user granted with execute privilege onSAMPLE_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!