|
|
|
|
Re: Audit of input parameters in Reports 6i (merged2) Oracle 9.2.0.7.0 , Aix 5.1 [message #407520 is a reply to message #407426] |
Wed, 10 June 2009 06:59 |
|
Littlefoot
Messages: 21823 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Here's one way to do that:select
user,
sys_context('userenv', 'ip_address') ip_address
from dual If SYS_CONTEXT doesn't work in your Reports Builder version, create a (stored) function in the database and call it from the report.
ADNANTARIQ_85 | are you satisfied ?
|
Can't speak for @bradsj, but - your code is bunch of nonsense.
How do you plan to create a table with those ("USER, PATH, SYSDATE") column names? (There is a way, but you didn't show it.)
What on Earth are "path" and "terminal_ip"? How can you insert something that doesn't exist?
|
|
|
Re: Audit of input parameters in Reports 6i (merged2) Oracle 9.2.0.7.0 , Aix 5.1 [message #407523 is a reply to message #407520] |
Wed, 10 June 2009 07:04 |
cornwall
Messages: 36 Registered: June 2009
|
Member |
|
|
This is what i have coded in the After Report Trigger in case anyone needs something similar.
I decided not to audit parameter values as i require the audit to go into all of our production reports. Each report contains a different number of parameters and this would make the code different in each report.
function AfterReport return boolean is
v_report XXLBS.XXBS_AUDIT_MODULES.module_id%TYPE;
v_user XXLBS.XXBS_AUDIT_MODULES.network_user%TYPE;
v_network_pc XXLBS.XXBS_AUDIT_MODULES.network_pc%TYPE;
begin
-- Get Report ID
SRW.GET_REPORT_NAME(v_report);
-- Get Network User
Select Nvl(Translate(osuser, '?', ' '), User)
Into v_user
From v$session
Where audsid = Userenv('sessionid');
-- Get Workstation ID
Select terminal
Into v_network_pc
From v$session
Where audsid = Userenv('sessionid');
-- Add Audit Record
Insert into XXLBS.XXBS_AUDIT_MODULES
(
TIME_STAMP,
MODULE_ID,
MODULE_TYPE,
NETWORK_PC,
NETWORK_USER
)
Values
(
SYSDATE,
v_report,
'Report',
v_network_pc,
v_user
);
return (TRUE);
end;
|
|
|
|
|
Re: Audit of input parameters in Reports 6i (merged2) Oracle 9.2.0.7.0 , Aix 5.1 [message #407541 is a reply to message #407523] |
Wed, 10 June 2009 07:41 |
|
Littlefoot
Messages: 21823 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
@bradsj,
thank you for the feedback. You could, though, rewrite the whole procedure as follows - it's easier to read it: v_report audit_modules.module_id%TYPE;
begin
SRW.GET_REPORT_NAME(v_report);
Insert into AUDIT_MODULES
(TIME_STAMP,
MODULE_ID,
MODULE_TYPE,
NETWORK_PC,
NETWORK_USER
)
(select
sysdate,
v_report,
'Report',
terminal,
Nvl(Translate(osuser, '?', ' '), User)
from v$session
where audsid = Userenv('sessionid')
);
return (TRUE);
end;
|
|
|
|
Re: Audit of input parameters in Reports 6i (merged2) Oracle 9.2.0.7.0 , Aix 5.1 [message #408946 is a reply to message #407310] |
Thu, 18 June 2009 07:32 |
cornwall
Messages: 36 Registered: June 2009
|
Member |
|
|
Littlefoot,
I wonder if i could ask for some help once again....
Regarding the piece of code in the ARfterReport Trigger.
I would like to create a stored procedure on the database and then call the procedure from within the oracle report.
The procedure will just run the insert statement.
One additional problem that i have is that i cannot use the package srw to retreive the report name so presumably i would need to still get this from the srw package in the report and then pass it to the procedure ?
Kind Regards
Bradsj
|
|
|
|
|