| Statement audit option not properly working [message #559570] |
Wed, 04 July 2012 05:05  |
morad_dba
Messages: 93
Registered: June 2008
|
Member |
|
|
Dear all,
I am trying to keep record of statement audit entry. I enabled table and alter table statement auditing for session, but data dictionary view (dba_stmt_audit_opts) shows that
auditing has been enabled for (by access) so whenever a table is created or altered an entry is recorded.
I issued the following commands:
SQL> audit table, alter table by test4 by session;
Audit succeeded.
SQL> conn test4/abc
Connected.
SQL> create table tbl (id number);
Table created.
SQL> alter table tbl add name varchar2(20);
Table altered.
SQL> SELECT audit_option, failure, success, user_name
FROM dba_stmt_audit_opts
ORDER BY audit_option, user_name; 2 3
AUDIT_OPTION FAILURE SUCCESS USER_NAME
---------------------------------------- ---------- ----------
TABLE BY ACCESS BY ACCESS TEST4
ALTER TABLE BY ACCESS BY ACCESS TEST4
SQL> alter table tbl add name varchar2(20);
Table altered.
SQL> alter table tbl add email varchar2(20);
Table altered.
SQL> select OS_USERNAME, USERHOST, ACTION, action_name, OBJ_NAMe, SES_ACTIONS,
2 to_char(TIMESTAMP, 'dd-mm-yyyy hh24:mi:ss')
3 from user_audit_trail;
OS_USERNAM USERHOST ACTION ACTION_NAME OBJ_NAME SES_ACTIONS TO_CHAR(TIMESTAMP,'
---------- ---------- ---------- ---------------------------- ---------- ------------------- -------------------
oracle DBTEST 1 CREATE TABLE TBL 04-07-2012 16:53:03
oracle DBTEST 1 CREATE TABLE TBL2 04-07-2012 16:53:21
oracle DBTEST 15 ALTER TABLE TBL 04-07-2012 16:53:44
oracle DBTEST 15 ALTER TABLE TBL 04-07-2012 16:54:02
So, how can i ensure statement auditing for session (one entry for all create or alter table)?
Another issue....
I enabled statement auditing for Not successfull event.. but and entry is recorded for every successful or unsuccessful event.
SQL> audit alter table by gr whenever not successful;
Audit succeeded.
SQL> SELECT audit_option, failure, success, user_name
FROM dba_stmt_audit_opts
ORDER BY audit_option, user_name; 2 3
AUDIT_OPTION FAILURE SUCCESS USER_NAME
---------------------------------------- ---------- ---------- ------------------------------
ALTER TABLE BY ACCESS BY ACCESS GR
SQL> conn gr/gr
connected.
SQL> alter table aud_tbl add dptid number;
Table altered.
SQL> select OS_USERNAME, USERHOST, ACTION, action_name, OBJ_NAMe, SES_ACTIONS,
2 to_char(TIMESTAMP, 'dd-mm-yyyy hh24:mi:ss')
3 from user_audit_trail;
OS_USERNAM USERHOST ACTION ACTION_NAME OBJ_NAME SES_ACTIONS TO_CHAR(TIMESTAMP,'
---------- ---------- ---------- ---------------------------- ---------- ------------------- -------------------
oracle DBTEST 15 ALTER TABLE AUD_TBL 04-07-2012 16:47:05
Not successful or (successful) is working .... Every successful or unsuccessful event is recorded..
So How can is ensure only successful or unsuccessful audit record of statement auditing?
Please help me.
Regards,
Morad.
[Updated on: Wed, 04 July 2012 05:59] by Moderator Report message to a moderator |
|
|
|
| Re: Statement audit option not properly working [message #559572 is a reply to message #559570] |
Wed, 04 July 2012 06:03  |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
1/ Do not let many empty lines at the end of your post
2/ Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
3/ Also always post your Oracle version, with 4 decimals.
4/ Do NOT fake what you did and got, post the REAL execution:
a) you can't add twice the same column in the same table
b) audit contains TBL2 when your script only TBL
From this, we can't say anything of what you actually have and so we can't help in anyway.
Be honest with us.
COPY AND PASTE the SQL*Plus session, the WHOLE session starting from user(s) creation; execute a "show user" after each change in connection.
Regards
Michel
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
