Oracle SQL Proxy Configuration

Oracle SQL Proxy allows users to use native Oracle clients such as SQLplus, SQL Developer, Dell Toad Oracle, Squirrel, etc. running on their client desktop computers to connect to remote Oracle RDBMS without disclosing scheme credentials.

The SQL Proxy provides role-based permissions, allows users to request a workflow to the database, audits the access to the database, records SQL traffic and provides support for notifications about session events.

While other PAM products claim to support secure database connections, they typically involve the use of a jump server which merely limits access to a DB client running on another host.

With PAM’s Oracle SQL Proxy, a user can run their own database client directly from their own workstation while the secure session runs through PAM where permissions and workflows are enforced and session events are tracked and monitored.

Using our SQL proxy, management of the schema credentials remains locked in the PAM vault and MFA can also be enabled to provide a secondary means of authentication to further secure access to these privileged sessions.

Enabling Oracle SQL Proxy

1. Login to the PAM with a System Administrator account.
2. Navigate to Administration > Setting > Parameters.
3. Locate and modify the following settings:
   1. Oracle Proxy: Switch this option to Enabled and click the Save button to its right.
   2. Oracle Proxy Port: Use or change the port value that the System will use for Oracle SQL proxy and click the Save button to its right.



4. Once both settings have been updated and saved, restart the PamManagement service (Windows) or pammanager service (Unix/Linux).
5. When the services is fully restarted (can take 1-5 minutes), the Oracle SQL Proxy module is online.
6. To confirm the proxy is started after the service restart, open the System log ($PAM_HOME\web\logs\pam.log.[CurrentDate].log) and search for the line below.

Note your proxy port may be different than 1522 if you changed its value in the previous step.

Oracle proxy server listening on *:1522

Creating Oracle SQL Proxy Records in Vault

After the Oracle Proxy is configured, create a new PAM record that will be used for Oracle Proxy connection.

To create this new record, first navigate to Administration > Record Types and locate the type Oracle.

Click Edit, uncheck the Hidden option and then click Save.

Navigate to a location where you want to create the record, click Add Record and select the type Oracle from the dropdown list.

For this new record, define the following values specific to the Oracle database:

• Name (required): PAM record name
• Description (optional): PAM record description
• Connection String (required): Oracle database connection string

  • Example:

    (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbhost)(PORT=1521))(CONNECT_DATA=(SID=orcl)))

  • Another example: host/SERVICE
  • Another example: host:port:SID
• User (required): The Oracle user account that has permission to connect to the database
• Password (required): The password of the Oracle account.

After defining your record details, click the Save and Return button.

With the new record having been created, be sure to configure the System permissions so that the users are able to connect with the proxy using this newly created record (Session Control: Connect).

Monitoring Oracle Client Connection

When the client creates a successful connection to the database using the Oracle Proxy, the user may now work with the database through their client.

System will log or display at least the following information for Oracle Proxy connections:

• Session Report (Record and System Level)

  • A new session will be created indicating when the session was established. An Active status indicates that the user is still connected to the proxy and a Completed status indicates that the user is no longer connected to the proxy.

  • The session’s Type will be labeled ORAP, meaning Oracle Proxy.

• Audit Log (Record and System Level)

  • o A new Audit Log entry will be created for Oracle Proxy sessions, both Session Created and Session Completed. The Channel in the Audit entry’s message will be ORAP.

• Record View

  • When viewing the System record that is being used by the Oracle Proxy, you will see an Active Session indicator when any user(s) is/are actively connected using this record.

SSL support for SQL Proxy connections

PAM supports accepting SSL connections from native Oracle clients as well as support for connections to destination Oracle RDBMS end-points using SSL connections.

This option secures Oracle RDBMS traffic from the native client to SQL Proxy to the destination Oracle RDBMS instance.

In addition to this, the option allows exposing non-SSL traffic from several RDBMS instances through an SSL-enabled channel for outside clients.

To enable SSL for the Oracle connection, include (protocol=tcps) to the address specification of the connection string.

To establish trust between native clients and SQL Proxy, import public proxy certificate found in $PAM_HOME/content/keys/certificate_rdp.cer to the native client key store.

For example, configuration for SQL Developer might include the following procedure:

1
2
3
4
5
keytool -importcert -trustcacerts -file certificate_rdp.cer -keystore chain.jks

AddVMOption -Djavax.net.ssl.trustStore=$STORE_PATH/chain.jks
AddVMOption -Djavax.net.ssl.trustStoreType=JKS
AddVMOption -Djavax.net.ssl.trustStorePassword=changeit