| can not audit entry in adump [message #561908] |
Thu, 26 July 2012 04:35  |
morad_dba
Messages: 93
Registered: June 2008
|
Member |
|
|
Dear all,
I am trying to enable auditing as
SQL> alter system set audit_trail=OS SCOPE=SPFILE;
System altered.
SQL> STARTUP FORCE
ORACLE instance started.
Total System Global Area 171966464 bytes
Fixed Size 2019320 bytes
Variable Size 113246216 bytes
Database Buffers 50331648 bytes
Redo Buffers 6369280 bytes
Database mounted.
Database opened.
SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/orcl/adu
mp
audit_sys_operations boolean FALSE
audit_syslog_level string
audit_trail string OS
SQL>
SQL> create user apexos identified by abc1;
User created.
SQL> grant connect, resource to apexos;
Grant succeeded.
SQL> audit select table, insert table by apexos by access;
Audit succeeded.
SQL> audit table by apexos by access;
SQL> SELECT audit_option, failure, success, user_name
FROM dba_stmt_audit_opts;
AUDIT_OPTION FAILURE SUCCESS USER_NAME
---------------------------------------- ---------- ---------- ------------------------------
TABLE BY ACCESS BY ACCESS APEXOS
SELECT TABLE BY ACCESS BY ACCESS APEXOS
INSERT TABLE BY ACCESS BY ACCESS APEXOS
cONN APPOS/ABC1
SQL> CREATE TABLE TAB1 (ID NUMBER, NAME VARCHAR2(20));
Table created.
SQL> insert into tab1 values (10, 'Michel');
1 row created.
SQL> insert into tab1 values (30, 'Andrew');
1 row created.
SQL> select * from tab1;
ID NAME
---------- --------------------
10 Michel
30 Andrew
SQL> /
ID NAME
---------- --------------------
10 Michel
30 Andrew
SQL>
SQL> select username, timestamp, action_name, action, SES_ACTIONs, sql_text
2 from USER_audit_trail where username='APEXOS';
no rows selected
SQL>
I also did not find any file contiaing the above statement as audit record in
/u01/app/oracle/admin/orcl/adump.
There are numerous old file in the /u01/app/oracle/admin/orcl/adump locaton. But When I executed the sql statement then that time no audit file was not generated in the location.
Please help me ... how to find audit record.
Regards,
Morad.
|
|
|
|
| Re: can not audit entry in adump [message #561914 is a reply to message #561908] |
Thu, 26 July 2012 04:44  |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
1/ "STARTUP FORCE", do NOT use that just shutdown and then startup, it is not so painful to write 2 commands and it is at least far painless than to have to restore and recover your database because of a corruption.
2/ "grant connect, resource to apexos", do NOT use predefined roles, create your own, it is not so painful to create a role and grant it the appropriate privilege for your usage.
As we say in French "Laziness is the source of all evil".
3/ "no rows selected", expected
4/ "There are numerous old file in the /u01/app/oracle/admin/orcl/adump locaton. But When I executed the sql statement then that time no audit file was not generated in the location." Maybe an old file was reused. Drop all of them, restart the instance (with shudown+startup) and retry.
Regards
Michel
|
|
|
|
| Re: can not audit entry in adump [message #561929 is a reply to message #561914] |
Thu, 26 July 2012 05:14 |
morad_dba
Messages: 93
Registered: June 2008
|
Member |
|
|
Dear Michel,
I shudown database and deleted all audit files and started database. and I issued the sql statement as
SQL> CREATE TABLE TAB1 (ID NUMBER, NAME VARCHAR2(20));
Table created.
SQL> insert into tab1 values (10, 'Michel');
1 row created.
SQL> insert into tab1 values (30, 'Andrew');
1 row created.
SQL> select * from tab1;
ID NAME
---------- --------------------
10 Michel
30 Andrew
SQL> /
ID NAME
---------- --------------------
10 Michel
30 Andrew
SQL> /
ID NAME
---------- --------------------
10 Michel
30 Andrew
SQL> /
ID NAME
---------- --------------------
10 Michel
30 Andrew
SQL>
And I read audit file in the /u01/app/oracle/admin/orcl/adump
[oracle@DBTEST adump]$ cat ora_11525.aud
Thu Jul 26 17:27:46 2012
SESSIONID: "24830" ENTRYID: "7" STATEMENT: "8" USERID: "APX" USERHOST: "DBTEST" TERMINAL: "pts/1" ACTION: "1" RETURNCODE: "0" OBJ$CREATOR: "APX" OBJ$NAME: "TAB1" OS$USERID: "oracle" PRIV$USED: 40
Thu Jul 26 17:27:53 2012
SESSIONID: "24830" ENTRYID: "8" STATEMENT: "9" USERID: "APX" USERHOST: "DBTEST" TERMINAL: "pts/1" ACTION: "2" RETURNCODE: "0" OBJ$CREATOR: "APX" OBJ$NAME: "TAB1" OS$USERID: "oracle"
Thu Jul 26 17:27:58 2012
SESSIONID: "24830" ENTRYID: "9" STATEMENT: "10" USERID: "APX" USERHOST: "DBTEST" TERMINAL: "pts/1" ACTION: "2" RETURNCODE: "0" OBJ$CREATOR: "APX" OBJ$NAME: "TAB1" OS$USERID: "oracle"
Thu Jul 26 17:28:07 2012
SESSIONID: "24830" ENTRYID: "10" STATEMENT: "11" USERID: "APX" USERHOST: "DBTEST" TERMINAL: "pts/1" ACTION: "3" RETURNCODE: "0" OBJ$CREATOR: "APX" OBJ$NAME: "TAB1" OS$USERID: "oracle"
Thu Jul 26 17:28:10 2012
SESSIONID: "24830" ENTRYID: "11" STATEMENT: "12" USERID: "APX" USERHOST: "DBTEST" TERMINAL: "pts/1" ACTION: "3" RETURNCODE: "0" OBJ$CREATOR: "APX" OBJ$NAME: "TAB1" OS$USERID: "oracle"
Thu Jul 26 17:30:24 2012
SESSIONID: "24830" ENTRYID: "12" STATEMENT: "13" USERID: "APX" USERHOST: "DBTEST" TERMINAL: "pts/1" ACTION: "3" RETURNCODE: "0" OBJ$CREATOR: "APX" OBJ$NAME: "TAB1" OS$USERID: "oracle"
Thu Jul 26 17:30:27 2012
SESSIONID: "24830" ENTRYID: "13" STATEMENT: "15" USERID: "APX" USERHOST: "DBTEST" TERMINAL: "pts/1" ACTION: "3" RETURNCODE: "0" OBJ$CREATOR: "APX" OBJ$NAME: "TAB1" OS$USERID: "oracle"
So how can i customize and format the output ...
pls help me
Regards,
Morad.
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
