Enable auditing on object table

[gxangel]

Hi,

I tried to enable auditing on one of the tables using the steps below:

1. run command

   alter system audit_trail=true scope=spfile

   and restart database

1. run command

   audit delete, insert, update on test_user.test_table

1. run command 'select * from dba_obj_audit_opts;'
   Results:
   

   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:

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!

[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.

[hrishy]

Hi

Did you also run this as sysdba

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

regards
Hrishy

[gxangel]

Hi,

Thanks for your help!
I've changed

AUDIT_TRAIL = DB_EXTENDED

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

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!

[hrishy]

Hi

Whats the parameter cursor_sharing set to ?

regards
Hrishy

[gxangel]

Hi Hrishy,

SQL> show parameter cursor_sharing

NAME                                 TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
cursor_sharing                       string
FORCE

Thank you!

[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

[davey23uk]

Hi,

Thanks for your help!
I've changed

AUDIT_TRAIL = DB_EXTENDED

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

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

[gxangel]

Hi,

I did

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?

[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