SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
Configuration for monitoring all SQL statement

 
Reply to topic    SoftTree Technologies Forum Index » DB Audit, DB Mail, DB Tools View previous topic
View next topic
Configuration for monitoring all SQL statement
Author Message
Joe



Joined: 01 Mar 2002
Posts: 2

Post Configuration for monitoring all SQL statements Reply with quote

Currently I have a test server set up running Oracle 10.2.0 and I am attempting to monitor SQL statements on this database.

I have installed the DB Audit and created the DB_AUDIT tablespace. I ran the suggested script to create the permissions that the DB_AUDIT user would need in the Advance System Audit Options for Oracle.

1) I go to the Audit Method tab and choose to Install alternative system audit objects in DB_AUDIT schema, when I click Install I choose the "DB_AUDIT_TS" that I created for this and I get the following error "A valid tablespace name must be selected!"

2) This may be related to #1, but when I click the Sys Audit button and select the SQL statements tab, All Statements, All Users, By Access, Always, and then select "Audit" I am notified that system audit is enabled. Upon running several sql statements then looking at the System Audit Trail reports I cannot find any information regarding the sql statements, I do find connection information about the login/logoff of the user, but not the SQL information

Thanks

Wed Apr 12, 2006 4:16 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7839

Post Re: Configuration for monitoring all SQL statement Reply with quote

1. Please don't use DB_AUDIT for database connections. DB_AUDIT should be used as a storage schema only. When setting audit parameters you should connect as SYS or at least as a other DBA account with sufficient permissions to access SYS objects and create things..

2. Ensure DB_AUDIT has a quote for the new tablespace. If it doesn't you won't be able to create any objects there. I recommend setting UNLIMITED quote for the new tablespace.
ALTER USER DB_AUDIT QUOTE UNLIMITED ON DB_AUDIT_TS;

: Currently I have a test server set up running Oracle 10.2.0 and I am
: attempting to monitor SQL statements on this database.

: I have installed the DB Audit and created the DB_AUDIT tablespace. I ran the
: suggested script to create the permissions that the DB_AUDIT user would
: need in the Advance System Audit Options for Oracle.

: 1) I go to the Audit Method tab and choose to Install alternative system
: audit objects in DB_AUDIT schema, when I click Install I choose the
: "DB_AUDIT_TS" that I created for this and I get the following
: error "A valid tablespace name must be selected!"

: 2) This may be related to #1, but when I click the Sys Audit button and
: select the SQL statements tab, All Statements, All Users, By Access,
: Always, and then select "Audit" I am notified that system audit
: is enabled. Upon running several sql statements then looking at the System
: Audit Trail reports I cannot find any information regarding the sql
: statements, I do find connection information about the login/logoff of the
: user, but not the SQL information

: Thanks

Wed Apr 12, 2006 5:04 pm View user's profile Send private message
Joe



Joined: 01 Mar 2002
Posts: 2

Post Re: Configuration for monitoring all SQL statement Reply with quote

: 1. Please don't use DB_AUDIT for database connections. DB_AUDIT should be
: used as a storage schema only. When setting audit parameters you should
: connect as SYS or at least as a other DBA account with sufficient
: permissions to access SYS objects and create things..

I am using the SYS user to connect to the database, the DB_AUDIT user was created by DB Audit upon the first connection to the database to my best guess, I did create the Tablespace DB_AUDIT_TS on the database.

: 2. Ensure DB_AUDIT has a quote for the new tablespace. If it doesn't you
: won't be able to create any objects there. I recommend setting UNLIMITED
: quote for the new tablespace.
: ALTER USER DB_AUDIT QUOTE UNLIMITED ON DB_AUDIT_TS;

DB_AUDIT which has a default tablespace of DB_AUDIT_TS has the following
Roles

Connect

Resource
System Privileges

Execute Any Procedure

Select Any Table

Unlimited Tablespace
Object Privileges

8 sys objects

Still when I select the Audit Method and try to select a tablespace and I choose DB_AUDIT_TS when connected with the SYS user I get "A valid Tablespace must be selected!"

I am still not getting the SQLText, Statement Audit Detail, or Statement Audit Summary of any statements run against the database.


Wed Apr 12, 2006 6:15 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7839

Post Re: Configuration for monitoring all SQL statement Reply with quote

Let's not confuse 2 different things here. You are trying to make the audit records to go to the alternative audit trail table instead of the system sys.aud$, which has nothing to do with events being audited.

Let's deal with these 2 thins separately. Let's first ensure you get the events and then make them go to a different place.

So first thing first. Click System Audit menu then Set Audit Options. On that screen activate Oracle 10g tab and enable auditing for SQL queries.

Make sure the auditing is running. In SQL*Plus run "show parameter audit_trail" and verify the effective value is "TRUE" If it is not set this parameter in your [sid]init.ora file and bounce the instance.

When this is done and working let's talk about the alternative audit trail location.

: I am using the SYS user to connect to the database, the DB_AUDIT user was
: created by DB Audit upon the first connection to the database to my best
: guess, I did create the Tablespace DB_AUDIT_TS on the database.

: DB_AUDIT which has a default tablespace of DB_AUDIT_TS has the following
: Roles

: Connect

: Resource
: System Privileges

: Execute Any Procedure

: Select Any Table

: Unlimited Tablespace
: Object Privileges

: 8 sys objects

: Still when I select the Audit Method and try to select a tablespace and I
: choose DB_AUDIT_TS when connected with the SYS user I get "A valid
: Tablespace must be selected!"

: I am still not getting the SQLText, Statement Audit Detail, or Statement
: Audit Summary of any statements run against the database.

Wed Apr 12, 2006 7:15 pm View user's profile Send private message
Joe



Joined: 01 Mar 2002
Posts: 2

Post Re: Configuration for monitoring all SQL statement Reply with quote

: Let's not confuse 2 different things here. You are trying to make the audit
: records to go to the alternative audit trail table instead of the system
: sys.aud$, which has nothing to do with events being audited.

: Let's deal with these 2 thins separately. Let's first ensure you get the
: events and then make them go to a different place.

: So first thing first. Click System Audit menu then Set Audit Options. On that
: screen activate Oracle 10g tab and enable auditing for SQL queries.

: Make sure the auditing is running. In SQL*Plus run "show parameter
: audit_trail" and verify the effective value is "TRUE" If it
: is not set this parameter in your [sid]init.ora file and bounce the
: instance.

: When this is done and working let's talk about the alternative audit trail
: location.

Currently I have the following

SQL> show parameter audit_trail

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_trail string DB_EXTENDED

I set it to that value based on the text on the 10g tab prior to starting this discussion, I can definitely change it to TRUE if that is how it should be set to trace SQL statements and I have misread something.

Wed Apr 12, 2006 7:49 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7839

Post Re: Configuration for monitoring all SQL statement Reply with quote

Yes, that is correct audit_trail must be set to DB_EXTENDED, don't change it.
Have you enabled any auditing on the first tab? for example, audits for all SELECTs?

Do you have any data in sys.aud$ table or that table is empty?

: Currently I have the following

: SQL> show parameter audit_trail

: NAME TYPE VALUE
: ------------------------------------ -----------
: ------------------------------
: audit_trail string DB_EXTENDED

: I set it to that value based on the text on the 10g tab prior to starting
: this discussion, I can definitely change it to TRUE if that is how it
: should be set to trace SQL statements and I have misread something.

Wed Apr 12, 2006 9:19 pm View user's profile Send private message
Joe



Joined: 01 Mar 2002
Posts: 2

Post Re: Configuration for monitoring all SQL statement Reply with quote

: Yes, that is correct audit_trail must be set to DB_EXTENDED, don't change it.
: Have you enabled any auditing on the first tab? for example, audits for all
: SELECTs?

: Do you have any data in sys.aud$ table or that table is empty?

Ok... That solves that problem.

2 left to tackle,
1)the install of the alternative system audit objects in the DB_AUDIT schema
2)moving the data out of the source instance into a remote instance for space considerations.

Thank you for your help thus far.

Wed Apr 12, 2006 10:00 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7839

Post Re: Configuration for monitoring all SQL statement Reply with quote

1) When you get the Select Tablespace dialog, try the following
From the drop-down choose tablespace for the table and then press the Tab key to jump to the next field. From the second drop-down choose tablespace for the indexes and then press the Tab key to tab out. Press the OK button. Hope this helps.

2) When done with 1. Click Tools/Schedule Periodic Archiving menu to automate the archiving process. On the dialog, click the Install button to install the required procedures, fill the parameters for the job and use the Schedule button to schedule this process. It is also recommended that you either choose the purge option here for the archive job or use a separate purge option using Tools/Schedule Purge menu.

Here is the difference between these 2 purge methods. The first method, if enabled, will clean the audit trail in the local database immediately after the audit data is transferred to OS log files for shipping to the repository. The second option can be setup to purge audit trail based on the history size, in other words to keep x number of most recent days or months, deleting only very old stuff. Please note that some SOX reports can be run only locally as they compare data in the audit trail against data in the local data dictionary tables. So if the first option is used the local trail is clean and not all SOX reports can be run. An optimal solution is not allow the archival process to purge the data and setup the other purge to run once a day or once a week and delete all but last 2 or 3 months of data.

Now, after get the archival running smoothly, you need to automate log shipping to the repository server. The actual method depends on the operation system type. For example if you send logs to the repository fro ma Unix/Linux machine you can use regular FTP or secure FTP and schedule that process as a crontab job, running every 10-15 minutes

Here is a sample shell script that can be run using crontab

#!/bin/sh
mv *.log *.aud
ftp -n YOUR_REPOSITORY_SERVER <<!
user anonymous nopassword
cd logs
prompt
mput *.aud
bye
!
rm *.aud

Depending on the type of the repository server different method will be needed to load the shipped logs into the central repository tables. Some integration effort might be required if you are planning to ship data change logs in addition to system audit logs, especially if the data will come from heterogeneous systems, for instance logs shipped from Oracle and Sybase systems and loaded into MS SQL repository database.

: Ok... That solves that problem.

: 2 left to tackle,
: 1)the install of the alternative system audit objects in the DB_AUDIT schema
: 2)moving the data out of the source instance into a remote instance for space
: considerations.

: Thank you for your help thus far.

Wed Apr 12, 2006 10:40 pm View user's profile Send private message
Joe



Joined: 01 Mar 2002
Posts: 2

Post Re: Configuration for monitoring all SQL statement Reply with quote

: 1) When you get the Select Tablespace dialog, try the following
: From the drop-down choose tablespace for the table and then press the Tab key
: to jump to the next field. From the second drop-down choose tablespace for
: the indexes and then press the Tab key to tab out. Press the OK button.
: Hope this helps.

I must be looking in the wrong spot.

I am in the System Audit menu, Advanced Options, selecting the Audit Method tab, when I click Install under #1 there is only one drop down list to choose from.

Thu Apr 13, 2006 10:53 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7839

Post Re: Configuration for monitoring all SQL statement Reply with quote

No, that's the right place. There should be 2 drop-downs. I am looking into this.

: I must be looking in the wrong spot.

: I am in the System Audit menu, Advanced Options, selecting the Audit Method
: tab, when I click Install under #1 there is only one drop down list to
: choose from.

Thu Apr 13, 2006 1:37 pm View user's profile Send private message
Joe



Joined: 01 Mar 2002
Posts: 2

Post Re: Configuration for monitoring all SQL statement Reply with quote

: No, that's the right place. There should be 2 drop-downs. I am looking into
: this.

Here is what I have...

http://i4.photobucket.com/albums/y140/eojrr1/DBAudit.gif

Thu Apr 13, 2006 2:08 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7839

Post Re: Configuration for monitoring all SQL statement Reply with quote

For some reason the second drop-down is missing. I am not sure why. I am looking for a script that can be used to install that table and related objects w/o using GUI.

: Here is what I have...

: http://i4.photobucket.com/albums/y140/eojrr1/DBAudit.gif

Thu Apr 13, 2006 5:12 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7839

Post Re: Configuration for monitoring all SQL statement Reply with quote

It appears that scripts are complicated and differ for different Oracle versions.
We just emailed you a fix from the last version 3.0.8 build.

To see description of all changes in 3.0.8 go here
http://www.softtreetech.com/cgi_bin/mconfig.cgi?read=1162

: For some reason the second drop-down is missing. I am not sure why. I am
: looking for a script that can be used to install that table and related
: objects w/o using GUI.

Fri Apr 14, 2006 10:33 am View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » DB Audit, DB Mail, DB Tools All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.