Problem solve Get help with specific problems with your technologies, process and projects.

Capturing LOGON events in 7.3

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

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.