OraFAQ Forum: Forms » After logon trigger

Home » Developer & Programmer » Forms » After logon trigger (oracle 9i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

After logon trigger [message #449867]

Thu, 01 April 2010 14:01

Amine
Messages: 376
Registered: March 2010

Senior Member

Hi All,
Is there a way to get the program the client is using in an after logon trigger ? This column is empty in v$session, and I think that it can be populated through DBMS_CLIENT_INFO...

Any ideas ??

Thanks

Report message to a moderator

Re: After logon trigger [message #449878 is a reply to message #449867]

Thu, 01 April 2010 16:11

gregor
Messages: 86
Registered: March 2010
Location: Germany

Member

Hi Amine,

what do you want to know after-logon
"get the program the client is using"?

is that
a) in the form: the current-form-name : use :system.current_form
b) auditing logons/logoffs
create a LOGON and LOGOFF trigger
that populate a history table
c) you will have infos in V$SESSION-Table:
to use DBMS_APPLICATION_INFO.SET_MODULE (in forms)
is a good idea, to set fields in v$session

Report message to a moderator

Re: After logon trigger [message #449879 is a reply to message #449878]

Thu, 01 April 2010 16:19

Amine
Messages: 376
Registered: March 2010

Senior Member

The aim goal of doing this is to log connexions from programs such as sqlplus, toad,...

How to populate module and program of v$session through DBMS_APPLICATION_INFO.set_module() ????

How to do this in the trigger (in the PL/SQL block)

Report message to a moderator

Re: After logon trigger [message #449880 is a reply to message #449867]

Thu, 01 April 2010 16:57

gregor
Messages: 86
Registered: March 2010
Location: Germany

Member

Hi Amine,

the DBMS_APPLICATION_INFO.set_module() must be set
in the Client(in FORMS,SQLPLUS/TOAD)_appilkation.

You an set this in your forms-module to a
value you like DBMS_APPLICATION_INFO.set_module('BINGBANG')

This is no allways possible.( exam. TOAD-Sessions)

-- generally when you can't set the "DBMS_APPLICATION_INFO.set_module()" because the user
uses SQLPLUS/TOAD

you can log connections with a database trigger:
create or replace TRIGGER TRG_LOGON_FILTER
AFTER
LOGON
ON DATABASE
declare
V_program varchar2(120);
...
begin
SELECT upper(program) into v_program FROM v$session where audsid=sys_context('USERENV','SESSIONID') and rownum=1;
if upper(v_program) in ('TOAD','SQLPLUS')) then
<write to protokoll-table>

end if;
end;

Report message to a moderator

Re: After logon trigger [message #449887 is a reply to message #449879]

Fri, 02 April 2010 00:05

Michel Cadot
Messages: 68686
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Amine wrote on Thu, 01 April 2010 23:19

The aim goal of doing this is to log connexions from programs such as sqlplus, toad,...

How to populate module and program of v$session through DBMS_APPLICATION_INFO.set_module() ????

How to do this in the trigger (in the PL/SQL block)

SQL*Plus module is set during the connection without dbms_application_info so you can get it.
TOAD uses this package so you can't get it.
Anyway this can be spoofed so you cannot rely on it.

Regards
Michel

Report message to a moderator

Re: After logon trigger [message #449922 is a reply to message #449887]

Fri, 02 April 2010 05:23

Amine
Messages: 376
Registered: March 2010

Senior Member

But a select on v$session to get the module or the program just after the connection gives nothing.

These two columns are populated when a DML is issued.

For spoofing problems, I don't care about them for now.

Thanks Michel

Report message to a moderator

Re: After logon trigger [message #449923 is a reply to message #449922]

Fri, 02 April 2010 05:40

Michel Cadot
Messages: 68686
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

But a select on v$session to get the module or the program just after the connection gives nothing.

Are you sure of this?

MICHEL> create table log (msg varchar2(200));

Table created.

MICHEL> create or replace trigger mytrg after logon on michel.schema
  2  declare
  3    l_module varchar2(100);
  4    l_program varchar2(100);
  5  begin
  6    select module, program into l_module, l_program
  7    from v$session where sid=sys_context('userenv','sid');
  8    insert into log values ('module: '||l_module||', program: '||l_program);
  9    commit;
 10  end;
 11  /

Trigger created.

MICHEL> connect michel/michel
Connected.
MICHEL> select * from log;
MSG
-------------------------------------------------------------------------------------------
module: sqlplusw.exe, program: sqlplusw.exe

1 row selected.

Regard
Michel

Report message to a moderator

Re: After logon trigger [message #449926 is a reply to message #449923]

Fri, 02 April 2010 05:57

Amine
Messages: 376
Registered: March 2010

Senior Member

I was sure until today Smile

May be my clause was always false (it was not sid=sys_context('userenv','sid')).

I'll verify this once at work,

Thanks another time,

Amine

Report message to a moderator

Re: After logon trigger [message #449929 is a reply to message #449926]

Fri, 02 April 2010 06:55

Michel Cadot
Messages: 68686
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Sorry I didn't see your version "sys_context('userenv','sid')" does not exist in 9i (iirc), you can use the following instead:

MICHEL> create table log (msg varchar2(200));

Table created.

MICHEL> create or replace trigger mytrg after logon on michel.schema
  2  declare
  3    l_module varchar2(100);
  4    l_program varchar2(100);
  5  begin
  6    select module, program into l_module, l_program
  7    from v$session where sid=(select sid from v$mystat where rownum=1);
  8    insert into log values ('module: '||l_module||', program: '||l_program);
  9    commit;
 10  end;
 11  /

Trigger created.

MICHEL> connect michel/michel
Connected.
MICHEL> select * from log;
MSG
---------------------------------------------------------------------------------
module: sqlplusw.exe, program: sqlplusw.exe

1 row selected.

Do not use "sys_context('userenv','sessionid')", it is not set at time of logon trigger.

Regards
Michel

Report message to a moderator

Previous Topic:	displaying data in a form called from another
Next Topic:	How can i Change effect of font to Strikeout?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 10:39:37 CDT 2024