RE: What stupid mistake am I making with this onlogin trigger?
- From: <Joel.Patterson@xxxxxxxxxxx>
- To: <nigel_cl_thomas@xxxxxxxxx>, <Jay.Miller@xxxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 26 Feb 2008 07:45:24 -0500
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@xxxxxxxxxxx
x72546
904 727-2546
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Nigel Thomas
Sent: Tuesday, February 26, 2008 7:14 AM
To: Jay.Miller@xxxxxxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
