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.

External Links

Oracle-Base: Auditing

Auditing Database Use

Configuring Oracle

You have to enable auditing on the Oracle database. Log into your database with a user that has ALTER SYSTEM privilege and execute:

• ALTER SYSTEM SET audit_trail=db_extended SCOPE=SPFILE;

This command turns on Oracle’s extended auditing capability. After executing this command, you have to reboot your database in order for it to take effect.

Building a Model for Oracle

To start, generate a model for your Oracle database.

• Select New Project from the File menu

• Select Oracle

• Add your database connection

The user that you use to connect to the database must have AUDIT ANY privilege for the script to work.

• Select your input source and press Options…

• Remove all schemas except SYS and SYSTEM

• Change Schema List Operation to be Include only schemas in list

• Press OK

The demonstration will show the tables that Lattix accesses when it creates a Lattix model for your database. Lattix primarily parses data from the SYS and SYSTEM schemata when generating a Lattix model for your database.

• Press OK to dismiss the Oracle Options dialog

• Press Create Project to create the project

Using the OracleAudit script

If you built your Lattix model using the previous instructions, it probably looks something like this:

Run the Audit Oracle script

• Select Audit Oracle from the Scripts menu

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:

Also, the script creates partitions in the DSM for each of the sessions that accessed the database while auditing was on:

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:

The information panel can also be used to explore information about the dependencies:

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:

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.