-
Enable auditing on object table
Hi,
I tried to enable auditing on one of the tables using the steps below:
- run command
Code:
alter system audit_trail=true scope=spfile
and restart database
- run command
Code:
audit delete, insert, update on test_user.test_table
- 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!
-
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.
-
Hi
Did you also run this as sysdba
insert into test_table values (1234, 'test test');
regards
Hrishy
-
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!
-
Hi
Whats the parameter cursor_sharing set to ?
regards
Hrishy
-
Hi Hrishy,
Code:
SQL> show parameter cursor_sharing
NAME TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
cursor_sharing string
FORCE
Thank you!
-
Hi
I am not sure but you might have to try and set that to cursor_sharing=exact and then try again.
regards
Hrishy
-
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
-
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?
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|