Home » RDBMS Server » Security » audit the update/modify and insert (Oracle 11g R2 atop SUSE Linux)
audit the update/modify and insert [message #527506] Tue, 18 October 2011 07:18 Go to next message
needee
Messages: 19
Registered: November 2009
Junior Member
I am not a dba or Oracle guy Sad

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 Wink, as well as if testuser drops a table or creates a table, its also logged Wink.

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 #527511 is a reply to message #527506] Tue, 18 October 2011 07:55 Go to previous messageGo to next message
Michel Cadot
Messages: 59149
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ An application user MUST NOT have DBA role.
2/ A dba MUST NOT have privileges to modify application data.
3/ An end user MUST NOT have privilege to create or modify objects.
4/ An end user MUST NOT own the application objects.
5/ The application owner MUST be locked.

Separate the roles of your accounts.
So what is the purpose of this "testuser" account.

Regards
Michel
Re: audit the update/modify and insert [message #527533 is a reply to message #527506] Tue, 18 October 2011 11:02 Go to previous messageGo to next message
John Watson
Messages: 4571
Registered: January 2010
Location: Global Village
Senior Member
you need to set up auditing, it is described in the docs:
http://www.oracle.com/pls/db112/portal.all_books
Read the Security Guide, particularly the chapter on auditing.
But really, your DBA should be doing this: tell him what you need, and he will do it. As Michel says, if you try to set this up yourself, you may cause more problems than you solve.
Re: audit the update/modify and insert [message #527538 is a reply to message #527533] Tue, 18 October 2011 12:12 Go to previous messageGo to next message
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 #527541 is a reply to message #527538] Tue, 18 October 2011 12:41 Go to previous messageGo to next message
Michel Cadot
Messages: 59149
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Either enable audit in extended mode using AUDIT_TRAIL parameter and AUDIT statement.
Either, if the previous one is not sufficient, create a trigger on all audited table.

Regards
Michel
Re: audit the update/modify and insert [message #527553 is a reply to message #527538] Tue, 18 October 2011 14:53 Go to previous messageGo to next message
John Watson
Messages: 4571
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 Go to previous messageGo to next message
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 Wink

>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 #527580 is a reply to message #527574] Wed, 19 October 2011 01:04 Go to previous messageGo to next message
Michel Cadot
Messages: 59149
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post the result of:
show parameter audit_trail

You can also read what I posted, it tells you what you have to do next.

Regards
Michel
Re: audit the update/modify and insert [message #527584 is a reply to message #527580] Wed, 19 October 2011 02:12 Go to previous messageGo to next message
needee
Messages: 19
Registered: November 2009
Junior Member
Hello Michel

SQL> show parameter audit_trail;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
audit_trail			     string	 DB
Re: audit the update/modify and insert [message #527587 is a reply to message #527584] Wed, 19 October 2011 02:18 Go to previous messageGo to next message
Michel Cadot
Messages: 59149
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You cannot have the previous output from audit with this value of the parameter.
Also John said "AUDIT_TRIAL=XML,EXTENDED". Extended means you also have the complete statement that is audited.

Regards
Michel
Re: audit the update/modify and insert [message #527615 is a reply to message #527587] Wed, 19 October 2011 05:46 Go to previous messageGo to next message
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
Re: audit the update/modify and insert [message #527616 is a reply to message #527615] Wed, 19 October 2011 05:54 Go to previous messageGo to next message
Michel Cadot
Messages: 59149
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
DB means the audit records are stored in sys.aud$ table (or dba_audit_trail view), location your tool seems to know.
XML means the audit are stored in XML files outside the DB, location your tool seems to not know.

So have you now all the information you want?
Please post an example of what your tool now shows.

Regards
Michel
Re: audit the update/modify and insert [message #527632 is a reply to message #527616] Wed, 19 October 2011 07:18 Go to previous message
needee
Messages: 19
Registered: November 2009
Junior Member
Michel,

>So have you now all the information you want?
>
Yes Wink

>Please post an example of what your tool now shows.
>
screenshot attached

Regards
Previous Topic: how to find current user in database
Next Topic: errors in running pupbld.sql script (2 Merged)
Goto Forum:
  


Current Time: Mon Sep 22 01:50:16 CDT 2014

Total time taken to generate the page: 0.12502 seconds