Home » SQL & PL/SQL » SQL & PL/SQL » oracle 9i and user activity logging (oracle 9i database version on 64 bit \ Unix Aix 5.2)
oracle 9i and user activity logging [message #428382] Wed, 28 October 2009 03:02 Go to next message
user_pg_2009
Messages: 6
Registered: October 2009
Location: libreville
Junior Member

Hi expert,
i am new on the matter but i have to trace the user activity and build a small application.
those are the specifications :
i'm using 3 oracle 9i database version on 64 bit
the O.S. used is Unix Aix 5.2
for each database i am using i have to trace the user activity for all of them
i have created some triggers to get some information, but still the work is there :
create trigger ... after logoff on database .. begin insert into table xx_tab ( ,,,,) end ;
create trigger ... after ddl on database .. begin insert into table xx_tab ( ,,,,) end ;
create trigger ... after logon on database .. begin insert into table xx_tab ( ,,,,) end ;
create trigger ... after servererror on schema .. begin insert into table xx_tab ( ,,,,) end ;

Here is the issue i am faced to /
how could we simulate a create trigger ... after DML on database or schema ? as far as i know we trigger a DML only on a specific table. Is there any possibility to trigger a DML on any database object or schema directly?

for the moment i don't know if it 's the good think to do but, but it's a try, if you have any idea or other methods to trace that user activities on DB, please don't hesitate to get me informed.

Rgds,
Paul
Re: oracle 9i and user activity logging [message #428386 is a reply to message #428382] Wed, 28 October 2009 03:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
how could we simulate a create trigger ... after DML on database or schema ?

You can't.

Why don't you activate AUDIT instead of using your own trigger. Do you think about the performances issue?

Regards
Michel
Re: oracle 9i and user activity logging [message #429199 is a reply to message #428386] Mon, 02 November 2009 09:04 Go to previous messageGo to next message
user_pg_2009
Messages: 6
Registered: October 2009
Location: libreville
Junior Member
I think i will considere your view point and will close the subjet.

Thank you all,
Rgds,
Paul
Re: oracle 9i and user activity logging [message #429216 is a reply to message #428386] Mon, 02 November 2009 10:19 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Really Strict Auditing
I have seen certain government settings where, for privacy reasons, they need to track who has seen various elements of any given record. For example, the tax office keeps very
detailed records as to how much you make, what you own, and so on. This is extremely sensitive information. Whenever someone queries up someoneʹs record and sees this sensitive information, they need to audit that. This is so they can audit over time to see if people are getting into records they should not be, or to retroactively find out who had access to certain information after a leak to the press, or some other exposure.
With autonomous transactions and views, we can do this in a way that is very nonintrusive and, to the casual end user using whatever tools, is totally transparent. They will not be able to get around it, and it will not get in their way. It will, of course, add additional overhead to the query, but this is really suited to those occasions where you pull up one record at a time, not hundreds or thousands of records. Given this constraint,the implementation is quite simple. Using the EMP table as a template, we can implement an audit on the HIREDATE, SALARY, and COMMISSION columns, so whenever someone views a SALARY for example, we know who looked at it, and even what record that they saw. Weʹll start by creating an audit trail table for our EMP table, which we copied from SCOTT previously in this chapter:
[copied from expert one on one oracle]
Re: oracle 9i and user activity logging [message #429220 is a reply to message #429216] Mon, 02 November 2009 10:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just one word (or 2) fine-grained auditing.

Remember that Expert One on One was written in last century, in time of 8i, now there are in-built solutions.

Regards
Michel
Re: oracle 9i and user activity logging [message #429269 is a reply to message #429220] Mon, 02 November 2009 23:26 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Great
Any book you suggest for this era please.
Re: oracle 9i and user activity logging [message #429270 is a reply to message #429269] Mon, 02 November 2009 23:30 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
ayush_anand wrote on Mon, 02 November 2009 21:26
Great
Any book you suggest for this era please.


Your answers are just a few clicks beyond the URL below.
http://tahiti.oracle.com
Let us know when you have found your answer.

Re: oracle 9i and user activity logging [message #429271 is a reply to message #429270] Mon, 02 November 2009 23:32 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Thanks .. Smile
seems there are no shortcuts in life

[Updated on: Mon, 02 November 2009 23:32]

Report message to a moderator

Re: oracle 9i and user activity logging [message #429284 is a reply to message #429271] Tue, 03 November 2009 00:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ayush_anand wrote on Tue, 03 November 2009 06:32
Thanks .. Smile
seems there are no shortcuts in life

Yes, just click on the link I provided.

Regards
Michel

Re: oracle 9i and user activity logging [message #429390 is a reply to message #429270] Tue, 03 November 2009 08:21 Go to previous messageGo to next message
user_pg_2009
Messages: 6
Registered: October 2009
Location: libreville
Junior Member
Hi,
Asap, i get you informed. It seems it's a new track to explore.

Rgds,
Paul Cool
Re: oracle 9i and user activity logging [message #429412 is a reply to message #429390] Tue, 03 November 2009 10:39 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Here is another link. Its best info comes near the end where the author gives a summary of the various techniques that can be used to audit.

Additionally the most significant part of the article at this link is when the author asks the question of all questions:

why?... why do you want to audit?... what are the questions you are looking to answer?.

Without knowing what is important to you, you cannot possibly come up with the correct auditing solution(s) (there may be more that one).

Kevin
Re: oracle 9i and user activity logging [message #429480 is a reply to message #429412] Wed, 04 November 2009 02:19 Go to previous messageGo to next message
user_pg_2009
Messages: 6
Registered: October 2009
Location: libreville
Junior Member
Hi kevin,
Thank you for your reply. your link were valuable but i have already had a look on it. And for the moment, my issues are still there

Rgds,
Paul
Re: oracle 9i and user activity logging [message #429482 is a reply to message #429216] Wed, 04 November 2009 02:29 Go to previous messageGo to next message
user_pg_2009
Messages: 6
Registered: October 2009
Location: libreville
Junior Member
Hi ayush_anand,

Please, would you be more explicite. your statements kept my intention. Have you some good information and documentation to share with me, you can send it personally to me, here my e-mail
user_pg_2009@yahoo.fr

best regards,
Paul
Re: oracle 9i and user activity logging [message #429491 is a reply to message #429482] Wed, 04 November 2009 03:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Expert One on One is a T. Kyte's book you can find on amazon for instance. It has been superseded by his next book.
Why do you want to use a way that is 10 years old when new and more efficient solutions are available now?
Why do you want to rewrite what is built-in?

Regards
Michel

[Updated on: Wed, 04 November 2009 03:31]

Report message to a moderator

Re: oracle 9i and user activity logging [message #429493 is a reply to message #429491] Wed, 04 November 2009 03:59 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
agreed to Michel Cadot
But if you need to enable auditing over one /less number of tables still basic methods rock as you are the one who has the full control
Re: oracle 9i and user activity logging [message #429549 is a reply to message #429493] Wed, 04 November 2009 08:40 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
The question of auditing goes beyond just auditing. Again I go back to the basic issues at hand

WHY?

What do you think you will be doing with the audited data?

The answer to this question will dictate what method of auditing you will use. In the end, the ten year old method is many times the best choice.

Just cause something is old does not mean it is useless or a bad choice

For example: if you are on a system that is doing historical perspective, then your history data capture can serve as your audit data for data changes made to a system assuming you are keeping history on all tables. In my opinion, there is no Oracle built in feature that bests views and instead-of-triggers (the ten year old method) for doing this.

So in order to have a chance of getting auditing right, a DBA/Architect/Developer must first figure out what they are going to do with the audit data once they have collected it, and this includes how to get at it and report on it. Consider these factors before you make your choice.

1) what do I need to know
2) how do I get at the audit data once collected
3) will I be doing history in my system and how does this change things for auditing if at all
4) what kind of audit reports will I generate


In particular the history question has far reaching implications for solution designs. You should consider given how your system will achieve its historical perspective, how does this impact your auditing approach.

Kevin
Re: oracle 9i and user activity logging [message #429738 is a reply to message #429549] Thu, 05 November 2009 09:36 Go to previous message
user_pg_2009
Messages: 6
Registered: October 2009
Location: libreville
Junior Member
Hi all experts and gurus,

I have apreciated how much all of you have been involved on that thread topic, and i say thank you for all of you.

So, now we can consider the case (oracle 9i and user activity logging) is closed.

B.Rgds,
Herve
Previous Topic: display columns names from all_tab_columns with primary key on top rows
Next Topic: commiting the records with limit
Goto Forum:
  


Current Time: Sun Dec 04 12:39:15 CST 2016

Total time taken to generate the page: 0.15444 seconds