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: 59291
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: 22845
Registered: January 2009
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: 59291
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 message
Michel Cadot
Messages: 59291
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks to let us know.

Regards
Michel
Previous Topic: How to disable sql login
Next Topic: Regarding SYS Users Profile
Goto Forum:
  


Current Time: Wed Oct 01 20:10:44 CDT 2014

Total time taken to generate the page: 0.10851 seconds