| audit the update/modify and insert [message #527506] |
Tue, 18 October 2011 07:18  |
needee
Messages: 19
Registered: November 2009
|
Junior Member |
|
|
I am not a dba or Oracle guy 
I am using 'Novell Sentinel Log Manager' to collect/fetch logs from my Oracle 11g R2.
To enable auditing, first I did following:
login as sys, then
SQL> create user testuser identified by "testuser";
SQL> grant connect to testuser
SQL> grant dba to sharf
SQL> grant CREATE SESSION to testuser;
SQL> grant select on v_$session to testuser;
SQL> grant select on v_$version to testuser;
SQL> grant select on SYS.DBA_AUDIT_TRAIL to testuser;
SQL> grant select_catalog_role to testuser;
SQL> grant select any dictionary to testuser;
Now logon/logof of user 'testuser' are logged  , as well as if testuser drops a table or creates a table, its also logged .
but when 'testuser' insert a new record, this information does not logged ;( while I need to know exactly what was added
SQL> insert into emp (empid, name, salary) values (10002, 'Ron', 6000)
likewise if 'testuser' modify/update an existing record it also does not logged.
SQL> update emp set salary=700 where empid=10001;
Please help me, which sql statements I have to execute to start auditing 'insert' and 'update', so that I know what was added/inserted and exactly what was updated/changed/modify by user 'testuser'
Regards
|
|
|
|
|
|
|
|
| Re: audit the update/modify and insert [message #527538 is a reply to message #527533] |
Tue, 18 October 2011 12:12  |
needee
Messages: 19
Registered: November 2009
|
Junior Member |
|
|
Hello John and Michel
I am sorry for not telling this in first place that I am setting up/configuring a Demo of 'Sentinel Log Manager' where I will be showing the Customer how easily/efficiently they can manage their Oracle Logs, and the Oracle 11g R2 is also on a lab/demo machine... nothing in production.
>it is described in the docs:
>
All the SQL commands I have ran yet are documented in the 'Log Manager' manual, so I just copy paste the SQL.
As I am not a DBA, so understanding DBA related stuff is qiute difficult for me.
I would appreciate if someone tell me the SQL command(or whatever) to enable auditing so that if 'testuser' insert or update any record it will be logged with details(what was added/inserted and exactly what was updated/changed/modify by user 'testuser')
Regards
|
|
|
|
|
|
| Re: audit the update/modify and insert [message #527553 is a reply to message #527538] |
Tue, 18 October 2011 14:53 |
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
OK, I think I understand: you want to generate a few audit records, so that you can demonstrate how your product can report them. The esiest way will be to login as sysdba, and run
AUDIT ALL STATEMENTS BY TESTUSER;
that will capture everything testuser does. So logon as testuser, do a few things, see what you get. If the default information is not good enough, or if your tool can't find it, then you'll need to set the AUDIT_TRAIL parameter to a non-default value, probably
ALTER SYSTEM SET AUDIT_TRIAL=XML,EXTENDED SCOPE=SPFILE;
and restart the database but I'm only guessing.
|
|
|
|
| Re: audit the update/modify and insert [message #527574 is a reply to message #527553] |
Wed, 19 October 2011 00:51 |
needee
Messages: 19
Registered: November 2009
|
Junior Member |
|
|
>OK, I think I understand: you want to generate a few audit
>records, so that you can demonstrate how your product can report
>them
>
yes... you got it
>The esiest way will be to login as sysdba, and run
> AUDIT ALL STATEMENTS BY TESTUSER;
>
it works, e.g if 'testuser' update/insert/select its logged, e.g when I ran:
SQL> update employee set salary=7000 where empid=10001;
Log Manager reports it like:
Quote:
Message: Action UPDATE was performed on EMPLOYEE with a return code of 0
Terminal=pts/0; OS User Name=oracle; Session ID=20739; Entry ID=15; Owner=TESTUSER; SCN=1131746;
and when I ran:
SQL> insert into employee (empid, name, salary) values (10002, 'Ron', 200000);
Log Manager reports it like:
Quote:
Message: Action INSERT was performed on EMPLOYEE with a return code of 0
Terminal=pts/0; OS User Name=oracle; Session ID=20739; Entry ID=14; Owner=TESTUSER; SCN=1131287;
But its still not reports/logs that exactly what was Inserted or what values/columns/field was Updated on EMPLOYEE table.
So is it possible that information like what(record) was inserted, and which field/value was updated ?
Regards
[Updated on: Wed, 19 October 2011 01:04] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: audit the update/modify and insert [message #527615 is a reply to message #527587] |
Wed, 19 October 2011 05:46 |
needee
Messages: 19
Registered: November 2009
|
Junior Member |
|
|
Hello Michel
>You cannot have the previous output from audit with this value of
>the parameter.
>
after following John's following instruction:
The esiest way will be to login as sysdba, and run
AUDIT ALL STATEMENTS BY TESTUSER;
Log Manager starts reporting following:
Quote:
Message: Action INSERT was performed on EMPLOYEE with a return code of 0
Terminal=pts/0; OS User Name=oracle; Session ID=20739; Entry ID=14; Owner=TESTUSER; SCN=1131287;
Message: Action INSERT was performed on EMPLOYEE with a return code of 0
Terminal=pts/0; OS User Name=oracle; Session ID=20739; Entry ID=14; Owner=TESTUSER; SCN=1131287;
----
If I follow John's following instruction, then no new event reports by Sentinel Log Manager
Quote:ALTER SYSTEM SET AUDIT_TRAIL=XML,EXTENDED SCOPE=SPFILE;
and restart the database
Solution
as sysdba
SQL> AUDIT ALL STATEMENTS BY TESTUSER;
SQL> ALTER SYSTEM SET AUDIT_TRAIL=DB,EXTENDED SCOPE=SPFILE;
SQL> shutdown immediate;
SLQ> startup
i.e AUDIT_TRAIL=DB,EXTENDED is working, while AUDIT_TRAIL=XML,EXTENDED does not.
Thanks a lot for such a nice and quick help John and Michel, really appreciate.
Regards
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
