CD Actual Thematic 26: Security/pc_actual_seguridad.iso/Scanner/SecureScanNX/data1.cab/upgrade_DB/upgrade/upgradeCleanBugNewBase-TransfertData.sql

Text File | 2001-11-16 | 11.7 KB | 267 lines

insert into dba.WKS select * from nst_wks ;
insert into dba.TC_GLOSSARY select * from nst_glossary_entry;
insert into dba.GLOSSARY select glo_id, glo_name, glo_desc, glo_sta, flag from nst_glossary;

insert into dba.PARAMETER
select a.acc_id, b.par_id,b.par_clstype,NULL,b.par_name,b.par_desc,b.par_info,b.par_type,NULL,
c.pav_dfltvalue,c.pav_minval,c.pav_maxval,c.pav_value
from nst_tc a, nst_parameter b, nst_paramvalue c
where a.tca_id=b.par_objid and c.pav_objid=b.par_objid and b.par_id*=c.par_id and c.con_id=5 and b.par_clstype=8;

insert into dba.PARAMETER
select b.par_objid, b.par_id,b.par_clstype,NULL,b.par_name,b.par_desc,b.par_info,b.par_type,NULL,
c.pav_dfltvalue,c.pav_minval,c.pav_maxval,c.pav_value
from nst_parameter b, nst_paramvalue c
where c.pav_objid=b.par_objid and b.par_id*=c.par_id and c.con_id=5 and b.par_clstype not in (8);

update dba.PARAMETER set ClsType='UNDEFINED' where ClsType_id=0;
update dba.PARAMETER set ClsType='SESSION' where ClsType_id=1;
update dba.PARAMETER set ClsType='PERIMETER' where ClsType_id=2;
update dba.PARAMETER set ClsType='POLICY' where ClsType_id=3;
update dba.PARAMETER set ClsType='JOB' where ClsType_id=4;
update dba.PARAMETER set ClsType='HOST' where ClsType_id=5;
update dba.PARAMETER set ClsType='PROBE' where ClsType_id=6;
update dba.PARAMETER set ClsType='WKSERVICE' where ClsType_id=7;
update dba.PARAMETER set ClsType='TESTCASE' where ClsType_id=8;
update dba.PARAMETER set ClsType='GENERAL' where ClsType_id=9;
update dba.PARAMETER set ClsType='HELPERLIB' where ClsType_id=10;
update dba.PARAMETER set ClsType='OPAQUE' where ClsType_id=11;
update dba.PARAMETER set ClsType='PROPERTY' where ClsType_id=12;
update dba.PARAMETER set ClsType='PARAMETER' where ClsType_id=13;
update dba.PARAMETER set ClsType='SERVICE' where ClsType_id=14;
update dba.PARAMETER set ClsType='TESTRESULT' where ClsType_id=15;
update dba.PARAMETER set ClsType='HOSTFOUND' where ClsType_id=16;
update dba.PARAMETER set ClsType='EXTINFO' where ClsType_id=17;
update dba.PARAMETER set ClsType='LASTVALUE' where ClsType_id=18;


update dba.PARAMETER set Type='STRING' where Type_id=1;
update dba.PARAMETER set Type='INTEGER' where Type_id=2;

insert into dba.TCRESULT
select  a.tcr_id,b.acc_id,a.srv_id,a.crt_id,c.crt_name,
a.tcr_dt_begin,a.tcr_dt_end,a.hos_hostid,a.job_id
from nst_tcresult a, nst_tc b, nst_result_code c
where a.tca_id=b.tca_id and a.crt_id=c.crt_id;

insert into dba.SERVICE
select * from nst_service;

insert into dba.JOB_RANGE
select * from nst_jobrange;

insert into dba.RANGE
select * from nst_range;

insert into dba.INTERNAL_INDEX
select * from nst_index;

insert into dba.HOST
select * from  nst_host;

insert into dba.PERIMETER
select * from nst_perimeter;

insert into dba.POLICY
select * from nst_policy;

insert into dba.JOB
select * from nst_job;

insert into dba.JOB_HOST
select a.job_id, a.hos_hostid,a.joh_host_type,a.joh_host_probe,b.hst_id,b.hst_name
from nst_jobhost a, nst_hoststatus b
where a.joh_host_status=b.hst_id;

insert into dba.SESSION
select ses_id,pol_polid,per_perid,ses_desc,ses_type,ses_crdate,ses_lastdate,
ses_lastjobid, ses_scactive,ses_scjobtime,ses_scdaysom,ses_scdaysow,ses_scflags,
ses_scargs,ses_predefined
from nst_session;

insert into dba.POLICYDESC
select pol_polid, pol_type, pol_value1, pol_include
from nst_policydesc;

insert into dba.PROP_VALUE
select a.pro_propid,a.prv_classe,NULL,a.prv_objid,a.prv_value,NULL
from nst_propvalue a
where ses_id in
(select max(ses_id) from nst_propvalue);

insert into dba.PROP_VALUE select 8,3,NULL,1,'1','1'
where not exists (select * from dba.PROP_VALUE where Pro_id=8 and ClsType_id=3 and Obj_id=1);
insert into dba.PROP_VALUE select 8,3,NULL,2,'1','1'
where not exists (select * from dba.PROP_VALUE where Pro_id=8 and ClsType_id=3 and Obj_id=2);
insert into dba.PROP_VALUE select 8,3,NULL,3,'1','1'
where not exists (select * from dba.PROP_VALUE where Pro_id=8 and ClsType_id=3 and Obj_id=3);
insert into dba.PROP_VALUE select 9,3,NULL,1,'1023','1023'
where not exists (select * from dba.PROP_VALUE where Pro_id=9 and ClsType_id=3 and Obj_id=1);
insert into dba.PROP_VALUE select 9,3,NULL,2,'1023','1023'
where not exists (select * from dba.PROP_VALUE where Pro_id=9 and ClsType_id=3 and Obj_id=2);
insert into dba.PROP_VALUE select 9,3,NULL,3,'65535','65535'
where not exists (select * from dba.PROP_VALUE where Pro_id=9 and ClsType_id=3 and Obj_id=3);
insert into dba.PROP_VALUE select 16,3,NULL,1,'1','1'
where not exists (select * from dba.PROP_VALUE where Pro_id=16 and ClsType_id=3 and Obj_id=1);
insert into dba.PROP_VALUE select 16,3,NULL,2,'1','1'
where not exists (select * from dba.PROP_VALUE where Pro_id=16 and ClsType_id=3 and Obj_id=2);
insert into dba.PROP_VALUE select 16,3,NULL,3,'1','1'
where not exists (select * from dba.PROP_VALUE where Pro_id=16 and ClsType_id=3 and Obj_id=3);
insert into dba.PROP_VALUE select 17,3,NULL,1,'1023','1023'
where not exists (select * from dba.PROP_VALUE where Pro_id=17 and ClsType_id=3 and Obj_id=1);
insert into dba.PROP_VALUE select 17,3,NULL,2,'1023','1023'
where not exists (select * from dba.PROP_VALUE where Pro_id=17 and ClsType_id=3 and Obj_id=2);
insert into dba.PROP_VALUE select 17,3,NULL,3,'1023','1023'
where not exists (select * from dba.PROP_VALUE where Pro_id=17 and ClsType_id=3 and Obj_id=3);

update dba.PROP_VALUE set DfltValue='1' where Pro_id=8 and ClsType_id=3 and Obj_id=1;
update dba.PROP_VALUE set DfltValue='1' where Pro_id=8 and ClsType_id=3 and Obj_id=2;
update dba.PROP_VALUE set DfltValue='1' where Pro_id=8 and ClsType_id=3 and Obj_id=3;
update dba.PROP_VALUE set DfltValue='1023' where Pro_id=9 and ClsType_id=3 and Obj_id=1;
update dba.PROP_VALUE set DfltValue='1023' where Pro_id=9 and ClsType_id=3 and Obj_id=2;
update dba.PROP_VALUE set DfltValue='65535' where Pro_id=9 and ClsType_id=3 and Obj_id=3;
update dba.PROP_VALUE set DfltValue='1' where Pro_id=16 and ClsType_id=3 and Obj_id=1;
update dba.PROP_VALUE set DfltValue='1' where Pro_id=16 and ClsType_id=3 and Obj_id=2;
update dba.PROP_VALUE set DfltValue='1' where Pro_id=16 and ClsType_id=3 and Obj_id=3;
update dba.PROP_VALUE set DfltValue='1023' where Pro_id=17 and ClsType_id=3 and Obj_id=1;
update dba.PROP_VALUE set DfltValue='1023' where Pro_id=17 and ClsType_id=3 and Obj_id=2;
update dba.PROP_VALUE set DfltValue='1023' where Pro_id=17 and ClsType_id=3 and Obj_id=3;


update dba.PROP_VALUE set ClsType='UNDEFINED' where ClsType_id=0;
update dba.PROP_VALUE set ClsType='SESSION' where ClsType_id=1;
update dba.PROP_VALUE set ClsType='PERIMETER' where ClsType_id=2;
update dba.PROP_VALUE set ClsType='POLICY' where ClsType_id=3;
update dba.PROP_VALUE set ClsType='JOB' where ClsType_id=4;
update dba.PROP_VALUE set ClsType='HOST' where ClsType_id=5;
update dba.PROP_VALUE set ClsType='PROBE' where ClsType_id=6;
update dba.PROP_VALUE set ClsType='WKSERVICE' where ClsType_id=7;
update dba.PROP_VALUE set ClsType='TESTCASE' where ClsType_id=8;
update dba.PROP_VALUE set ClsType='GENERAL' where ClsType_id=9;
update dba.PROP_VALUE set ClsType='HELPERLIB' where ClsType_id=10;
update dba.PROP_VALUE set ClsType='OPAQUE' where ClsType_id=11;
update dba.PROP_VALUE set ClsType='PROPERTY' where ClsType_id=12;
update dba.PROP_VALUE set ClsType='PROP_VALUE' where ClsType_id=13;
update dba.PROP_VALUE set ClsType='SERVICE' where ClsType_id=14;
update dba.PROP_VALUE set ClsType='TESTRESULT' where ClsType_id=15;
update dba.PROP_VALUE set ClsType='HOSTFOUND' where ClsType_id=16;
update dba.PROP_VALUE set ClsType='EXTINFO' where ClsType_id=17;
update dba.PROP_VALUE set ClsType='LASTVALUE' where ClsType_id=18;

insert into dba.PROPERTY
select a.pro_propid,a.pro_clstype,NULL,a.pro_name,a.pro_type,a.pro_flags,a.pro_desc,a.pro_dfltvalue,a.pro_minval,a.pro_maxval
from nst_property a;

update dba.PROPERTY set ClsType='UNDEFINED' where ClsType_id=0;
update dba.PROPERTY set ClsType='SESSION' where ClsType_id=1;
update dba.PROPERTY set ClsType='PERIMETER' where ClsType_id=2;
update dba.PROPERTY set ClsType='POLICY' where ClsType_id=3;
update dba.PROPERTY set ClsType='JOB' where ClsType_id=4;
update dba.PROPERTY set ClsType='HOST' where ClsType_id=5;
update dba.PROPERTY set ClsType='PROBE' where ClsType_id=6;
update dba.PROPERTY set ClsType='WKSERVICE' where ClsType_id=7;
update dba.PROPERTY set ClsType='TESTCASE' where ClsType_id=8;
update dba.PROPERTY set ClsType='GENERAL' where ClsType_id=9;
update dba.PROPERTY set ClsType='HELPERLIB' where ClsType_id=10;
update dba.PROPERTY set ClsType='OPAQUE' where ClsType_id=11;
update dba.PROPERTY set ClsType='PROPERTY' where ClsType_id=12;
update dba.PROPERTY set ClsType='PROP_VALUE' where ClsType_id=13;
update dba.PROPERTY set ClsType='SERVICE' where ClsType_id=14;
update dba.PROPERTY set ClsType='TESTRESULT' where ClsType_id=15;
update dba.PROPERTY set ClsType='HOSTFOUND' where ClsType_id=16;
update dba.PROPERTY set ClsType='EXTINFO' where ClsType_id=17;
update dba.PROPERTY set ClsType='LASTVALUE' where ClsType_id=18;

create procedure "dba".extinfotrans
as
begin
  declare @tcr_id integer
  declare @tcr_idold integer
  declare @tcr_name varchar(25555)
  declare @ext_id integer
  declare @tmp integer
  declare @stmp varchar(25555)
  select @tmp=0
  select @stmp='tre'
  /* select tcr_id,ext_line from nst_extinfos order by tcr_id asc,ext_id asc*/
  begin
    declare cCur dynamic scroll cursor for select tcr_id,ext_line,ext_id from dba.nst_extinfos order by tcr_id asc,ext_id asc
    open cCur
    select @tcr_id=0
    select @tcr_idold=0
    select @ext_id=0
    while @tcr_id>-1
      begin
        /* select @stmp=@stmp+'0000'*/
        select @tcr_id=-1
        fetch next cCur into @tcr_id,@tcr_name,
          @ext_id
        if @tcr_id>-1
          if @tcr_id=@tcr_idold
            select @stmp=@stmp+'\x0D\x0A'+@tcr_name
          else
            begin
              if @tcr_idold>0 /* pas la premiere fois alors on fait l'insert*/
                insert into dba.TCRESULT_INFO values(@tcr_idold,1,3,null,null,@stmp)
              select @tcr_idold=@tcr_id
              select @stmp=@tcr_name
            end
         else
           begin
               if @tcr_idold>0 /* pas la premiere fois alors on fait l'insert*/
                insert into dba.TCRESULT_INFO values(@tcr_idold,1,3,null,null,@stmp)
           end
      /*  select @stmp*/
      end
   /* select 'fin:'+@stmp*/
    close cCur
  end
end;

CALL "DBA"."extinfotrans"(  );

drop procedure "dba".extinfotrans;

insert into dba.TESTCASE
select
    a.acc_id,
    a.tca_id,
    a.prt_id,
    a.prt_id,
    a.tca_name,
    a.tca_desc,
    a.tca_copyright,
    a.tca_dtPub,
    a.tca_test_method           ,
    NULL ,
    NULL        ,
    f.fol_name,
    b.fol_id               ,
    b.cpa_high_level_desc        ,
    b.cpa_nat_follow_up                             ,
    b.cpa_risk_desc,
    a.lev_id                         ,
    c.lev_desc,
    g.ref_desc,
    b.imp_id                        ,
    e.imp_desc,
    a.tca_active ,
    a.tca_flags  ,
    a.sta_network_type + 2*a.sta_firewall_type + 4*a.sta_system_type + 16*a.sta_database_type,
    a.sta_keywords                    ,
    a.sta_cve                                     ,
    a.sta_chain                ,
    600,
    a.mod_id
from dba.nst_tc a, dba.nst_contextual_part b, dba.nst_level c, dba.nst_impact e,
dba.nst_followup_urgency f,dba.nst_reference g, dba.nst_tc_ref h
where a.acc_id=b.acc_id and b.con_id=5 and a.lev_id*=c.lev_id and b.imp_id*=e.imp_id and b.fol_id*=f.fol_id and a.acc_id*=h.acc_id and h.ref_id*=g.ref_id
order by a.acc_id;


insert into dba.SOLUTION
select a.acc_id, b.sol_desc, 0, 'UNDEFINED'
from dba.nst_cp_sol a, dba.nst_solution b
where  a.sol_id=b.sol_id
order by a.acc_id asc;