RE: What stupid mistake am I making with this onlogin trigger?

From: <Joel.Patterson_at_crowley.com>
Date: Tue, 26 Feb 2008 07:45:24 -0500
Message-ID: <0684DA55864E404F8AD2E2EBDFD557DABE0CF7@JAXMSG01.crowley.com>

Thanks for the info: Syntax errors removed... read_module_info, l_user

declare

 l_module varchar2(48);
 l_action varchar2(32);
 l_username varchar2(30);
 l_sid integer;

begin
 dbms_application_info.read_module(l_module, l_action); l_username :=

sys_context('userenv','session_user');
 l_sid := sys_context('userenv','sessionid');
 dbms_output.put_line('User:'||l_username||', SID:'||l_sid||',

Module:'||l_module);
end;
/

Joel Patterson
Database Administrator
joel.patterson_at_crowley.com
x72546
904 727-2546

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Nigel Thomas Sent: Tuesday, February 26, 2008 7:14 AM To: Jay.Miller_at_tdameritrade.com; oracle-l_at_freelists.org Subject: Re: What stupid mistake am I making with this onlogin trigger?

Small extra point:

IMHO it's not best practice to use v$ views when you could use SYS_CONTEXT and
DBMS_APPLICATION_INFO instead - so you don't have to have too many users with
unrestricted read access to some or all of the v$ views. It's always a good
idea to minimise the scope of any grants.

(select unique(sid) from v$mystat)

can be replaced with:
  SYS_CONTEXT('userenv','sessionid')

which has the added benefit of avoiding a hash(unique) on v$mystat (370 rows in
my case).

To demonstrate:

declare

 l_module varchar2(48);
 l_action varchar2(32);
 l_username varchar2(30);
 l_sid integer;

begin
 dbms_application_info.read_module_info(l_module, l_action);  l_username := sys_context('userenv','session_user');  l_sid := sys_context('userenv','sessionid');

 dbms_output.put_line('User:'||l_user||', SID:'||l_sid||', Module:'||l_module);

end;
/

Shame that DBMS_APPLICATION_INFO doesn't have usable GET_xxx functions, but
forces the use of a procedure with output parameters. How 1960s is that? Also,
in some circumstances it might be appropriate to cover DBMS_APPLICATION_INFO
with a package that only allows gets, not sets.

NB I haven't compared the cost of these with accessing the views.

Regards Nigel

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l

Received on Tue Feb 26 2008 - 06:45:24 CST