Home » RDBMS Server » Server Administration » Statement auditing (oracle 10g, RHEL 5 64 bit)
Statement auditing [message #561224] Thu, 19 July 2012 09:57 Go to next message
morad_dba
Messages: 73
Registered: June 2008
Member
Dear all,

I am trying to record audit info about sql statement run by user (only one audit entry per specific type of operation such as create table, or insert table). Such as if a user create three tables, but database record only one entry of create table type per session.

I am giving you all the statement I issued...

SQL> create user saimon identified by abc1;

User created.

SQL> grant connect, resource to saimon;

Grant succeeded.

SQL> audit table, insert table by saimon by session;

Audit succeeded.

SQL> show parameter audit

NAME TYPE VALUE
-------------------- ----------- -------------
audit_file_dest string /u01/app/oracle/admin/orcl/adum
audit_sys_operations boolean FALSE
audit_syslog_level string
audit_trail string DBSQL>


[oracle@DBTEST ~]$ sqlplus saimon/abc1

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 19 21:45:09 2012

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL>

SQL> create table TB1 (id number, name varchar2(20));

Table created.

SQL> create table TB3 (id number, name varchar2(20));

Table created.

SQL> select USERNAME, TERMINAL, OS_USERNAME, USERHOST, ACTION, action_name, OBJ_NAMe
2 from user_audit_trail;

USERNAME TERMINAL OS_USERNAM USERHOST ACTION ACTION_NAME OBJ_NAME
---------- ---------- ---------- ----------------- ----------------------- ----------
SAIMON pts/4 oracle DBTEST 1 CREATE TABLE TB3
SAIMON pts/4 oracle DBTEST 1 CREATE TABLE TB1
conn / as sysdba

Now my question is I have enabled statement auditing for session not by access. So only one audit entry should have been recorded for two table creation. Why database is recording every create statement?

SQL> show user
USER is "SYS"

SQL> SELECT audit_option, failure, success, user_name
2 FROM dba_stmt_audit_opts;

AUDIT_OPTION FAILURE SUCCESS USER_NAME
----------------------------------- ---------- ---------- ------------------------------
TABLE BY SESSION BY SESSION SAIMON
INSERT TABLE BY SESSION BY SESSION SAIMON

Regards,
Morad.
Re: Statement auditing [message #561236 is a reply to message #561224] Thu, 19 July 2012 11:38 Go to previous messageGo to next message
Michel Cadot
Messages: 59993
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
By SESSION/ACCESS is only meaningfull for DML not DDL as specify in the documentation http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_4007.htm#i2059073

Quote:
If you specify statement options or system privileges that audit data definition language (DDL) statements, then the database automatically audits by access regardless of whether you specify the BY SESSION clause or BY ACCESS clause.


Regards
Michel
Re: Statement auditing [message #561565 is a reply to message #561236] Tue, 24 July 2012 00:17 Go to previous message
morad_dba
Messages: 73
Registered: June 2008
Member

Dear Michel,

Thank you very much for helping me giving most effective information about statement auditing of DDL statement.
Thanks again.

Regards,
Morad.
Previous Topic: what's the difference between checkpoint_change# and controlfile_change#?
Next Topic: Job not running on defined schedule
Goto Forum:
  


Current Time: Fri Dec 19 18:13:51 CST 2014

Total time taken to generate the page: 0.06104 seconds