Generating dependencies using Oracle Audit#

The OracleAudit.groovy script allows you to import dependency information from Oracle Audit Table into your Lattix model. This section describes configuring and using the OracleAudit.groovy script. The script is meant as a starting point that you can adapt to your own uses.

The Oracle Audit capability generates an audit trail in the SYS.AUD$ table. Some of the information in the table includes the following:

SESSION

Session Identifier

TERMINAL

Machine that the user performed the action from.

USERNAME

Oracle Username

OBJECT_OWNER

The owner of the object that was referenced

OBJECT_NAME

The name of the object that was referenced

ACTION

The SQL action performed

The audit script extracts this information to construct an element based on the SESSION, TERMINAL and USERNAME. The script then creates dependencies from this element to the objects referenced.

By identifying a COTS application associated with a SESSION, TERMINAL or USERNAME, it then becomes possible to extract the application’s dependencies on the database.

Run the Audit Oracle script#

  • Select Audit Oracle from the Scripts menu

image3
In the console window at the bottom of the screen, the script reports that it is Connecting to the database and that auditing has been turned on.
  • Select Project Update from the Project menu and update the model

The process of updating the Lattix model executes queries against a number of items in the SYS and SYSTEM schemas. When the update completes:

  • Click on $root in the DSM

  • Select Audit Oracle from the Scripts menu

Running the script a second time turns off auditing in the database and gathers auditing information that was generated since the script turned on auditing. In the console, the script prints the name of a temporary file into which it generates an HTML report that describes the audit data extracted from the database:
image4
Also, the script creates partitions in the DSM for each of the sessions that accessed the database while auditing was on:
image5
In this example, there were two sessions. session-632215-basement-SYSTEM is the session created by the Audit Oracle script when it turned off auditing and gathered the auditing information. The name of the session partition contains the session id (632215), the machine from which the connection was made (basement) and the name of the account used (SYSTEM). The script could be modified to generate partition names based on other information gleaned from the report.
If you click on the 4 in the DSM and show the Usage panel, you can see that the script touches the tables SYS.AUDIT$, SYS.STMT_AUDIT_OPTION_MAP, SYS.USER$ and SYS.DBA_STMT_AUDIT_OPTS:
image6
The information panel can also be used to explore information about the dependencies:
image7
From this graphic, you can see the SQL statements that created the audit record.

Modifying the Audit Oracle script#

Locating and Editing OracleAudit.groovy#

The groovy script for Audit Oracle is contained in OracleAudit.groovy, which ships in the scripts directory of your Lattix installation (i.e. C:Program FilesLattix7.2scripts). Modifying the script typically requires administrator access on Windows. If you modify the script while Lattix is running, you will have to hit the refresh button in Lattix to force the new version of the script to be loaded:
image8

Structure of a Groovy Script in Lattix#

The top part of the OracleAudit.groovy contains utilities for dumping out items in the DSM. The interesting part of the script starts at

@Action("Audit Oracle")
@Cancelable
def auditOracle() {

This the Lattix mechanism for adding a script to the scripts menu.

The script begins by loading the JDBC driver for Oracle and scanning the Lattix model for Oracle connection information. When it finds connection information, the script connects to the database to determine if auditing is on:

String databaseConnectString = "jdbc:oracle:thin:@" + host + ":" + port + ":" + name;
Connection con = DriverManager.getConnection(databaseConnectString, user, passwd);
Statement stmt = con.createStatement();
ResultSet rs = null;
rs = stmt.executeQuery("select count(*) from dba_stmt_audit_opts");
rs.next()
int auditCount = rs.getInt(1);
rs.close();
  • If auditCount is 0, then the script turns on auditing and notes the time that auditing began. The time information is used to filter out audit records that were created before this auditing session.

  • In the event that auditCount is non-zero, then the script turns off auditing and gathers the records.

  • While parsing the audit records, the loop both dumps the results to an HTML table in a log file and generates dependency information in the Lattix model.

  • The script generates a partition under the currently selected partition (remember, we selected $root, so the script creates partitions under $root). The script generates the partition names using the ordered triplet of (sessionid, session hostname, session user). Using the HTML log file, you might select other elements to comprise your generated partition.