Enable auditing on object table

Printable View

11-19-2007, 06:09 AM
gxangel

Enable auditing on object table
Hi,

I tried to enable auditing on one of the tables using the steps below:
1. run command
  
  Code:
  
  alter system audit_trail=true scope=spfile
  
  and restart database
1. run command
  
  Code:
  
  audit delete, insert, update on test_user.test_table
1. run command 'select * from dba_obj_audit_opts;'
  Results:
  
  Code:
  
  OWNER OBJECT_NAME OBJECT_TYPE ------------------------------ ------------------------------ ----------------- ALT AUD COM DEL GRA IND INS LOC --------- --------- --------- --------- --------- --------- --------- --------- REN SEL UPD REF EXE CRE REA WRI --------- --------- --------- --- --------- --------- --------- --------- FBK --------- TEST_USER TEST_TABLE TABLE -/- -/- -/- A/A -/- -/- A/A -/- -/- -/- A/A -/- -/- -/- -/- -/- -/-
After which i run the following sql:

Code:

SQL> insert into test_table values (1234, 'test test');

and exit sqlplus.

When i login as SYSDBA and check AUD$ table, the column 'SQLTEXT' contains null and does not have the sql which was run. However, the AUD$ table register user TEST_USER ran an INSERT statement on TEST_TABLE.

Is there a parameter which must be turned on to solve this?

Thanks in advance!
11-19-2007, 08:25 AM
walterflavio

gxangel,

You will need to set the audit_trail to DB_EXTENDED and to restart the instance because the AUDIT_TRAIL parameter it is not a dynamic parameter.

Look this:

AUDIT_TRAIL.
This has four possible values:
• NONE (or FALSE) Database auditing is disabled, no matter what auditing
you attempt to configure.
• OS Audit records will be written to the operating system’s audit trail: the
Application Log on Windows, or the AUDIT_FILE_DEST directory on Unix.
• DB (or TRUE) The audit records are written to a data dictionary table,
SYS.AUD$. There are views that let you see the contents of this table.
• DB_EXTENDED As DB, but including information on the SQL statements
with bind variables that generated the audit records.

I hope I have helped you.
11-19-2007, 11:25 AM
hrishy

Hi

Did you also run this as sysdba

insert into test_table values (1234, 'test test');

regards
Hrishy
11-19-2007, 10:53 PM
gxangel

Hi,

Thanks for your help!
I've changed

Code:

AUDIT_TRAIL = DB_EXTENDED

and it shows the sql run. However, the actual values which was entered was not shown. Instead, it shows

Code:

update test_table set name=:"SYS_B_0" where id = :"SYS_B_1"

Is there a way to see the actual SQL ran?
All these insert & update statements are executed using TEST_USER.

Thanks!
11-20-2007, 04:40 AM
hrishy

Hi

Whats the parameter cursor_sharing set to ?

regards
Hrishy
11-20-2007, 06:52 AM
gxangel

Hi Hrishy,

Code:

SQL> show parameter cursor_sharing NAME TYPE ------------------------------------ -------------------------------- VALUE ------------------------------ cursor_sharing string FORCE

Thank you!
11-20-2007, 07:39 AM
hrishy

Hi

I am not sure but you might have to try and set that to cursor_sharing=exact and then try again.

regards
Hrishy
11-20-2007, 07:51 AM
davey23uk

Quote:

Originally Posted by gxangel

Hi,

Thanks for your help!
I've changed

Code:

AUDIT_TRAIL = DB_EXTENDED

and it shows the sql run. However, the actual values which was entered was not shown. Instead, it shows

Code:

update test_table set name=:"SYS_B_0" where id = :"SYS_B_1"

Is there a way to see the actual SQL ran?
All these insert & update statements are executed using TEST_USER.

Thanks!

that is the actual SQL ran, you wont be able to see the values directly from the audit values
11-21-2007, 04:37 AM
gxangel

Hi,

I did

Code:

alter system set cursor_sharing=exact scope=spfile;

and i can see the exact sql being run.

Thanks alot Hrishy!
PS: Is there any implications of changing the cursor_sharing parameter?
11-21-2007, 04:41 AM
hrishy

Hi

Offcourse there are implications you should read the performance tuning guide and also look at why this was set in your particular environment ?

regards
Hrishy