Home » RDBMS Server » Security » Audit specific statment on specific table by specific user (Oracle 10g , Windows 2003)
Audit specific statment on specific table by specific user [message #555871] Tue, 29 May 2012 09:41 Go to next message
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.
Re: Audit specific statment on specific table by specific user [message #555877 is a reply to message #555871] Tue, 29 May 2012 10:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Audit specific statment on specific table by specific user [message #555901 is a reply to message #555877] Tue, 29 May 2012 14:34 Go to previous messageGo to next message
Karlia
Messages: 40
Registered: May 2011
Location: Algiers
Member
Thanks Michel,

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 ?
Re: Audit specific statment on specific table by specific user [message #555902 is a reply to message #555901] Tue, 29 May 2012 14:45 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>2 - what a user is doing
please post SQL that does as claimed above.
Re: Audit specific statment on specific table by specific user [message #555905 is a reply to message #555902] Tue, 29 May 2012 15:29 Go to previous messageGo to next message
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 ?
Re: Audit specific statment on specific table by specific user [message #555919 is a reply to message #555905] Tue, 29 May 2012 23:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Audit specific statment on specific table by specific user [message #555964 is a reply to message #555919] Wed, 30 May 2012 04:49 Go to previous messageGo to next message
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
Re: Audit specific statment on specific table by specific user [message #555971 is a reply to message #555964] Wed, 30 May 2012 05:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks to let us know.

Regards
Michel
Re: Audit specific statment on specific table by specific user [message #662784 is a reply to message #555905] Fri, 12 May 2017 03:30 Go to previous messageGo to next message
HankerPL
Messages: 2
Registered: May 2017
Junior Member
audit insert, update, delete on SIGNALSUMMARYBASE_0 by ACCESS; --OR BY SESSION --WHENEVER [NOT] SUCCESSFUL
Re: Audit specific statment on specific table by specific user [message #662799 is a reply to message #555964] Fri, 12 May 2017 06:52 Go to previous messageGo to next message
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.
Re: Audit specific statment on specific table by specific user [message #662820 is a reply to message #662784] Fri, 12 May 2017 09:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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.

Re: Audit specific statment on specific table by specific user [message #662896 is a reply to message #662820] Mon, 15 May 2017 06:41 Go to previous message
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.
Previous Topic: Commonly used root certificates
Next Topic: ORA-01924 - ALTER USER...GRANT CONNECT THROUGH APPSFER WITH ROLE
Goto Forum:
  


Current Time: Thu Mar 28 16:24:04 CDT 2024