Oracle 8.1.7 in HP-UX B.11.0.0 : trace login and logout

[RiclyLeRoy]

I need to trace only both every login and every logout to my Oracle database. Do you suggest what and how file configuration I have to modify ?

[Jean-Luc Oudart]

Hi

very old version for oracle and HPUX 11 !

Anyway,as I understand you want to capture login and logout information.

For this you need to create triggers on the database :
- After logon trigger
- Before logoff trigger

There are manyb threads on oracle forums for this.
examples :
http://www.dba-oracle.com/art_builder_sec_audit.htm
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:49818662859946
http://www.databasejournal.com/features/oracle/article.php/3299231/The-Trigger-Happy-DBA---System-Triggers.htm
http://forums.oracle.com/forums/thread.jspa?threadID=920051

You will need to decide on what information you want to record for the table design. All depends on your specific rquirements.

Regards
Jean-Luc

[RiclyLeRoy]

I need to retrieve this information directly into listener.ora log file. I know it's possibile to activate trace level on listener.ora but I'd like knowing if this traces login/logout info and how I can set it.
I shouldn't want to fit these events on DB tables.

[Jean-Luc Oudart]

The listener will only have the information for connection over SQLNet not local connection.
May be you could detail what you try to achieve exactly ?

Jean-Luc

[RiclyLeRoy]

I'm interesting to trace who (username) and when (datetime) tries to login/logout to database according to security measures.
You said:
"the listener will only have the information for connection over SQLNet not local connection".
It's Truth, but how I can also trace Oracle access from local connections ( e.g. by console) . Can you suggest me what files I have to set please ?

[Hein van den Heuvel]

Ricky,

No disrespect intended, but just because you happen to know that the listener log file mostly provides what you desire, does NOT make it the right place to focus on.

The is an ORACLE, platform independent question.

Listen (read) carefully to what Jean-Luc offered up. Follow those links and google some more, using the terms learned.

That will lead to the right solution for you.

It may not be the solution you wanted to hear, but it will be the right one, so give Jean-Luc 10 points the next time you get that opportunity!

Cheers,
Hein.

[Jean-Luc Oudart · Accepted solution]

Hi again

I suppose you can also use the AUDIT command inn the datbase. the logging can either be recording in the database or OS file.

SQL> connect / as sysdba
Connected.
SQL> AUDIT SESSION;
Audit succeeded.


you will have to set up an init.ora parameter :audit_trail
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams016.htm


for more :
http://www.camden.rutgers.edu/HELP/Documentation/Oracle/server.815/a67781/c27audit.htm

Regards
Jean-Luc

[RiclyLeRoy]

This forum is not suitable for Oracle problems but Jean-Luc Oudart gave me good suggestions