I have a procedure that I use in our Oracle 8 and 9 databases that is invoked after a LOGON EVENT. The script stores the name of the user, month/year of access, and increments the number of accesses in that month/year. The script is here.
We have found it quite handy for determining who is logging on to which databases and how often. What I would really like to do it to is to have the same ability to track access in our Oracle 7.3 databases. But given that there is no LOGON EVENT, is there another way to create a trigger that does the same thing... fires when a logon is detected?
I have looked at auditing (high overhead), and I have looked at manually parsing the listener logs (time consuming), but this script seems to best meets our needs.
I don't know of any way to easily capture a logon event in 7.3. You might be able to set up a job to run every few seconds (or less) to check v$session, compare it to an earlier copy of v$session and then compare the two. For any user that was not there previously, you could write a login record to your tracking table and for any user who was there previously but is not there now, you could write a logout record. I think this would be a weak option at the very best and would think that if all you want to audit is logins and logouts, using auditing overhead shouldn't be out of control and easily maintainable. I'd probably stick with that and not try to re-invent the wheel with a solution that may be not so good or time consuming (as you mentioned in your idea to parse the listener logs).
For More Information
- Dozens more answers to tough Oracle questions from Karen Morton are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in January 2003