Re: Logon Trigger for getting info of IP Address,etc into a table

Use system variable ora_client_ip_address instead of SYS_CONTEXT('USERENV','IP_ADDRESS') . This worked for my similar type trigger.

Oh yeah, you should not have to issue the grant insert on the table. The PlSql runs as SYSTEM which owns the table and so it already has permission to insert.

Also, I have never used the SYS_CONTEXT call, but there are other ways to get some of the info you want. Actually, I could be mistaken but this might be a good way to capture 3-tier environment info? that our method does not capture.

Our method only captures the client piece (middle-tier of a 3-tier) information, which is what auditing can capture too (I think).

Anyway, I also prefer the Plsql trigger method for a simple capture of some limited logon info rather than auditing, which I know others would disagree with.

Note: Whenever you can select something from dual, (like user or sysdate) , then you can just use the variable insert of selecting it from dual.

A quick revise of your insert would look like this ( not sure what you see as the difference between userid and user - 1st 2 columns):

  Insert Into
client_info(client_id,client_name,work_station,host_name,database_server,db_ domain,ip_address,network_protocol)
  Values
 ( SYS_CONTEXT('USERENV','CURRENT_USERID'),    User,
   SYS_CONTEXT('USERENV','OS_USER'),
   Userenv('TERMINAL'),

   ora_database_name,
   SYS_CONTEXT('USERENV','DB_DOMAIN'),
   ora_client_ip_address,
   SYS_CONTEXT('USERENV','NETWORK_PROTOCOL')

Note: Some of the other SYS_CONTEXT info can be selected from V$ views (like os_user from v$session).

-- 

"David" <dt_146_at_yahoo.co.in> wrote in message
news:d5840ebd.0402210240.416ec33d_at_posting.google.com...


> Hi everyone,

>

> With regards to getting the client's information like IP Address,Host

> Name etc. ,

> I created the following table "CLIENT_INFO":

>

> Sql> connect system/manager

> sql>CREATE TABLE CLIENT_INFO(client_id varchar2(20),client_name

> VARCHAR2(30),

> work_station varchar2(40),host_name varchar2(30),database_server

> varchar2(40),

> db_domain varchar2(40),ip_address varchar2(30)

> ,network_protocol varchar2(60));

>

> sql>grant select,insert on client_info to public;

> sql >create public synonym client_info for client_info;

>

>

>

> Then I create a LOGON trigger in order to store the required

> Information of all the clients into my table

> "CLIENT_INFO":

>

> LOGON Trigger:

>

> SQl> Connect system/manager

> Sql>create or replace trigger logon_trigger

> after logon ON database

> begin

>

> insert into client_info(client_id

> ,client_name,work_station,host_name,database_server,

> db_domain,ip_address,network_protocol)

> select

SYS_CONTEXT('USERENV','CURRENT_USERID'),SYS_CONTEXT('USERENV','CURRENT_USER'
),


> SYS_CONTEXT('USERENV','OS_USER'),SYS_CONTEXT('USERENV','HOST'),

> SYS_CONTEXT('USERENV','DB_NAME'),SYS_CONTEXT('USERENV','DB_DOMAIN'),

>

SYS_CONTEXT('USERENV','IP_ADDRESS'),SYS_CONTEXT('USERENV','NETWORK_PROTOCOL'
)


> FROM DUAL;

> COMMIT;

> end if;

>

> END;

> /

>

> I created the above Logon trigger,so every session when logon to

> database, insert its own environment info. into the client_info table.

>

> But when I run the query >select * from client_info;

>

> It displays the information for only two users(system,sys).However

> there are several other sessions connected to the database like

> scott,test etc.

> And also there is null value for IP_Address column,why?

>

>  for example:

>

>

> CLIENT_ID  CLIENT_NAME  WORK_STATION         HOST_NAME

> DATABASE_SERVER   DB_DOMAIN  IP_ADDRESS

> 5           SYSTEM      SAMAD\Administrator   RUSH\SAMAD     TEST

> 0           SYS         SAMAD\Administrator   RUSH\SAMAD     TEST

> 5           SYSTEM      SAMAD\Administrator   RUSH\SAMAD     TEST

> 0           SYS         SAMAD\Administrator   RUSH\SAMAD     TEST

> 5           SYSTEM      SAMAD\Administrator   RUSH\SAMAD     TEST

> 0           SYS         SAMAD\Administrator   RUSH\SAMAD     TEST

> 6 rows selected.

>

>

> How should I get the required info from my historical table

> "CLIENT_INFO" for each and  every user.

>

> I am looking forward to your suggestion.

>

> Thanks

>

> David