-- Start of DDL Script for Trigger SYS.AFTER_LOGON_BD
-- Generated 25-nov-2010 10:41:14 from SYS@vs01
CREATE OR REPLACE TRIGGER after_logon_bd
AFTER
LOGON
ON DATABASE
DECLARE
v_session sys.V$SESSION%rowtype;
instance sys.v$instance.instance_name%type;
BEGIN
-- select sys_context('USERENV','IP_ADDRESS') into ip from dual;
--
select instance_name into instance from v$instance;
--
select * into v_session from v$session where sid = userenv('sid');
--select * into v_session from v$session where audsid = userenv('SESSIONID') and OSUSER= SYS_CONTEXT('USERENV','OS_USER');
--
--Verifica se a máquina é da rede da Politec ANVS\BSBxxxx
if (upper(v_session.machine) like '%BSBXXXX%') OR (upper(v_session.terminal) like '%BSBXXXX%')then
-- Elimina as conexões do css que estão usando o sqlnav ou o sqlplus
if (UPPER(v_session.PROGRAM) in ('SQLNAV4.EXE','SQLNAV5.EXE','SQLPLUSW.EXE','SQLPLUS.EXE','TOAD.EXE','MSACCESS.EXE','SQL DEVELOPER')) and v_session.username not in ('CSS_DATAVISA' ) then
begin
execute immediate 'ALTER SYSTEM KILL SESSION '||''''||v_session.sid||','||v_session.serial#||''';';
EXCEPTION
when others then
insert into admin.tb_after_logon values (v_session.OSUSER, v_session.PROGRAM, v_session.username, v_session.machine, v_session.terminal, v_session.logon_time,'Login-Politec-1.1');
commit;
-- admin.send_mail('ORACLE','dba@anvisa.gov.br',instance||' Login: '||v_session.osuser||' - Program: '||v_session.PROGRAM||' User DB: '||v_session.username||' Máquina: '||v_session.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_session.OSUSER) not in (
/* Fábrica Mirante - Sistemas diversos */
'nilton.andrade')) then
begin
execute immediate 'ALTER SYSTEM KILL SESSION '||''''||v_session.sid||','||v_session.serial#||''';';
EXCEPTION
when others then
insert into admin.tb_after_logon values (v_session.OSUSER, v_session.PROGRAM, v_session.username, v_session.machine, v_session.terminal, v_session.logon_time,'Login-Politec-1.2');
commit;
-- admin.send_mail('ORACLE','dba@anvisa.gov.br',instance||' Login: '||v_session.OSUSER||' - Program: '||v_session.PROGRAM||' User DB: '||v_session.username||' Máquina: '||v_session.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 -- Verifica se o usuário de banco tem acesso
IF (UPPER(v_session.username) NOT IN ('SYSTEM','SYS','ADMIN','DWADMIN','DBSNMP','SYSMAN','MARCELO_ANGELO','NADIR_OGLIARI')) then
-- Verifica se o usuário de Sistema Operacional tem acesso - Funcionário com perfil de DBA/AD/DW
If UPPER(v_session.osuser) NOT IN ('MARCO.LIMA','GABRIELA.MARQUES','ANDERSON.RIBEIRO','DEIVISON.MUNIZ','JAQUELINE.GISMONTI','GUSTAVO.SILVA','YURI.ADAME') then
-- Trava de usuário com problema
if UPPER(v_session.username) in ('CONSULTA_DBSVS_WEB') then
--raise_application_error('-20000','Acesso negado!');
null;
--Verifica se as conexões são provenientes dos servidores ANVSSDFxx
elsif (upper(v_session.machine) like '%ANVSSDF%') then
null;
elsif (upper(v_session.terminal) like '%ANVSSDF%') then
null;
--Verifica se as conexões são provenientes dos servidores WWW1.ANVISA.GOV.BR
elsif (upper(v_session.machine) like '%WWW1.ANVISA.GOV.BR%') then
null;
elsif (upper(v_session.terminal) like '%WWW1.ANVISA.GOV.BR%') then
null;
--Libera o acesso temporário de migração
elsif (upper(v_session.machine) like '%WWW1N%') then
null;
--Libera o acesso da máquina an041387 da usuaria Carolina.Pigret e da maquina AN042791 do Ricardo.Borges
-- elsif (upper(v_session.terminal) in ('AN041387','AN042791','AN057032','AN041386')) and UPPER(v_session.osuser) IN ('CAROLINA.PINGRET','RICARDO.BORGES','MARCELO.ANGELO','FABIO.ALMEIDA') and upper(v_session.username) = 'CONSULTA_DBSVS' then
-- null;
elsif (upper(v_session.program) in ('JDBC THIN CLIENT')) and UPPER(v_session.username) IN ('ETIQUETA_WEB') then
null;
--Libera o acesso da máquina an050717 da analista aline
elsif (upper(v_session.terminal) in ('AN050717')) and UPPER(v_session.osuser) IN ('ALINE.MAGALHAES') then
null;
--###############################################################
--Libera o acesso da máquina gbt-02 Alessandro Mirante temporario
elsif v_session.machine in ('gbt-02.local') and UPPER(v_session.username) IN ('CONS_EXTERNA') then
null;
--################################################################
-- Acesso para o setor de Arrecadação do sistema SAT
elsif UPPER(v_session.osuser) in ('FREDERICO.FERNANDES','JULIANA.QUARTIN','JORGE.NASCIMENTO') and (upper(v_session.username) = 'CADEMPRESA_WEB') then
null;
-- Libera o acesso para o usuário de banco CONS_PPS_DATAVISA
elsif UPPER(v_session.osuser) in ('STELA.MELCHIOR','MARIA.VICENTE','CARLA.CRUZ','GUILHERME.BUSS','CARLOS.FORNAZIER','MARIO.CHAVES')
and (upper(v_session.username) = 'CONS_PPS_DATAVISA') then
null;
--Acesso para Sammed
elsif (upper(v_session.username) = 'SAMMED_WEB') then
null;
--desenvolvedor
-- Acesso para a GGREM
elsif UPPER(v_session.osuser) in ('ALESANDRE.SANTOS') and (upper(v_session.username) = 'MON_WEB') then
null;
--Libera o acesso da máquina AN0431 para o acesso do CSS com ousuário CSS_DATAVISA
elsif upper(v_session.terminal) = 'AN0431' and UPPER(v_session.username) = 'CSS_DATAVISA' then
null;
--Verifica se as conexões são proveniente de um APLICATIVO autorizado
elsif upper(v_session.program) in ('AGENDATEL.EXE','BAIXABBARRECADACAO.EXE','CODIVA_VS01.EXE','DIVA_VS01.EXE','FINANCEIROSIPAD.EXE',
'INETINFO.EXE','ESTATPAFSEG.EXE','MONITORAMENTO.EXE','PRJ_INTERFACE_DBMON.EXE',
'PDARF.EXE','UNICA.EXE','SNGPC.EXE','SIPAT.EXE') then
null;
--Elimina as conexões de usuários de SO desconhecidos
elsif v_session.osuser is null then
begin
insert into admin.tb_after_logon values (v_session.OSUSER, v_session.PROGRAM, v_session.username, v_session.machine, v_session.terminal, v_session.logon_time,'Login-Anvisa-1.0');
commit;
--execute immediate 'ALTER SYSTEM KILL SESSION '||''''||v_session.sid||','||v_session.serial#||''';';
EXCEPTION
when others then
null;
--insert into admin.tb_after_logon values (v_session.OSUSER, v_session.PROGRAM, v_session.username, v_session.machine, v_session.terminal, v_session.logon_time,'Login-Anvisa-1.0');
--commit;
--admin.send_mail('ORACLE','dba@anvisa.gov.br',instance||' Login: '||v_session.OSUSER||' - Program: '||v_session.PROGRAM||' User DB: '||v_session.username||' Máquina: '||v_session.machine||' Data: '||to_char(sysdate,'dd/mm/yyyy hh24:mi:ss')||' Login-Anvisa-1.0',null);
--raise_application_error('-20000','Acesso negado!');
end;
--Elimina as conexões dos usuário DB*
elsif (UPPER(v_session.username) like 'DB%') or (UPPER(v_session.username) in ('AD')) then
begin
execute immediate 'ALTER SYSTEM KILL SESSION '||''''||v_session.sid||','||v_session.serial#||''';';
EXCEPTION
when others then
insert into admin.tb_after_logon values (v_session.OSUSER, v_session.PROGRAM, v_session.username, v_session.machine, v_session.terminal, v_session.logon_time,'Login-Anvisa-1.1');
commit;
--admin.send_mail('ORACLE','dba@anvisa.gov.br',instance||' Login: '||v_session.OSUSER||' - Program: '||v_session.PROGRAM||' User DB: '||v_session.username||' Máquina: '||v_session.machine||' Data: '||to_char(sysdate,'dd/mm/yyyy hh24:mi:ss')||' Login-Anvisa-1.0',null);
raise_application_error('-20000','Acesso negado!');
end;
--Elimina as conexões não autorizadas
else
If (UPPER(v_session.osuser) NOT IN (
/* ANALISTA DE SISTEMAS - ANVISA */
'CARLOS.GONCALVES','DANIELLE.MACIEL','EWERTON.MARTINS','HONORIO.MARQUES','PEDRO.ALMEIDA',
'PAULO.CESAR','NELCI.SANTOS','ORACLE','VERANGGE.LOPES','JORGE.CARVALHO',
--/*ANALISTAS DA UNIMIX*/ 'ALEX.VIEIRA',
/* ANALISTA DE SISTEMAS - MIRANTE */
--/*Suporte Oracle*/ 'ARISTOTELLES.SERRA',
'ADEMIR.JUNIOR','ALEXANDRE.MAXIMO','BRUNO.AMORIM','DIONE.OLIVEIRA','ROGERIO.MIARI','GLADSON.LIMA','MARCELO.RODRIGUES','WESLEY.MARINHO', 'ROBERTO.LUIZ','DARLAN.LISBOA','ERIC.SILVA','HELCIO.GONCALVES','RUZBEH.SHOKRANIAN','VICTOR.LUIZ','BRUNO.SANTOS','DIOGO.CORAZOLLA','PEDRO.ALMEIDA',
'RENATO.VALE','DIVINO.JUNIOR','ULISSES.SOARES','LEO.MARRA','LEONARDO.OSOUZA','JOAO.CHAGAS','BRUNO.BEHMOIRAS','WALLACE.SANTOS','DAYSE.MAGALHAES','LAZARO.FREITAS','CLAUDIO.DASILVA','ALESSANDRO.LEITE','WILSON.BRITO','DAVID.PEREIRA','RAIMUNDA.CIRILO','HOSANA.MACHADO','FRANCISCO.ADEMILSON'
/* DIVERSOS */)) then
begin
execute immediate 'ALTER SYSTEM KILL SESSION '||''''||v_session.sid||','||v_session.serial#||''';';
EXCEPTION
when others then
insert into admin.tb_after_logon values (v_session.OSUSER, v_session.PROGRAM, v_session.username, v_session.machine, v_session.terminal, v_session.logon_time,'Login-Anvisa-1.2');
commit;
--admin.send_mail('ORACLE','dba@anvisa.gov.br',instance||' - '||to_char(sysdate,'dd/mm/yyyy hh24:mi:ss')||' Login-Anvisa-1.1','Login: '||v_session.OSUSER||chr(13)||'Program: '||v_session.PROGRAM||chr(13)||'User DB: '||v_session.username||chr(13)||'Máquina: '||v_session.machine||chr(13)||'Máquina: '||v_session.terminal);
raise_application_error('-20000','Acesso negado! Favor entrar em contato com a GESIS para maiores informações. Ramal: 1133.');
end;
end if;
end if;
End if;
END IF;
end if;
END;
/
-- End of DDL Script for Trigger SYS.AFTER_LOGON_BD
