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.

SQL Proxy provides role-based permissions, request workflow to the database access, audits the access, records SQL traffic and supports notification about session events.

Enabling Oracle SQL Proxy in XTAM

  1. Login to XTAM 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 XTAM will use for Oracle SQL proxy and click the Save button to its right.

    4. XTAM-Help-Oracle_SQL_Proxy_Configuration.png

  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 XTAM log ($XTAM\web\logs\pam.log.[CurrentDate].log) and search for the line below.

    7. 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 XTAM Vault

After the Oracle Proxy is configured, create a new XTAM 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): XTAM record name
  • Description (optional): XTAM 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 XTAM 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.

XTAM 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 XTAM 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.

Note: The Oracle Proxy connection and underlying records support native XTAM permission and workflow options. The users must have at least some level of Session Control: Connect permission and cannot be bound by an unapproved workflow request to successfully connect with the proxy.