-- Start of DDL Script for Trigger SYS.AFTER_LOGON_BD
-- Generated 29/10/2007 10:45:47 from SYS@VS01

CREATE OR REPLACE TRIGGER sys.after_logon_bd
 AFTER
  LOGON
 ON DATABASE
DECLARE
  v_program  SYS.V$SESSION.PROGRAM%TYPE;
  v_osuser   SYS.V$SESSION.OSUSER%TYPE;
  v_sid      sys.V$SESSION.sid%type;
  v_serial   sys.V$SESSION.serial#%type;
  v_machine  sys.V$SESSION.machine%type;
  v_username sys.V$SESSION.username%type;
  instance   varchar2(20);
  ip         varchar2(30);
BEGIN


 begin
    select instance_name into instance from v$instance;
  --
    select sys_context('USERENV','IP_ADDRESS') into ip from dual;
  --
    select program, osuser, sid, serial#, machine, username into   v_program, v_osuser, v_sid, v_serial, v_machine, v_username
    from   v$session where audsid = userenv('sessionid') and OSUSER= SYS_CONTEXT('USERENV','OS_USER');
  exception
  when others then 
    null;  
  end;
  --Verifica se a máquina é da rede da Politec ANVS\BSBxxxx
  if upper(v_machine) like '%BSB%' then
    -- Elimina as conexões do css que estão usando o sqlnav ou o sqlplus
    if (UPPER(V_PROGRAM) in ('SQLNAV4.EXE','SQLPLUSW.EXE','SQLPLUS.EXE','TOAD.EXE','MSACCESS.EXE')) and v_username not in ('CSS_DATAVISA' ) then
       begin
          execute immediate 'ALTER SYSTEM KILL SESSION '||''''||v_sid||','||v_serial||''';';
       EXCEPTION
       when others then
          admin.send_mail('ORACLE','dba@anvisa.gov.br',instance||' Login: '||V_OSUSER||'  - Program: '||V_PROGRAM||' User DB: '||v_username||' Máquina: '||v_machine||'  Data: '||to_char(sysdate,'dd/mm/yyyy hh24:mi:ss')||' Login-Politec-1.1',null);
          raise_application_error('-20000','Ferramenta permitida apenas para o usuário CSS_DATAVISA!');
       end;
    -- Verifica se as conexões dos CSS são feitas pelos usários permitidos
    elsif (lower(V_OSUSER) not in (
    /* CSS - Sistemas diversos */  'alexandre.maximo','luciana.nunes','bruna.ferreira','wilton.Souza','vando.ferreira','marcelo.pinto','herliton.silva','allan.moreira',
                                   'rodolfo.mota','cleyton.cruz','alexander.marinho','valter.Gomes','gleidson.lima','wesley.assis','marcos.teixeira',
    /* CSS - Sistema Datavisa  */  'alysson.muniz','cleidson.carritilha','ademir.gouveia','livia.mota','christiany.melo','ricardo.almeida','joana.hattingh',
                                   'marcelo.rodrigues','ademir.junior','bruno.saturnino','flavio.penna','marlene.marques','gladson.lima','carlenisio.oliveira')) then
       begin
          execute immediate 'ALTER SYSTEM KILL SESSION '||''''||v_sid||','||v_serial||''';';
       EXCEPTION
       when others then
          admin.send_mail('ORACLE','dba@anvisa.gov.br',instance||' Login: '||V_OSUSER||'  - Program: '||V_PROGRAM||' User DB: '||v_username||' Máquina: '||v_machine||'  Data: '||to_char(sysdate,'dd/mm/yyyy hh24:mi:ss')||' Login-Politec-1.2',null);
          raise_application_error('-20000','Acesso negado!');
       end;
    end if;
  --Máquinas da rede da Anvisa ANVS\ANxxxx
  else
    IF UPPER(V_OSUSER) NOT IN
      /* Usuários DBAs */ ('SYSTEM','SYS','ANVS\ADMINISTRATOR','MARCO.LIMA','ROGERIO.COSTA','ROGERIO.ADMIN','GABRIELA.MARQUES','NATANAEL.LEITE','ORACLE','ANDERSON.RIBEIRO',
      /* Func. Politec */  'ANDERSON.ALDI','BETAN.GUIMARAES','GUSTAVO.SILVA','LEONARDO.SANTOS','CONCEICAO.MARTINS','LUIZ.REIS','ANDRE.BOARO','HAI.CUI',
      /* Func. Anvisa  */  'REINALDO.NELLI','NELCI.SANTOS','SHEILA.ABDALA','PAULO.CESAR','CAMILO.MUSSI','JAQUELINE.GISMONTI') then
      IF UPPER(V_PROGRAM) NOT IN ('DLLHOST.EXE','JDBC THIN CLIENT','ORACLE@ANVSSDF40.ANVISA.GOV.BR (TNS V1-V3)','EXP.EXE','PHP-CGI.EXE','DELPHI32.EXE',
                                  'DIVA_OLD.EXE','CODIVA_VS01.EXE','DIVA.EXE','VIGI.EXE','PRJ_INTERFACE_DBMON.EXE','ASPNET_WP.EXE','SINEPSINTRA.EXE','SINEPS.EXE','SAMMED.EXE',
                                  'DIVA_VS01.EXE','BAIXABBARRECADACAO.EXE','DATAVISASEG.EXE','MONITORAMENTO.EXE','MONITORAMENTOMERCADOSERVNT.EXE',
                                  'SQLPLUS@ANVSSDF45.ANVISA.GOV.BR (TNS V1-V3)','ORACLE@ANVSSDF22N.ANVISA.GOV.BR (TNS V1-V3)','RMAN.EXE','ORACLEDBCOLLECT@LW-APPLIANCE (TNS V1-V3)',
                                  'ORACLEVS05@ANVSSDF22.ANVISA.GOV.BR (TNS V1-V3)','ORACLEVS09@ANVSSDF40.ANVISA.GOV.BR (TNS V1-V3)') THEN
         IF UPPER(V_PROGRAM) NOT LIKE ('SQLPLUS@ANVSSDF45N%') THEN
           admin.send_mail('ORACLE','dba@anvisa.gov.br',instance||' Login: '||V_OSUSER||'  - Program: '||V_PROGRAM||' User DB: '||v_username||' Máquina: '||v_machine||'  Data: '||to_char(sysdate,'dd/mm/yyyy hh24:mi:ss'),null);
         END IF;
      END IF;
    END IF;
  end if;
END;
/


-- End of DDL Script for Trigger SYS.AFTER_LOGON_BD