Home » SQL & PL/SQL » SQL & PL/SQL » How to get login and hostname from the invoker of a procedure
How to get login and hostname from the invoker of a procedure [message #259446] Wed, 15 August 2007 11:27 Go to next message
diribuf
Messages: 8
Registered: August 2007
Location: Rio de Janeiro, Brazil
Junior Member
I need to know the login (username) and hostname (or IP) of the invoker of a given procedure so I can insert it on a table.

I've looked all over for some variables that would give me that. I know those infos can be found at v$session, but how can I know what is the current session?

Or is there a easier way?

Thanks in advance,
Christian
Re: How to get login and hostname from the invoker of a procedure [message #259447 is a reply to message #259446] Wed, 15 August 2007 11:33 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>I need to know the login (username) and hostname (or IP) of the invoker of a given procedure so I can insert it on a table.
Is this a 2 tier or 3 tier application environment?
Re: How to get login and hostname from the invoker of a procedure [message #259448 is a reply to message #259447] Wed, 15 August 2007 11:37 Go to previous messageGo to next message
diribuf
Messages: 8
Registered: August 2007
Location: Rio de Janeiro, Brazil
Junior Member
anacedent wrote on Wed, 15 August 2007 13:33
>I need to know the login (username) and hostname (or IP) of the invoker of a given procedure so I can insert it on a table.
Is this a 2 tier or 3 tier application environment?

Sorry, I'm not sure and don't know how this is relevant.

It is a simple procedure. A user will login into the database and execute it to execute some tasks. I'd like the capture it's username and host (the machine from where this is user logged from) to log these infos to a table.
Re: How to get login and hostname from the invoker of a procedure [message #259450 is a reply to message #259446] Wed, 15 August 2007 11:45 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>Sorry, I'm not sure and don't know how this is relevant.
You don't know what your don't know.
In a 3 tier application environment, the user connects to the Apps. Server & the App. Server connects (logs in) to the database
& your PL/SQL won't know who the user is unless the application provides that information.

Search the Beginner forum for a recent post of mine on "trigger not firing".
Re: How to get login and hostname from the invoker of a procedure [message #259455 is a reply to message #259446] Wed, 15 August 2007 11:54 Go to previous messageGo to next message
sarans
Messages: 30
Registered: November 2006
Location: UK
Member
The following works only within the PL/SQL calls.

Use the following to get the user Login(Oracle user)

SELECT user FROM DUAL;

Use the following to get the IP address.

SELECT SYS_CONTEXT ('USERENV', 'IP_ADDRESS') FROM DUAL;

Confirm whether it worked for you????

[Updated on: Wed, 15 August 2007 11:58]

Report message to a moderator

Re: How to get login and hostname from the invoker of a procedure [message #259456 is a reply to message #259446] Wed, 15 August 2007 11:57 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
You can also use:

 
SELECT SYS_CONTEXT ('USERENV', 'OS_USER') FROM DUAL;


This will get you the operating system logged in user id.
Re: How to get login and hostname from the invoker of a procedure [message #259457 is a reply to message #259446] Wed, 15 August 2007 11:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> SQL> select sys_context('userenv','sid') sid, sys_context('userenv','session_user') username,
  2         sys_context('userenv','os_user') osuser, sys_context('userenv','host') host
  3  from dual;
SID        USERNAME   OSUSER                         HOST
---------- ---------- ------------------------------ ------------------------------
150        MICHEL     XXXXXXXXX\Michel               WORKGROUP\XXXXXXXXX

1 row selected.

(Of course I replace confidential data with X)

Regards
Michel
Re: How to get login and hostname from the invoker of a procedure [message #259458 is a reply to message #259446] Wed, 15 August 2007 12:01 Go to previous messageGo to next message
diribuf
Messages: 8
Registered: August 2007
Location: Rio de Janeiro, Brazil
Junior Member
Thanks a lot everyone. I guess the 'user' variable and USERENV(host) are exactly what I need.
Re: How to get login and hostname from the invoker of a procedure [message #259462 is a reply to message #259446] Wed, 15 August 2007 12:09 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Just as an FYI:

The USER variable is the database user, which could be different from the OS_USER.



Re: How to get login and hostname from the invoker of a procedure [message #259745 is a reply to message #259455] Thu, 16 August 2007 06:52 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
sarans wrote on Wed, 15 August 2007 18:54
The following works only within the PL/SQL calls.

Use the following to get the user Login(Oracle user)

SELECT user FROM DUAL;

Use the following to get the IP address.

SELECT SYS_CONTEXT ('USERENV', 'IP_ADDRESS') FROM DUAL;

Confirm whether it worked for you????


Don't select from dual what you can assign as variable.
Re: How to get login and hostname from the invoker of a procedure [message #265546 is a reply to message #259745] Thu, 06 September 2007 11:18 Go to previous messageGo to next message
sarans
Messages: 30
Registered: November 2006
Location: UK
Member
Frank

Select from dual is just to explain how it works instantly thats it....

Saran.
Re: How to get login and hostname from the invoker of a procedure [message #265594 is a reply to message #259455] Thu, 06 September 2007 12:33 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
sarans wrote on Wed, 15 August 2007 18:54
The following works only within the PL/SQL calls.

Use the following to get the user Login(Oracle user)

SELECT user FROM DUAL;



Looks to me you are talking about pl/sql code here..
Re: How to get login and hostname from the invoker of a procedure [message #265596 is a reply to message #265594] Thu, 06 September 2007 12:40 Go to previous messageGo to next message
sarans
Messages: 30
Registered: November 2006
Location: UK
Member
Is that the original question asked to get the values within the pl/sql - procedure??? if its not pl/sql what is that you are talking about...

correct me if I am wrong....
Re: How to get login and hostname from the invoker of a procedure [message #265648 is a reply to message #265596] Thu, 06 September 2007 15:53 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If it's pl/sql, then you should assign the values to a variable directly, not select them from dual. That was my original point.

[Updated on: Thu, 06 September 2007 15:53]

Report message to a moderator

Re: How to get login and hostname from the invoker of a procedure [message #265732 is a reply to message #265648] Fri, 07 September 2007 03:42 Go to previous messageGo to next message
sarans
Messages: 30
Registered: November 2006
Location: UK
Member
I agree to your point, but by using dual the user can view the value instantly, this is for convenient, by this way the user can understand what is the return value in the sqlplus or toad, then within the procedure he can assign to an variable as you have mentioned.... this is my point... hope its clear...
Re: How to get login and hostname from the invoker of a procedure [message #265802 is a reply to message #265732] Fri, 07 September 2007 07:51 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
var := SYSDATE is faster than
select sysdate into var from dual;
as Frank was stating. No need to "hit" the database.

I don't know what you mean by the user being able to view the value instantly.

[Updated on: Fri, 07 September 2007 07:51]

Report message to a moderator

Previous Topic: Number of rows & size of tables of a tablespace
Next Topic: Dense Rank - help needed
Goto Forum:
  


Current Time: Fri Dec 02 23:19:35 CST 2016

Total time taken to generate the page: 0.12729 seconds