ITQuants blog

Sophis Risque: how to declare a user for SQL insertion of deals

Jun 14

Written by:
6/14/2013 11:58 AM  RssIcon

If you need to run SQL scripts in order to update some deals stored into the Oracle table of deals, HISTOMVTS, several things have to be done in  order to permit the update done by the Oracle triggers at the same time. The steps to respect are the following ones:

- either deactivate the trigger..., not really possible on a production database when users are connected,

- or permit to the Oracle function getuserid to run without error, but returning null, by calling into the same Oracle session:

select seqlog.nextval from dual;


- or, knowing on which computer the script will run, inserting a record into the Sophis audit table which logs the connections, SECURITY_LOG, using for example this script (using MANAGER for the user ident uident):

02.temp number := 0;
03.begin getnextloginid() into temp from dual;
05.insert into security_log (ident, uident, eventdate, workstation, category, event, parent, infos) values (temp, 1, SYSDATE,sys_context('USERENV', 'HOST') , 1, 101, temp, 0);
08. getuserid() from dual;


In this last case, the triggers setted on the HISTOMVTS table will use the getuserid declared on Sophis schema, and will insert the ident used for insertion into the field uident of SECURITY_LOG.

Tags: Sophis , Risque , deals , security , audit

Search blog