Skip Headers
Oracle® Audit Vault Administrator's Guide
Release 10.3

Part Number E23571-07
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

10 Audit Vault SQL Server (AVMSSQLDB) Utility Commands

Use the Audit Vault SQL Server Database (AVMSSQLDB) command-line utility to manage the relationship between Oracle Audit Vault and a Microsoft SQL Server source database instance and collector. When you run these commands, remember the following:

Table 10-1 describes the AVMSSQLDB commands and where each is used, whether on the Audit Vault Server, on the Audit Vault collection agent, or in both places.

Table 10-1 AVMSSQLDB Commands

Command Where Used? Description

add_collector

Server

Adds a collector to Oracle Audit Vault

add_source

Server

Registers an audit source with Oracle Audit Vault

alter_collector

Server

Alters the attributes of a collector

alter_source

Server

Alters the attributes of a source

drop_collector

Server

Drops a collector from Oracle Audit Vault

drop_source

Server

Drops a source from Oracle Audit Vault

-help

Both

Displays help information for the AVMSSQLDB commands

setup

Collection agent

Adds the source user credentials to the wallet, creates a database alias in the wallet for the source user, and verifies the connection to the source using the wallet

verify

Both

Verifies that the source is compatible with the collectors


10.1 avmssqldb

The AVMSSQLDB command-line utility, which you use to configure a Microsoft SQL Server database instance with Oracle Audit Vault.

Syntax

avmssqldb command -help

avmssqldb command [options] arguments

Arguments

Argument Description
command Enter one of the commands listed in Table 10-1.
arguments Enter one or more of the AVMSSQLDB command arguments.
-help Displays help information for the AVMSSQLDB commands.

Usage Notes

Issuing an AVMSSQLDB command generates the following log file: $ORACLE_HOME/av/log/srcname-mssqldb-#.log. The # is a generation number that starts from 0 (zero) and increases once the file size reaches the 100 MB limit.

10.2 add_collector

The avmssqldb add_collector command adds a collector for the given SQL Server source database instance to Oracle Audit Vault. Oracle Audit Vault verifies the source database instance for the collector requirements.

Where to Run This Command

Audit Vault Server:

  • UNIX: Set the appropriate environment variables, as described in Section 2.2.2.

  • Microsoft Windows: Go to the Audit Vault Server ORACLE_HOME\bin directory.

Syntax

avmssqldb add_collector -srcname srcname -agentname agentname
         [-collname collname] [-desc desc]

Arguments

Argument Description
-srcname srcname Enter the name of the source database instance for which the collector is to be added. Remember that the source database instance name is case-sensitive.
-agentname agentname Enter the name of the collection agent that was created when you ran the avca add_agent command. (In most cases, this is the agent that you created when you installed the Audit Vault collection agent, as described in Oracle Audit Vault Collection Agent Installation Guide.)

If you are not sure of the agent name, then you can find it as follows: Log in to the Audit Vault Console, click the Configuration tab, and then click the Agent tab to display the Agents page. The name of the agent is displayed in the Agent column.

-collname collname Create a name for the MSSQLDB collector. Optional. If you do not create a name, Oracle Audit Vault names the collector MSSQLCollector.
-desc desc Enter a brief description of the collector. Optional.

Usage Notes

  • Run any collector-specific preparation scripts before you execute the avmssqldb add_collector command.

  • The avmssqldb add_collector command prompts for the source user name and password. This user account must exist on the source database instance.

Example

The following example shows how to add the MSSQLDB collector to Oracle Audit Vault.

avmssqldb add_collector -srcname mssqldb4 -agentname agent1 
Enter a username :source_user_name
Enter a password : password

***** Collector Added Successfully*****

10.3 add_source

The avmssqldb add_source command registers a SQL Server source database instance with Oracle Audit Vault for audit data consolidation.

Where to Run This Command

Audit Vault Server:

  • UNIX: Set the appropriate environment variables, as described in Section 2.2.2.

  • Microsoft Windows: Go to the Audit Vault Server ORACLE_HOME\bin directory.

Syntax

avmssqldb add_source -src host:port|host\instance_name -srcname srcname  
[-desc desc] 

Arguments

Argument Description
-src host:port|host\instance_name Enter the source database instance connection information.Typically, the host is the fully qualified domain name or IP address of the server on which the SQL Server source database instance is running.

The syntax you use depends on your configuration. Use the following syntax for single-instance configurations where the port number is different from 1433. Separate the host and port number with a colon.

-src host:port

Use the following syntax if the instance is not on the default port or does not have a default name. For configurations with multiple instances on one server, you must only use this syntax. Separate the host name and instance name with a backslash, and then enclose them single quotation marks.

-src 'host\instance_name'
-srcname srcname Create a name for the source database instance connection. Remember that the database instance name is case-sensitive. Oracle Audit Vault uses this name to connect to the Microsoft SQL Server source database instance.
-desc desc Enter a brief description for the source database instance. Optional.

Usage Notes

The avmssqldb add_source command prompts for the source user name and password. This user account must exist on the source database instance. See the example.

Example

The following example shows how to register a source with Oracle Audit Vault.

avmssqldb add_source -src mssqlserver\hr_db -srcname mssqldb4 -desc 'HR Database'
Enter a username :source_user_name
Enter a password : password

***** Source Verified *****
***** Source Added Successfully *****

10.4 alter_collector

The avmssqldb alter_collector command modifies the attributes of an MSSQLDB collector.

Where to Run This Command

Audit Vault Server:

  • UNIX: Set the appropriate environment variables, as described in Section 2.2.2.

  • Microsoft Windows: Go to the Audit Vault Server ORACLE_HOME\bin directory.

Syntax

avmssqldb alter_collector -srcname srcname -collname collname 
      [attrname=attrvalue...attrname=attrvalue]

Arguments

Argument Description
-srcname srcname Enter the name of the source database instance to which this collector belongs. Remember that the database instance name is case-sensitive.
-collname collname Enter the name of the collector to be modified.
attrname=attrvalue Enter the attribute pair (attribute name, new attribute value) for mutable collector property and attributes for this collector type. This argument is optional.

Enclose the attribute value in double quotation marks. For multiple values, enclose the entire set in double quotation marks and separate each value with a space. For example:

...="value1 value2 value3"

Usage Notes

  • For SQL Server 2000 source databases only, the trace file (.trc) audit trail is not released to the collector until either the file reaches its maximum file size and another trace file is created, or the source database instance is shut down and restarted.

  • You can specify the SERVERSIDE_TRACE_FILEPATH or C2_TRACE_FILEPATH attributes in the following ways:

    • The value for the path can be of the form Drive:\Directory....\File Prefix.trc, enclosed in double quotation marks. For example:

      "c:\tracefiles\SQLAudit.trc"
      
    • Enter #DYNAMIC (but not enclosed in quotation marks) to enable the collector to query the SQL Server database to find the trace file paths. For example:

      ... SERVERSIDE_TRACE_FILEPATH=#DYNAMIC
      
    • You can include the asterisk (*) wildcard character to select multiple files. For example:

      ... SERVERSIDE_TRACE_FILEPATH="c:\SQLAuditFile*.trc"
      

      Be aware that if you include the asterisk (*) wildcard character in the file path, then the collector reads from all files that are affected by the wildcard. For example, if you enter c:\SQLAuditFile*.trc, then the collector reads from SQLAuditFile1.trc, SQLAuditFile2.trc, SQLAuditFile3.trc, and so on.

    • Specify the path by providing the complete file path name. For example:

      ... SERVERSIDE_TRACE_FILEPATH="c:\SQLAuditFile1.trc"
      
    • Specify the multiple trace file paths by separating each path with a semicolon (;). For example:

      ... SERVERSIDE_TRACE_FILEPATH="c:\SQLAuditFile1.trc;SQLAuditFile2.trc; c:\tracefi*.trc"
      
  • If the SERVERSIDE_TRACE_FILEPATH attribute or the C2_TRACE_FILEPATH attribute is set to null, then the SQL Server collector does not retrieve audit data from the source database instance.

  • If AUDIT_SERVERSIDE_TRACES_FLAG is not set, then collector does not check the value of SERVERSIDE_TRACE_FILEPATH. In this case, no data is collected from the server side traces, even if the value of SERVERSIDE_TRACE_FILEPATH is set. This behavior also applies to the AUDIT_C2_FLAG and C2_TRACE_FILEPATH settings, which control record collection from C2 traces.

  • For server side traces, if AUDIT_SERVERSIDE_TRACES_FLAG is set, then the collector retrieves the value of the SERVERSIDE_TRACE_FILEPATH setting. If this parameter contains the value #DYNAMIC, then the collector collects audit data from the SQL Server source database instance. This behavior also applies to the AUDIT_C2_FLAG and C2_TRACE_FILEPATH settings.

  • You can modify the collector DESCRIPTION property and one or more attributes at a time. Table 10-2 lists the collector attributes (parameters), whether the parameter is mutable, the default value, and a brief description of the attribute.

  • Note the following case-sensitivity guidelines for specifying attributes:

    • Except for the AGENTNAME attribute, the attribute names are case sensitive. Enter them in upper-case letters.

    • All the attribute values, including the AGENTNAME attribute value, are case sensitive. Enter them in the case shown the following tables.

Table 10-2 MSSQLDB Collector Attributes

Attribute Description Mutable Default Value

AGENTNAME

Name of an agent to replace the agent that was specified by the avmssqldb add_collector command that was used for this source database instance. This feature enables you to move a collector from one agent to another. It is useful for failover recovery if the host computer running the original agent fails. This attribute only applies to collectors that collect from the server-side trace logs. When you enter a value for AGENTNAME, enter it using the same case that you used when you ran the avca add_agent command.

After you replace the agent, you must run the avmssqldb setup command and avctl start_collector command. See "Examples" for more information.

Yes

NULL

DESCRIPTION

The description for this collector. The value you enter is not case sensitive.

Yes

NULL

DBCONNECTION

Number of connections to the database

No

1

AUDIT_C2_FLAG

Whether C2 logs can be collected by the MSSQLDB collector. Values can be 0 or 1.

Yes

1

AUDIT_SERVERSIDE_TRACES_FLAG

Whether server-side trace logs can be collected by the MSSQLDB collector. Values can be 0 or 1. See the usage notes.

Yes

1

AUDIT_EVENT_LOG_FLAG

Whether events logs can be collected by the MSSQLDB collector. Values can be 0 or 1. For SQL Server 2000, set this parameter to 0, because in that release, there are no auditable events written to the Windows Eventlog.

Yes

1

C2_TRACE_FILEPATH

The C2 trace file path. The value you enter is not case sensitive. See the usage notes.

Yes

NULL

SERVERSIDE_TRACE_FILEPATH

The value for server-side trace file path The value you enter is not case sensitive. See the usage notes.

Yes

NULL

DELAY_TIME

The delay time (in milliseconds) for the MSSQLDB collector to sleep when it has no records to process at a particular time. After sleeping for this amount of time, the collector wakes up and checks to see if any events have arrived.

Yes

20000 (20 seconds)

ACTIVE_DELAY_TIME

The delay time (in milliseconds) in retrieving events from an active trace file. For example, suppose you set ACTIVE_DELAY_TIME to 10000 (for 10 seconds). Audit Vault will begin to capture events that are generated now (with the event time as current timestamp) only 10 seconds after now. The reason for this setting is that Audit Vault queries for events in between timestamp ranges (for example, fromTtimestamp 1 to Timestamp 2). This setting ensures that all events in this range are available for querying. It prevents the case in which events that have Timestamp 2 are in the process of being retrieved but are not available when an auditor tries to generate a report that includes the events.

This setting applies to C2 and Server Side traces only. It does not apply to the Windows event logs. A typical value that you may want to enter is between 2000 and 10000 milliseconds.

Yes

5000 (5 seconds)

NO_OF_RECORDS

The maximum number of records to be fetched by the collector. This attribute is mutable.

Yes

1000


Examples

The following example shows how to alter the NO_OF_RECORDS attribute and the collector description for the MSSQLCollector collector in Oracle Audit Vault:

avmssqldb alter_collector -srcname mssqldb4 -collname MSSQLCollector NO_OF_RECORDS=1500 DESCRIPTION="MSSQLDB collector 45" SERVERSIDE_TRACE_FILEPATH="c:\SQLAuditFile*.trc"

***** Collector Altered Successfully *****

The following sequence of commands demonstrate how to move a collector from one collection agent to another agent:

  1. From the Audit Vault Server, configure two agents, A and B, on two separate hosts.

    For example:

    avca add_agent -agentname A -agenthost host1.example.com
    
    Adding agent...
    Enter agent user name: agent_user_name
    Enter agent user password: agent_user_pwd
    Re-enter agent user password: agent_user_pwd
    
    avca add_agent -agentname B -agenthost host2.example.com
    ...
    
  2. Configure collector L to run under agent A and collect from source S.

    For example:

    avorcldb add_collector -collname L -srcname S -agentname A
    
  3. The node that runs agent A fails.

  4. Move the collector L from agent A to agent B.

    For example:

    avorcldb alter_collector -collname L -srcname S agentname=B
    
  5. From the Audit Vault collection agent home, configure agent B to connect to source S.

    For example:

    avorcldb setup -srcname S
    
    Enter Source user name: source_user_name
    Enter Source password: password
    ...
    
  6. From the Audit Vault Server, restart the collector.

    For example:

    avctl start_collector -collname L -srcname S
    
    Starting Collector...
    Collector started successfully.
    

10.5 alter_source

The avmssqldb alter_source command modifies the attributes of a SQL Server source database instance.

Where to Run This Command

Audit Vault Server:

  • UNIX: Set the appropriate environment variables, as described in Section 2.2.2.

  • Microsoft Windows: Go to the Audit Vault Server ORACLE_HOME\bin directory.

Syntax

avmssqldb alter_source -srcname sourcename 
          [attrname=attrvalue...attrname=attrvalue]

Arguments

Argument Description
-srcname sourcename Enter the name of the source database instance to be modified. Remember that the database instance name is case-sensitive.
attrname=attrvalue Enter the attribute pair (attribute name, new attribute value) for mutable source properties and attributes for this source type. This argument is optional. Separate multiple pairs by a space on the command line.

Usage Notes

Table 10-3 lists the source attributes, a brief description of the attribute, whether the attribute is mutable, and the default value. You can modify one or more source attributes at a time.

Table 10-3 Source Attributes

Attribute Description Mutable Default Value

SOURCETYPE

The source type name for this source database instance. The default name is MSSQLDB.

No

NULL

NAME

The name for this source database instance

No

NULL

HOST

The source database instance host name

No

NULL

HOST_IP

The source database instance host IP address

No

NULL

VERSION

The source database instance version

Yes

NULL

DESCRIPTION

The description for this source database instance

Yes

NULL

PORT

A new port number for this system where the source database instance audit data resides

Yes

None


Example

The following example shows how to alter the DESCRIPTION attribute for the source database instance named mssqldb4 in Oracle Audit Vault:

avmssqldb alter_source -srcname mssqldb4 DESCRIPTION="HR Database" 

***** Source Altered Successfully *****

10.6 drop_collector

The avmssqldb drop_collector command disables (but does not remove) an MSSQLDB collector from Oracle Audit Vault.

Where to Run This Command

Audit Vault Server:

  • UNIX: Set the appropriate environment variables, as described in Section 2.2.2.

  • Microsoft Windows: Go to the Audit Vault Server ORACLE_HOME\bin directory.

Syntax

avmssqldb drop_collector -srcname srcname -collname collname

Arguments

Argument Description
-srcname srcname Enter the name of the source database instance to which the collector (specified in the -collname argument) belongs. Remember that the database instance name is case-sensitive.
-collname collname Enter the name of the collector to be dropped from Oracle Audit Vault.

Usage Notes

The drop_collector command does not delete the collector from Oracle Audit Vault. It only disables the collector. The collector metadata is still in the database after you run the drop_collector command. If you want to recreate the collector, create it with a different name.

Example

The following example shows how to drop a collector named MSSQLCollector from Oracle Audit Vault:

avmssqldb drop_collector -srcname mssqldb4 -collname MSSQLCollector

***** Collector Dropped Successfully *****

10.7 drop_source

The avmssqldb drop_source command disables (but does not remove) a SQL Server source database instance from Oracle Audit Vault.

Where to Run This Command

Audit Vault Server:

  • UNIX: Set the appropriate environment variables, as described in Section 2.2.2.

  • Microsoft Windows: Go to the Audit Vault Server ORACLE_HOME\bin directory.

Syntax

avmssqldb drop_source -srcname srcname

Arguments

Argument Description
-srcname srcname Enter the source database instance to be dropped from Oracle Audit Vault. Remember that the database instance name is case-sensitive.

Usage Notes

  • The drop_source command does not delete the source database instance from Oracle Audit Vault. It only disables the source database instance definition in Oracle Audit Vault. The source database instance metadata is still in the database after you run the drop_source command. If you want to re-create the source database instance definition, create it with a different name.

  • You cannot drop a source database instance if it has any active collectors for this source database instance. You must drop all collectors associated with the source database instance before you can run the drop_source command on it.

Example

The following example shows how to drop the source named mssqldb4 from Oracle Audit Vault:

avmssqldb drop_source -srcname mssqldb4

***** Drop Source Successfully *****

10.8 -help

The avmssqldb -help command displays help information for the AVMSSQLDB commands.

Where to Run This Command

Either Audit Vault Server and collection agent:

  • UNIX: Set the appropriate environment variables, as described in Section 2.2.2 for Audit Vault Server or Section 2.2.3 for the collection agent.

  • Microsoft Windows: Go to the Audit Vault Server or collection agent ORACLE_HOME\bin directory.

Syntax

avmssqldb -help

avmssqldb command -help

Arguments

Argument Description
command Enter the name of an AVMSSQLDB command for which you want help to appear.

Usage Notes

None

Example

The following example shows how to display general AVMSSQLDB utility help in Oracle Audit Vault:

avmssqldb -help

The following example shows how to display specific AVMSSQLDB help for the add_source command in the Audit Vault Server home.

avmssqldb add_source -help

    add_source 
      -src <host>[:<port>|\<instancename>] -srcname
      <srcname> [-desc <desc>]
 
  Purpose: The source is added to Audit Vault. 
 
  Arguments:
       -src        : Source DB connection information
       -srcname    : Name of a source 
       -desc       : Optional description of the source
 
   Examples:
     avmssqldb add_source -src 'server\instancename'
     -desc 'source for admin databases' -srcname mssource

10.9 setup

The avmssqldb setup command adds the SQL Server source user credentials to the wallet, creates a database alias in the wallet for the source user, and verifies the connection to the source using the wallet. You also can use this command to change the source user credentials in the wallet after these credentials have been changed in the source database instance.

Where to Run This Command

Audit Vault collection agent:

  • UNIX: Set the appropriate environment variables, as described in Section 2.2.3.

  • Microsoft Windows: Go to the Audit Vault collection agent ORACLE_HOME\bin directory.

Syntax

avmssqldb setup -srcname srcname 

Arguments

Argument Description
-srcname srcname Enter the name of the source database instance. Remember that the database instance name is case-sensitive.

Usage Notes

  • You cannot run this command in a UNIX environment.

  • The avmssqldb setup command prompts for the source user name and password. This user account must exist on the source database instance.

Example

avmssqldb setup -srcname mssqldb4
Enter a username : source_user_name
Enter a password : password

***** Credentials Successfully added *****

10.10 verify

The avmssqldb verify command verifies that a SQL Server source database instance is compatible for setting up the specified collector.

Where to Run This Command

Either Audit Vault Server and collection agent:

  • UNIX: Set the appropriate environment variables, as described in Section 2.2.2 for Audit Vault Server or Section 2.2.3 for the collection agent.

  • Microsoft Windows: Go to the Audit Vault Server or collection agent ORACLE_HOME\bin directory.

Syntax

avmssqldb verify -src host:port|host\instance_name

Arguments

Argument Description
-src host:port|host\instance_name Enter the source database instance connection information.Typically, the host is the fully qualified domain name or IP address of the server on which the SQL Server database instance is running.

The syntax you use depends on your configuration. Use the following syntax for single-instance configurations where the port number is different from 1433. Separate the host and port number with a colon.

-src host:port

Use the following syntax if the instance is not on the default port or does not have a default name. For configurations with multiple instances on one server, you must only use this syntax. Separate the host name and instance name with a backslash, and then enclose them single quotation marks.

-src 'host\instance_name'

Usage Notes

  • The avmssqldb verify command checks the following:

    • Whether the version of the SQL Server database is supported: SQL Server 2000 or SQL Server 2005

    • Whether the source user has the required privileges in the source database instance that is to be registered with Oracle Audit Vault

    • Whether auditing (C2 auditing and server-side trace auditing) is enabled in the source database instance

  • If you installed the collection agent on a Microsoft Windows computer, then run the avmssqldb verify command from the ORACLE_HOME\bin directory. For UNIX or Linux installations, set the appropriate environment variables before running this command. See Section 2.2 for more information.

  • The avmssqldb verify command prompts for the source user name and password. This user account must exist on the source database instance.

Example

The following example verifies that the source is compatible with the MSSQLDB collector on Windows.

avmssqldb verify -src 192.0.2.1:4523
Enter a username : source_user_name
Enter a password : password

***** Source Verified *****