XTAM Session Events – Capturing SQL Traffic
XTAM Session Event recording enables the ability to save SQL statements to the Session Events Logs when connecting to a MySQL or MS SQL Server database through the use of a SSH Proxy tunnel using native clients such as MySQL Workbench, MS SQL Studio, command line SQL prompts or other client applications. The option to record this SQL traffic helps management and auditors to understand typical administration activities, alert stakeholders about suspicious queries or to comply with regulations.
The traffic recording option is enabled automatically for XTAM channels opened through the SSH Tunnel using the database’s standard ports (port 3306 for MySQL and port 1433 for MS SQL). It is also possible to provide hints to the SSH Tunnel to enable traffic monitoring established over non-standard ports. See the section below named Capturing SQL Traffic from XTAM SSH Tunnel Sessions Over Non-Standard Ports for configuration.
The traffic recording option is enabled by XTAM’s Session Control Recording roles. To capture the SQL traffic of a user or group, simply assign one of the Session Control levels that include the with Session Events options. More information about XTAM Permission Levels can be found here.
Capturing SQL Traffic from XTAM SSH Tunnel Sessions Over Standard Ports
The following section describes how to enable SQL Traffic to be recorded to a session’s Session Event report when the tunnel is using standard ports (for example, port 3306 for MySQL or port 1433 for MS SQL). It is assumed that an SSH Tunnel session is already configured properly in XTAM.
Navigate to the record that is being used to create your SSH Tunnel session and open its Permissions (Manage > Permissions).
Assign one of the with Session Events permissions levels to your user or group. If this record inherits its permissions from a parent, then modify the parent object’s permissions as needed.
Open an XTAM SSH Tunnel session using this record with this assigned user and perform some SQL commands to test the event capture.
While the session is still active (or you may complete the session), navigate to this record’s Session report and choose Recording > Events to open the Session Events report.
Observe that your executed SQL statements were captured to the Session Event log.
Capturing SQL Traffic from XTAM SSH Tunnel Sessions Over Non-Standard Ports
The following section describes how to enable SQL Traffic to be recorded to a session’s Session Event report when the tunnel is using non-standard ports (for example, not port 3306 for MySQL or port 1433 for MS SQL).
Login to XTAM as a System Administration and navigate to Administration > Record Types.
Find the Record Type that is being used for your SSH Tunnel session and click its Edit button.
On the Record Type’s Edit page, click the Add Field button and create a new field with the following configuration:
- Field Type: String
- Name: TrafficIntercepterHints
- Display Name: Traffic Intercepter Hints
- You may configure the rest of the options as needed.
Save the field and save the Record Type.
Navigate to the record that is being used to create your SSH Tunnel session and click its Edit button.
In this new Traffic Intercepter Hints field enter your port hint. The hint is a comma-, space- or semicolon-separated list of protocols and ports that should be recorded. For example the hint mssql:1444 mysql:3333 instructs recording of the MS SQL Server traffic connecting to port 1444 and MySQL traffic connecting to port 3333.
When finished, Save the record changes and follow the procedure described in the previous section to complete the configuration and to test the results.