Sophis Risque: missing audit tables
Mar
27
Written by:
3/27/2014 5:29 PM
On Sophis Risque 5.3.7 and Value equivalent version, some data needed for the pricing or that could be needed for traceability for regulatory convenience are not audited. Triggers and supplementary tables need to be defined in order to duplicate them, and to offer to the internal audit service the possibillity to know which user has modified the data.
At least, these tables need to be audited:
HISTORIQUE, containing the last prices of the instruments defined and linked to market data (RIC or bloomberg code),
DEVISEV2, the table containing the definition fo the currencies and the default family curve to use
FERIES, the table containing the holidays, linked to the currencies,
HISTOTAUX, in order to catch all modifications done on the yield curves,
MARCHEORGANISE, MO_ECHEANCE, MO_SUPPORT, MO_STRIKES, MO_AJUSTEMENT, MO_AJUSTSTRIKE, MO_FRAIS and MO_SUPPORT which correspond to the tables in which the definition of the listed options is stored.
RIC, containing the configuration used by the DRT server to retrieve the real-time quotes, or even to make a snapshot and store the close prices.
Concerning the code to add for such a trigger, it looks like the one below (for HISTORIQUE). The fact that nothing is done when updating or inserting directly using SQL scripts can be discussed. If other columns are added using NEW_HISTORY_COLUMN, it has of course to be added in the trigger.
CREATE OR REPLACE TRIGGER AUDIT_HISTORIQUE
AFTER insert or update or delete on historique for each row
declare temp_seq number;
seq_not_initialized EXCEPTION;
PRAGMA EXCEPTION_INIT(seq_not_initialized, -8002);
begin
--exit if the seqlog has not been initialized which mean the update is not done via frontend.
begin
select seqlog.currval into temp_seq from DUAL;
exception
when seq_not_initialized then
return;
--quit the trigger
end;
IF INSERTING THEN
insert into AUDIT_HISTORIQUE (
SICOVAM,
JOUR,
DATEMODIF,
MODIF_TYPE,
USERIDENT
) values (
:new.SICOVAM,
:new.JOUR,
sysdate,
1,
GETUSERID
);
ELSIF UPDATING THEN
insert into AUDIT_HISTORIQUE (
SICOVAM,
JOUR,
COURS,
COUPON,
BETA ,
P ,
H ,
B ,
D ,
T ,
A ,
DATEMODIF,
MODIF_TYPE ,
USERIDENT
) values (
:old.SICOVAM,
:old.JOUR,
:old.COURS,
:old.COUPON,
:old.BETA ,
:old.P ,
:old.H ,
:old.B ,
:old.D ,
:old.T ,
:old.A ,
sysdate,
2 ,
GETUSERID
);
elsif deleting then
insert into AUDIT_HISTORIQUE (
SICOVAM,
JOUR,
COURS,
COUPON,
BETA ,
P ,
H ,
B ,
D ,
T ,
A ,
DATEMODIF,
MODIF_TYPE ,
USERIDENT
) values (
:old.SICOVAM,
:old.JOUR,
:old.COURS,
:old.COUPON,
:old.BETA ,
:old.P ,
:old.H ,
:old.B ,
:old.D ,
:old.T ,
:old.A ,
sysdate,
3 ,
GETUSERID
);
end if;
end;
/
Another check that could be done is to add a trigger in order to disable the change of the default family curve by the user. Such behaviour can be done using the following code:
CREATE OR REPLACE TRIGGER MYBANK_FAMILY_PROHIBITED
BEFORE UPDATE ON DEVISEV2 FOR EACH ROW
BEGIN
IF (:NEW.typecourbemonetaire <> :OLD.typecourbemonetaire)
THEN
RAISE_APPLICATION_ERROR(-20001,'CAN NOT CHANGE DEFAULT CURVE FAMILY FOR CURRENCY.');
END IF;
END;
1 comment(s) so far...
Re: Sophis Risque: missing audit tables
Et for those who want to trace the change on the reporting algorithm selected on each F8, the REPORTING table can be added to the list.
By Philippe Bonneau on
1/26/2015 3:38 PM
|