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

Hi Burt and Everyone,

Could you please give me some hints about auditing to get the IP address of all clients etc?
Should I use procedure to do the auditing?

Regards

David

"Burt Peltier" <burttemp1ReMoVeThIs_at_bellsouth.net> wrote in message news:<%XRZb.41457$kR3.10734_at_bignews4.bellsouth.net>...
> 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
Received on Sun Feb 22 2004 - 00:05:25 CST