OraFAQ Forum: Security » Audit specific statment on specific table by specific user

Home » RDBMS Server » Security » Audit specific statment on specific table by specific user (Oracle 10g , Windows 2003)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Audit specific statment on specific table by specific user [message #555871]

Tue, 29 May 2012 09:41

Karlia
Messages: 40
Registered: May 2011
Location: Algiers

Member

Hello everybody,

I'd like to know if it is possible to track DML actions issued on a specific table by a specific user, for example , i tried :

AUDIT SELECT on SCOTT.DEPT by HR by ACCESS;

I get an error, where is my syntax error ?

Please don't say me to create a trigger, i want to know if it's possible to do it without trigger ?

Thanks.

Report message to a moderator

Re: Audit specific statment on specific table by specific user [message #555877 is a reply to message #555871]

Tue, 29 May 2012 10:22

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

I get an error, where is my syntax error ?

The error is that this syntax does not exist.

SQL> AUDIT SELECT on SCOTT.DEPT by HR by ACCESS;
AUDIT SELECT on SCOTT.DEPT by HR by ACCESS
                              *
ERROR at line 1:
ORA-01708: ACCESS or SESSION expected

So, no, you cannot do it.

Regards
Michel

Report message to a moderator

Re: Audit specific statment on specific table by specific user [message #555901 is a reply to message #555877]

Tue, 29 May 2012 14:34

Karlia
Messages: 40
Registered: May 2011
Location: Algiers

Member

Thanks Michel,

So, in oracle we can audit (without using triggers) :

1 -

2 -

but never both of them in the same time , isn't it ?

Report message to a moderator

Re: Audit specific statment on specific table by specific user [message #555902 is a reply to message #555901]

Tue, 29 May 2012 14:45

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>2 - what a user is doing
please post SQL that does as claimed above.

Report message to a moderator

Re: Audit specific statment on specific table by specific user [message #555905 is a reply to message #555902]

Tue, 29 May 2012 15:29

Karlia
Messages: 40
Registered: May 2011
Location: Algiers

Member

I mean :
when i want to track all INSERTs and UPDATEs on SCOTT.EMP i have just to issue :

 AUDIT INSERT, UPDATE on SCOTT.EMP

On the other hand,o
to track all INSERTs adn UPDATEs issued by HR, we have only to execute :

AUDIT INSERT TABLE, UPDATE TABLE by HR

but the first auditing generates entries when INSERTs and UPDATEs are executed on EMP by every user , not only HR, while in the second auditing , all INSERTs and UPDATEs done by HR are tracked not only those that concern SCOTT.EMP

So, according to MICHEL, to audit just what is issued by HR on only SCOTT.EMP, we have to user triggers , am i right ?

Report message to a moderator

Re: Audit specific statment on specific table by specific user [message #555919 is a reply to message #555905]

Tue, 29 May 2012 23:22

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

So, in oracle we can audit (without using triggers) :
1 - What is done on an object
or
2 - what a user is doing
but never both of them in the same time , isn't it ?

In original audit, yes.

Quote:

am i right ?

You can also have a look at fine-grained auditing.

Regards
Michel

Report message to a moderator

Re: Audit specific statment on specific table by specific user [message #555964 is a reply to message #555919]

Wed, 30 May 2012 04:49

Karlia
Messages: 40
Registered: May 2011
Location: Algiers

Member

GREAT, GREAT ! Michel,
That's what i was looking for , look what i did with FINE-GRAINED-AUDITING:

begin
dbms_fga.add_policy (
object_schema   => 'SCOTT',
object_name     => 'DEPT',
policy_name     => 'DEPT_TRAIL',
statement_types => 'INSERT, UPDATE',
audit_condition => 'USER=''HR'''
);
end;

And it worked ! Thanks to every body

Report message to a moderator

Re: Audit specific statment on specific table by specific user [message #555971 is a reply to message #555964]

Wed, 30 May 2012 05:02

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Thanks to let us know.

Regards
Michel

Report message to a moderator

Re: Audit specific statment on specific table by specific user [message #662784 is a reply to message #555905]

Fri, 12 May 2017 03:30

HankerPL
Messages: 2
Registered: May 2017

Junior Member

audit insert, update, delete on SIGNALSUMMARYBASE_0 by ACCESS; --OR BY SESSION --WHENEVER [NOT] SUCCESSFUL

Report message to a moderator

Re: Audit specific statment on specific table by specific user [message #662799 is a reply to message #555964]

Fri, 12 May 2017 06:52

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

Karlia wrote on Wed, 30 May 2012 04:49

GREAT, GREAT ! Michel,
That's what i was looking for , look what i did with FINE-GRAINED-AUDITING:
begin
dbms_fga.add_policy (
object_schema   => 'SCOTT',
object_name     => 'DEPT',
policy_name     => 'DEPT_TRAIL',
statement_types => 'INSERT, UPDATE',
audit_condition => 'USER=''HR'''
);
end;
And it worked ! Thanks to every body

Even without FGA, what would have been so bad about auditing all users who perform the operation, then simply filtering out who you are interested in when you query DBA_AUDIT_TRAIL? That would have been the 'standard' approach. That's the beauty of a SELECT statement .. there is always a WHERE clause available for your use.

Report message to a moderator

Re: Audit specific statment on specific table by specific user [message #662820 is a reply to message #662784]

Fri, 12 May 2017 09:59

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

HankerPL wrote on Fri, 12 May 2017 10:30

audit insert, update, delete on SIGNALSUMMARYBASE_0 by ACCESS; --OR BY SESSION --WHENEVER [NOT] SUCCESSFUL

Great answer 5 years later... but it is wrong.
You have only one chance to make a good first impression... you failed. Too bad.

Report message to a moderator

Re: Audit specific statment on specific table by specific user [message #662896 is a reply to message #662820]

Mon, 15 May 2017 06:41

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

Michel Cadot wrote on Fri, 12 May 2017 09:59

HankerPL wrote on Fri, 12 May 2017 10:30
audit insert, update, delete on SIGNALSUMMARYBASE_0 by ACCESS; --OR BY SESSION --WHENEVER [NOT] SUCCESSFUL
Great answer 5 years later... but it is wrong.
You have only one chance to make a good first impression... you failed. Too bad.

And because he revived a 5-year old thread, it suckered me into asking a question in response to a 5-year old message. Shame on me AND HankerPL.

Report message to a moderator

Previous Topic:	Commonly used root certificates
Next Topic:	ORA-01924 - ALTER USER...GRANT CONNECT THROUGH APPSFER WITH ROLE

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jan 17 21:34:20 CST 2025