Home » SQL & PL/SQL » SQL & PL/SQL » Get Window User Name and MAC address after insert in RFQ table in oracle application Transaction (merged) (12.2.6)
Get Window User Name and MAC address after insert in RFQ table in oracle application Transaction (merged) [message #674789] Sun, 17 February 2019 23:40 Go to next message
ashokb21
Messages: 92
Registered: May 2010
Member
Hi Team,

Need help.

Query:-

I want to capture the Window's USer name and MAC adress of the machine used to any transaction.

when i am running below script on TOAD is shows proper data but when i using this script in trigger on INSERT of table from Oracle application, is show Application server User name and IP address details.

select to_char(sysdate, 'mm-dd-yyyy HH:MI:SSam') asondate,
sys_context('userenv','OS_USER') OS_USER,
userenv('TERMINAL') UNique_machine_name ,
SYS_CONTEXT('USERENV','IP_ADDRESS') IP 
from dual;

Please help.
Re: Get Window User Name and MAC address after insert in RFQ table in oracle application Transaction [message #674792 is a reply to message #674789] Mon, 18 February 2019 00:51 Go to previous messageGo to next message
Michel Cadot
Messages: 66934
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Oracle only knows information about who is connected to it (and only if the client gives it), it can't know the information about who is connected to who connects to it... unless this later one gives it the information about the former.

So the issue is first on application server which have to gather this information.

Re: Get Window User Name and MAC address after insert in RFQ table in oracle application Transaction [message #674806 is a reply to message #674792] Mon, 18 February 2019 05:23 Go to previous messageGo to next message
ashokb21
Messages: 92
Registered: May 2010
Member
I know, but is there any way to find windows detail.
Re: Get Window User Name and MAC address after insert in RFQ table in oracle application Transaction [message #674807 is a reply to message #674806] Mon, 18 February 2019 05:33 Go to previous messageGo to next message
Michel Cadot
Messages: 66934
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Yes there are.
Have a look at Windows API.

Re: Get Window User Name and MAC address after insert in RFQ table in oracle application Transaction [message #674815 is a reply to message #674789] Mon, 18 February 2019 08:41 Go to previous messageGo to next message
BlackSwan
Messages: 26669
Registered: January 2009
Location: SoCal
Senior Member
ashokb21 wrote on Sun, 17 February 2019 21:40
Hi Team,

Need help.

Query:-

I want to capture the Window's USer name and MAC adress of the machine used to any transaction.

when i am running below script on TOAD is shows proper data but when i using this script in trigger on INSERT of table from Oracle application, is show Application server User name and IP address details.

select to_char(sysdate, 'mm-dd-yyyy HH:MI:SSam') asondate,
sys_context('userenv','OS_USER') OS_USER,
userenv('TERMINAL') UNique_machine_name ,
SYS_CONTEXT('USERENV','IP_ADDRESS') IP 
from dual;

Please help.
In a 3-tier application, code running on the DB Server is far removed from end user.

EndUser<=>browser<=>webserver<=>ApplicationServer<=>DatabaseServer

The Webserver must be programmed to provide Browser details to Application Server.
Then Application server needs to be programmed to pass the End User details on to the DB Server.
Nothing happens automagically, with explicit coding.

Re: Get Window User Name and MAC address after insert in RFQ table in oracle application Transaction [message #674816 is a reply to message #674815] Mon, 18 February 2019 09:19 Go to previous messageGo to next message
BlackSwan
Messages: 26669
Registered: January 2009
Location: SoCal
Senior Member
https://stackoverflow.com/questions/3454858/how-to-get-client-mac-address-by-a-access-on-a-website

The MAC address, by TCP/IP standards, is never communicated outside of the local-area network to which it pertains -- routers beyond that LAN don't even get the information you're trying to record. There are many other ways to try and identify unique visitors, including matching the user-agent's details in addition to the IP, serving cookies as part of your response, &c: it is, after all, a core functionality in the field of "web analytics". MAC addresses are simply not part of the gamut of techniques that it makes sense to utilize for it!-)
Re: Get Window User Name and MAC address after insert in RFQ table in oracle application Transaction [message #674865 is a reply to message #674815] Wed, 20 February 2019 00:12 Go to previous messageGo to next message
ashokb21
Messages: 92
Registered: May 2010
Member
Thanks For your reply,

Please help if you have any idea how do i get the Windows details.

Regards
Ashok
Re: Get Window User Name and MAC address after insert in RFQ table in oracle application Transaction [message #674869 is a reply to message #674865] Wed, 20 February 2019 00:57 Go to previous messageGo to next message
Michel Cadot
Messages: 66934
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please explain your question.

Re: Get Window User Name and MAC address after insert in RFQ table in oracle application Transaction [message #674879 is a reply to message #674865] Wed, 20 February 2019 08:19 Go to previous messageGo to next message
BlackSwan
Messages: 26669
Registered: January 2009
Location: SoCal
Senior Member
ashokb21 wrote on Tue, 19 February 2019 22:12
Thanks For your reply,

Please help if you have any idea how do i get the Windows details.

Regards
Ashok

https://stackoverflow.com/questions/15280402/get-client-user-name-from-a-web-application-running-in-a-web-server-with-windows
Re: Get Window User Name and MAC address after insert in RFQ table in oracle application Transaction [message #674889 is a reply to message #674879] Thu, 21 February 2019 02:47 Go to previous messageGo to next message
ashokb21
Messages: 92
Registered: May 2010
Member
Hi Team,

Thanks for reply.

i am trying this method in OAF, but which package to be import.

TextBox6_AddDataControl1.Text = Request.ServerVariables("LOGON_USER")

Re: Get Window User Name and MAC address after insert in RFQ table in oracle application Transaction [message #674890 is a reply to message #674869] Thu, 21 February 2019 03:16 Go to previous messageGo to next message
ashokb21
Messages: 92
Registered: May 2010
Member
I want to capture the Window's USer name and MAC adress of the machine used to any transaction.

when i am running below script on TOAD is shows proper data but when i using this script in trigger on INSERT of table from Oracle application, is show Application server User name and IP address details.
Michel Cadot wrote on Wed, 20 February 2019 00:57

Please explain your question.

Re: Get Window User Name and MAC address after insert in RFQ table in oracle application Transaction [message #674891 is a reply to message #674890] Thu, 21 February 2019 03:20 Go to previous messageGo to next message
cookiemonster
Messages: 13864
Registered: September 2008
Location: Rainy Manchester
Senior Member
Yes - that's expected behaviour.
You're getting the machine that's running the program that is logged onto the DB.
TOAD is logged onto the DB.
The oracle application running on the application server is logged onto the DB.

To get the details of the machines connected to the application server the application server code would need to get this data and pass it to the DB.
It isn't.

You can try asking in a forum for the application in question if there is any way to get the data you need, but if it's an Oracle Application (ie not one you wrote yourself) then I suspect you are out of luck.
Re: Get Window User Name and MAC address after insert in RFQ table in oracle application Transaction [message #674892 is a reply to message #674891] Thu, 21 February 2019 03:57 Go to previous messageGo to next message
ashokb21
Messages: 92
Registered: May 2010
Member
cookiemonster wrote on Thu, 21 February 2019 03:20
Yes - that's expected behaviour.
You're getting the machine that's running the program that is logged onto the DB.
TOAD is logged onto the DB.
The oracle application running on the application server is logged onto the DB.

To get the details of the machines connected to the application server the application server code would need to get this data and pass it to the DB.
It isn't.

You can try asking in a forum for the application in question if there is any way to get the data you need, but if it's an Oracle Application (ie not one you wrote yourself) then I suspect you are out of luck.

Ok I will close this and raise another SR on Application.
I want to capture the Window's USer name and MAC adress of the machine used to any transaction. [message #674893 is a reply to message #674789] Thu, 21 February 2019 04:00 Go to previous messageGo to next message
ashokb21
Messages: 92
Registered: May 2010
Member
Hi Team,

Need help.

Query:-

I want to capture the Window's USer name and MAC adress of the machine used to any transaction.

when i am running below script on TOAD is shows proper data but when i using this script in trigger on INSERT of table from Oracle application, is show Application server User name and IP address details.

Quote:

select to_char(sysdate, 'mm-dd-yyyy HH:MI:SSam') asondate,
sys_context('userenv','OS_USER') OS_USER,
userenv('TERMINAL') UNique_machine_name ,
SYS_CONTEXT('USERENV','IP_ADDRESS') IP
from dual;
Re: I want to capture the Window's USer name and MAC adress of the machine used to any transaction. [message #674910 is a reply to message #674893] Thu, 21 February 2019 10:05 Go to previous messageGo to next message
Michel Cadot
Messages: 66934
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You already got the answer to this, another topic will not get you more.

Re: I want to capture the Window's USer name and MAC adress of the machine used to any transaction. [message #674913 is a reply to message #674910] Thu, 21 February 2019 10:35 Go to previous messageGo to next message
cookiemonster
Messages: 13864
Registered: September 2008
Location: Rainy Manchester
Senior Member
And just to be completely clear - those userenv values you're checking will always return the values of the application server if you're using one. There is no way round this.
The program running on the application server can theoretically get the values of the browser machine and pass them to the DB but if it did so they wouldn't be stored in userenv and would have to be put elsewhere.
Re: Get Window User Name and MAC address after insert in RFQ table in oracle application Transaction (merged) [message #674915 is a reply to message #674789] Thu, 21 February 2019 13:01 Go to previous messageGo to next message
joy_division
Messages: 4949
Registered: February 2005
Location: East Coast USA
Senior Member
ashokb21 wrote on Mon, 18 February 2019 00:40

when i am running below script on TOAD is shows proper data but when i using this script in trigger on INSERT of table from Oracle application, is show Application server User name and IP address details.

select to_char(sysdate, 'mm-dd-yyyy HH:MI:SSam') asondate,
sys_context('userenv','OS_USER') OS_USER,
userenv('TERMINAL') UNique_machine_name ,
SYS_CONTEXT('USERENV','IP_ADDRESS') IP 
from dual;
I don't believe you on this one. If it is a trigger on the table, then it will show info from the database, not the application server. Triggers run deep inside the database.

Additionally, surely userenv('terminal') is not going to give you back a unique machine name.
Re: Get Window User Name and MAC address after insert in RFQ table in oracle application Transaction (merged) [message #674917 is a reply to message #674915] Thu, 21 February 2019 16:20 Go to previous messageGo to next message
BlackSwan
Messages: 26669
Registered: January 2009
Location: SoCal
Senior Member
>I want to capture the Window's USer name and MAC adress of the machine used to any transaction.
In a 3-tier application environment, I question the actual usefulness of the obtained User Name.
Why do you ASSUME that End User is sitting in front of a Windows PC as opposed to an iPhone or Android phone running an appropriate webbrowser?
The actual End User "User Name" will be meaningless to you.
What is the benefit for obtaining it & storing it in some table column?
Re: Get Window User Name and MAC address after insert in RFQ table in oracle application Transaction (merged) [message #674922 is a reply to message #674915] Fri, 22 February 2019 03:07 Go to previous messageGo to next message
cookiemonster
Messages: 13864
Registered: September 2008
Location: Rainy Manchester
Senior Member
joy_division wrote on Thu, 21 February 2019 19:01
ashokb21 wrote on Mon, 18 February 2019 00:40

when i am running below script on TOAD is shows proper data but when i using this script in trigger on INSERT of table from Oracle application, is show Application server User name and IP address details.

select to_char(sysdate, 'mm-dd-yyyy HH:MI:SSam') asondate,
sys_context('userenv','OS_USER') OS_USER,
userenv('TERMINAL') UNique_machine_name ,
SYS_CONTEXT('USERENV','IP_ADDRESS') IP 
from dual;
I don't believe you on this one. If it is a trigger on the table, then it will show info from the database, not the application server. Triggers run deep inside the database.

Additionally, surely userenv('terminal') is not going to give you back a unique machine name.
Don't know why you don't believe him - those contexts give the details of the program that connected to the DB for the current session. That's what they are supposed to do. You can see the same data in v$session and terminal and os_user isn't the same for all rows there (and if it was then you would have ask why the columns were included in that view in the first place).
If you're connected with SQLPlus or a GUI then it'll give the details of the machine running that program. If you're connected via a web-app running on an app server it'll give details of the app server.
Re: Get Window User Name and MAC address after insert in RFQ table in oracle application Transaction (merged) [message #674931 is a reply to message #674922] Fri, 22 February 2019 09:15 Go to previous message
joy_division
Messages: 4949
Registered: February 2005
Location: East Coast USA
Senior Member
cookiemonster, you know what, I was wrong.
I figured if the trigger was on the table, it runs at the database irrelevant of the client, so all those values would come from the database server, but clearly I was wrong.
I never had any use for this data, so I never used it, therefore my answer was based only on theory, a big mistake of mine. I feel bad.
Previous Topic: Parallel DDL statements in a same table with different partitions
Next Topic: Scenario Dates
Goto Forum:
  


Current Time: Sun Apr 05 04:49:11 CDT 2020