Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Thoi.

Asked: August 09, 2000 - 9:00 pm UTC

Last updated: April 05, 2005 - 3:51 pm UTC

Version: version 8.0.4.0.0

Viewed 1000+ times

You Asked

I have got an Oracle version 8.0.4.0.0
I'd like to know how you get the IP address from users when they have already logged on the Database?
Thanhs beforehand.

and Tom said...

In 8.0 you can enabled auditing (set audit_trail=true in the init.ora and restart). then audit connect;

then you can:

1* select comment_text from user_audit_trail where sessionid=userenv('sessionid')
scott@ORA806.WORLD> /

COMMENT_TEXT
----------------------------------------------------------------------------------------------------
Authenticated by: DATABASE; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=138.2.5.206)(PORT=32850))

scott@ORA806.WORLD>




Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

m.p.kirankumar, July 17, 2002 - 4:50 am UTC


A reader, July 17, 2002 - 4:50 am UTC

I tom I have enabled audit_trail=true in init.ora , I am not getting any rows in user_audit_trail...

Tom Kyte
July 17, 2002 - 10:19 am UTC

then you

a) didn't restart the database (init.ora changes don't take effect till then)

or

b) did not "audit connect"

read the admin guide or the sql ref -- it documents fully how to enable auditing.

kiran, July 17, 2002 - 4:52 am UTC

hi tom, what is connect audit...

Tom Kyte
July 17, 2002 - 10:20 am UTC

it is audit connect and it audits connections - see the admin guide for details on auditing.

To those people submitting low star ratings...

DBA, July 17, 2002 - 1:31 pm UTC

Tom is right. Auditing is the only way to reliably get client IP addresses from the database.

If you are a DBA and you don't know what auditing functionality is already, you probably shouldn't be a DBA.

If you are a developer, talk to your DBA.

Stop giving Tom 1 or 2 star ratings for the correct answer because he didn't give you the answer you wanted.... the man is an Oracle god and you are lucky to get any of his time.

Turning on auditing for connect only is not a big performance hit.


last one was good

Mahesh, September 05, 2003 - 2:40 am UTC


64-bit HP machine running Oracle 8.1.6

Paul B. Parmley, April 05, 2005 - 3:51 pm UTC

Our company recently upgraded our HP to a 64-bit machine which caused the "SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') FROM DUAL" query to no longer return the IP Address. I contacted our DBA to turn on audit tracking per Tom's response and it works perfectly. Thanks!!