Uma das maiores dificuldades do DBA, é garantir a seguranças das informações existentes no database. Evitar que elas sejam acessadas por usuários que não deveriam possuir autorização para tal.
De forma resumida, o objetivo dessa trigger será, receber a solicitação de conexão ao database e, conforme a faixa de IP do usuário, aplicação, usuário de rede, e usuário do database, permitir ou não essa conexão.
Iremos criar alguns objetos para que a trigger possa funcionar corretamente, e de forma melhor separar os mesmos dentro do database precisaremos de um schema que chamaremos SECURITY_USER.
1 - Usuário SECURITY_USER - Este usuário irá manter as tabelas utilizadas pela trigger de logon.
2 - Tabela NETWORK_RELEASE - Essa tabela armazena o registro das faixas de IP que possuem acesso livre ao database.
3 - Tabela PROGRAM_RELEASE - Essa tabela armazena a lista dos programas que podem vir a tentar conectar no database.
Nesta tabela você deverá cadastrar no mínimo um registro CODE_PROGRAM = 999, PROGRAM_NAME = ALL. Utilizaremos o código 999 quando o usuário cadastrado em USER_RELEASE puder conectar com qualquer(999) programa
4 - Tabela USER_RELEASE - Essa tabela armazena a relação, OSUSER + SESSION_USER + PROGRAM dos usuários que podem acessar o database.
Você deve cadastrar no mínimo um registro com a seguinte combinação OSUSER = ORACLE, SESSION_USER = * e CODE_PROGRAM = 999. Em resumo, esse registro informa a trigger que qualquer usuário de sistema operacional ORACLE, poderá se conectar ao banco utilizando qualquer usuário(SCHEMA) e qualquer programa(999) deste que esteja na rede liberada.
5 - Tabela LOGIN_DENIED - Essa tabela registra as tentativas de login ao database e que foram bloqueadas.
6 - Tabela LOGIN_TRACE - Essa tabela registra os logins realizados durante o dia no database, e que NÃO foram bloqueados.
7 - Tabela LOGIN_TRACE_SUMMARY - Essa tabela totaliza os registros da tabela LOGIN_TRACE.
8 - Tabela USER_BLOCKER - Essa tabela armazena usuários que possuem seu acesso bloqueado, considerando OS_USER + SESSION_USER + PROGRAMA.
9 - Procedure EXEC_SUMMARY - Essa procedure totaliza a tabela LOGIN_TRACE, armazenando resultado na tabela LOGIN_TRACE_SUMMARY. Deve ser executada diariamente a 00:00:00. Isso faz com que na tabela LOGIN_TRACE existam apenas os registros pertencentes a um único dia.
CREATE OR REPLACE PROCEDURE SECURITY_USER.exec_summary
- IS
- CURSOR c_summary_login (last_date DATE)
- IS
- SELECT session_user, machine_user, ip_address, program_user, os_user,
- COUNT (1) count_logins
- FROM security_user.login_trace
- WHERE logon_time <= last_date
- GROUP BY session_user, machine_user, ip_address, program_user, os_user;
- r_summary_login c_summary_login%ROWTYPE;
- hora_ini DATE;
- BEGIN
- SELECT SYSDATE
- INTO hora_ini
- FROM DUAL;
- OPEN c_summary_login (hora_ini);
- LOOP
- FETCH c_summary_login
- INTO r_summary_login;
- EXIT WHEN c_summary_login%NOTFOUND;
- BEGIN
- INSERT INTO security_user.login_trace_summary
- (session_user,
- machine_user, date_logon,
- ip_address,
- program_user, os_user,
- count_logins)
- VALUES (r_summary_login.session_user,
- r_summary_login.machine_user, TRUNC (hora_ini - 1),
- r_summary_login.ip_address,
- r_summary_login.program_user, r_summary_login.os_user,
- r_summary_login.count_logins);
- END;
- END LOOP;
- DELETE security_user.login_trace
- WHERE logon_time <= hora_ini;
- COMMIT;
- EXECUTE IMMEDIATE 'ALTER TABLE SECURITY_USER.LOGIN_TRACE SHRINK SPACE CASCADE';
- END;
Cadastrando as redes liberadas
É necessário cadastrar as redes que possuem acesso permitido(ALLOWED) ao login do database.
Se você desejar liberar um IP específico, deve cadastrá-lo seguindo o padrão 000.000.000.000, preenchendo o IP_NUMBER_BEGIN e IP_NUMBER_END com ZEROS a esquerda de cada parte do endereço IP.
Eg: Para liberar o IP 10.0.61.55 será necessário incluir um registro com o padrão:
- IP_NUMBER_BEGIN = 010.000.061.055
- IP_NUMBER_END = 010.000.061.055
No exemplo abaixo, estão cadastradas duas faixas de rede 10.0.11.* e 10.0.30.*
A trigger possui algumas funcionalidades interessantes além do bloqueio ou não do login. Podemos citar a possibilidade de utilizar um database standby D-1 por exemplo para realizar consultas. É possível também ativar o trace do banco de dados automaticamente para uma determinada OS_USER + SESSION_USER + PROGRAM.
Se desejar também pode configurar parâmetros de sessão exclusivos para um determinada combinação de SESSION_USER.
O código da trigger
CREATE OR REPLACE TRIGGER SYS.t_database_login AFTER LOGON ON DATABASE DECLARE v_sessionuser VARCHAR2 (30); t_sessionuser VARCHAR2 (30); v_machine VARCHAR2 (64); v_program VARCHAR2 (48); v_ip VARCHAR2 (30); v_ip_nomask VARCHAR2 (30); v_osuser VARCHAR2 (30); v_sid NUMBER; v_networkrelease NUMBER; v_userrelease NUMBER; v_userblocker NUMBER; v_codeprogram NUMBER; v_openmode VARCHAR2 (10); v_standbyonly VARCHAR2 (1); v_messagedenied VARCHAR2 (300); v_action VARCHAR2 (32); BEGIN v_standbyonly := 'N'; v_userrelease := NULL; v_userblocker := NULL;
-- Identificar o modo de abertura do banco (READ ONLY / READ WRITE) SELECT open_mode INTO v_openmode FROM v$database;
-- Identificar o usuário de database da sessão do usuário SELECT SYS_CONTEXT ('USERENV', 'SESSION_USER') INTO v_sessionuser FROM DUAL;
-- Capturar o valor da coluna ACTION da sessão do usuário no database SELECT SYS_CONTEXT ('USERENV', 'ACTION') INTO v_action FROM DUAL;
-- Capturar o valor do SID da sessão do usuário no database SELECT SYS_CONTEXT ('USERENV', 'SID') INTO v_sid FROM DUAL;
-- Capturar o nome do programa e a máquina onde o usuário está conectado ao database SELECT program, machine INTO v_program, v_machine FROM v$session WHERE SID = v_sid;
-- Capturar o endereço IP do terminal do usuário SELECT SYS_CONTEXT ('USERENV', 'IP_ADDRESS'), LPAD (SUBSTR (SYS_CONTEXT ('USERENV', 'IP_ADDRESS'), 1, INSTR (SYS_CONTEXT ('USERENV', 'IP_ADDRESS'), '.', 1, 1 ) - 1 ), 3, '0' ) || LPAD (SUBSTR (SYS_CONTEXT ('USERENV', 'IP_ADDRESS'), INSTR (SYS_CONTEXT ('USERENV', 'IP_ADDRESS'), '.', 1, 1 ) + 1, INSTR (SYS_CONTEXT ('USERENV', 'IP_ADDRESS'), '.', 1, 2 ) - INSTR (SYS_CONTEXT ('USERENV', 'IP_ADDRESS'), '.', 1, 1 ) - 1 ), 3, '0' ) || LPAD (SUBSTR (SYS_CONTEXT ('USERENV', 'IP_ADDRESS'), INSTR (SYS_CONTEXT ('USERENV', 'IP_ADDRESS'), '.', 1, 2 ) + 1, INSTR (SYS_CONTEXT ('USERENV', 'IP_ADDRESS'), '.', 1, 3 ) - INSTR (SYS_CONTEXT ('USERENV', 'IP_ADDRESS'), '.', 1, 2 ) - 1 ), 3, '0' ) || LPAD (SUBSTR (SYS_CONTEXT ('USERENV', 'IP_ADDRESS'), INSTR (SYS_CONTEXT ('USERENV', 'IP_ADDRESS'), '.', 1, 3 ) + 1, LENGTH (SYS_CONTEXT ('USERENV', 'IP_ADDRESS')) - INSTR (SYS_CONTEXT ('USERENV', 'IP_ADDRESS'), '.', 1, 3 ) ), 3, '0' ) INTO v_ip, v_ip_nomask FROM DUAL;
-- Identificar o usuário de sistema operacional/rede utilizado para conectar ao database SELECT NVL (SYS_CONTEXT ('USERENV', 'OS_USER'), 'oracle') INTO v_osuser FROM DUAL;
-- Se o database estiver em modo READ WRITE, inserir na tabela de logins a tentativa de login no database IF v_openmode = 'READ WRITE' THEN INSERT INTO security_user.login_trace (session_user, machine_user, ip_address, logon_time, program_user, os_user ) VALUES (v_sessionuser, v_machine, v_ip, SYSDATE, v_program, v_osuser ); COMMIT; END IF;
-- Procurar na tabela de rede liberadas e determinar se a rede do usuário que iniciou a sessão tem conexão liberada do database. BEGIN SELECT NVL (COUNT (1), 0) INTO v_networkrelease FROM security_user.network_release WHERE TO_NUMBER (v_ip_nomask) BETWEEN TO_NUMBER (REPLACE (ip_number_begin, '.', '')) AND TO_NUMBER (REPLACE (ip_number_end, '.', '')); EXCEPTION WHEN NO_DATA_FOUND THEN -- Se o IP do usuário não for encontrado na faixa de redes liberadas, v_networkrelease será igual a ZERO. v_networkrelease := 0; END;
-- Determinar se o usuário de rede poderá conectar com qualquer usuário de banco(*), utilizando qualquer aplicação(999) BEGIN SELECT NVL (COUNT (1), 0), 999, ur.standby_only, '*' INTO v_userrelease, v_codeprogram, v_standbyonly, t_sessionuser FROM security_user.user_release ur WHERE UPPER (v_osuser) = UPPER (ur.os_user) AND 999 = ur.code_program AND UPPER ('*') = UPPER (ur.session_user) GROUP BY 999, ur.standby_only; EXCEPTION WHEN NO_DATA_FOUND THEN IF v_userrelease IS NULL THEN v_userrelease := 0; END IF;
IF v_standbyonly IS NULL THEN v_standbyonly := 'Y'; END IF; END;
-- Se a consulta anterior não localizar permissão de acesso para a combinação usuário de rede + qualquer usuário de banco(*) + qualquer aplicação(999), IF v_userrelease = 0 THEN -- Identificar se a combinação USUÁRIO DE REDE + USUÁRIO DE BANCO + TODAS AS APLICAÇÕES(999) possuem acesso permitido de conexão do database, ou seja, -- se o usuário de rede conectado pode acessar o database utilizando o usuário de banco especificado, com QUALQUER aplicação. BEGIN SELECT NVL (COUNT (1), 0), 999, ur.standby_only, UPPER (v_sessionuser) INTO v_userrelease, v_codeprogram, v_standbyonly, t_sessionuser FROM security_user.user_release ur WHERE UPPER (v_osuser) = UPPER (ur.os_user) AND 999 = ur.code_program AND UPPER (v_sessionuser) = UPPER (ur.session_user) GROUP BY 999, ur.standby_only;
EXCEPTION WHEN NO_DATA_FOUND THEN IF v_userrelease IS NULL THEN v_userrelease := 0; END IF;
IF v_standbyonly IS NULL THEN v_standbyonly := 'Y'; END IF; END; END IF;
-- Verificar se existe uma combinação específica para USUARIO DE REDE + USUÁRIO DE BANCO + APLICAÇÃO -- Essa consulta determina o acesso para aplicações específicas. BEGIN SELECT NVL (COUNT (1), 0), pr.code_program, ur.standby_only, UPPER (v_sessionuser) INTO v_userrelease, v_codeprogram, v_standbyonly, t_sessionuser FROM security_user.user_release ur, security_user.program_release pr WHERE ur.code_program = pr.code_program AND UPPER (v_osuser) = UPPER (ur.os_user) AND ur.code_program = pr.code_program AND UPPER (v_program) = UPPER (pr.program_name) AND UPPER (v_sessionuser) = UPPER (ur.session_user) GROUP BY pr.code_program, ur.standby_only; EXCEPTION WHEN NO_DATA_FOUND THEN v_messagedenied := 0; END;
-- Procura por algum bloqueio especifico para a combinação USUÁRIO DE REDE + TODOS OS USUÁRIOS DE BANCO + TODAS AS APLICAÇÕES BEGIN SELECT NVL (COUNT (1), 0) INTO v_userblocker FROM security_user.user_blocker ub WHERE UPPER (v_osuser) = UPPER (ub.os_user) AND ub.code_program = 999 AND UPPER ('*') = UPPER (ub.session_user); EXCEPTION WHEN NO_DATA_FOUND THEN v_userblocker := 0;
IF v_standbyonly IS NULL THEN v_standbyonly := 'Y'; END IF; END; -- Caso não seja encontrado bloqueio específico para a combinação USUÁRIO DE REDE + TODOS OS USUÁRIOS DE BANCO + TODAS AS APLICAÇÕES -- Procurar por bloqueio para combinação USUARIO DE REDE + USUÁRIO DE BANCO + TODAS AS APLICAÇÕES IF v_userblocker = 0 THEN BEGIN SELECT NVL (COUNT (1), 0) INTO v_userblocker FROM security_user.user_blocker ub WHERE UPPER (v_osuser) = UPPER (ub.os_user) AND ub.code_program = 999 AND UPPER (v_sessionuser) = UPPER (ub.session_user); EXCEPTION WHEN NO_DATA_FOUND THEN v_userblocker := 0;
IF v_standbyonly IS NULL THEN v_standbyonly := 'Y'; END IF; END; END IF;
-- Caso não seja encontrado bloqueio para USUARIO DE REDE + USUÁRIO DE BANCO + TODAS AS APLICAÇÕES -- Procurar bloqueio para USUARIO DE REDE + USUÁRIO DE BANCO + APLICAÇÃO ESPECÍFICA IF v_userblocker = 0 THEN BEGIN SELECT NVL (COUNT (1), 0) INTO v_userblocker FROM security_user.user_blocker ub, security_user.program_release pr WHERE ub.code_program = pr.code_program AND UPPER (v_osuser) = UPPER (ub.os_user) AND ub.code_program = pr.code_program AND UPPER (v_program) = UPPER (pr.program_name) AND UPPER (v_sessionuser) = UPPER (ub.session_user) GROUP BY pr.code_program; EXCEPTION WHEN NO_DATA_FOUND THEN v_userblocker := 0;
IF v_standbyonly IS NULL THEN v_standbyonly := 'Y'; END IF; END; END IF;
-- Se o database está em modo READ WRITE e o usuário de rede É DIFERENTE de ORACLE -- Atualizar a informação referente ao último login do usuário no ambiente. -- Informação útil principalmente para determinar a última vez que usuários com liberação específica em USER_RELEASE fizeram acesso. IF (v_openmode = 'READ WRITE') AND (UPPER (v_osuser) NOT IN ('ORACLE')) THEN UPDATE security_user.user_release ur SET last_login = SYSDATE WHERE UPPER (v_osuser) = UPPER (ur.os_user) AND ur.code_program = v_codeprogram AND UPPER (t_sessionuser) = UPPER (ur.session_user);
COMMIT; END IF;
-- Essa condição tem objetivo NÃO PERMITIR aos usuários com acesso apenas no standby(READ ONLY), utilizarem esse acesso no servidor primário(READ WRITE) IF (UPPER (v_standbyonly) = 'Y') AND (v_openmode = 'READ WRITE') AND (v_userrelease <> 0) THEN v_messagedenied := 'Logon nao autorizado. Usuario nao possui acesso em modo READ WRITE(' || v_osuser || ',' || v_sessionuser || ',' || v_program || ',' || v_machine || '). Contate o suporte.';
-- Essa validação evita situações contrárias, quando um usuário com permissão apenas no PRIMÁRIO tentar acessar o STANDBY -- O database faz um registro da tentativa de login, se o database estiver READ ONLY, ocorrerá erro de gravação na tabela LOGIN_DENIED IF (v_openmode = 'READ WRITE') THEN INSERT INTO security_user.LOGIN_DENIED (os_user, session_user, program_user, ip_address, denied_messeage, denied_time ) VALUES (v_osuser, v_sessionuser, v_program, v_ip, v_messagedenied, SYSDATE );
COMMIT; END IF;
-- A exception será gerada independente do banco ser READ ONLY ou READ WRITE raise_application_error (-20001, v_messagedenied, TRUE); END IF;
-- Realiza testes para liberar o acesso, verificando se: -- Usuário está em uma rede sem acesso liberado??? Usuário não tem acesso liberado??? -- OU Usuário está bloqueado??? IF ( ((v_networkrelease < 1) ) AND ((v_userrelease < 1) ) ) OR (v_userblocker > 0 ) THEN -- Se a negativa do acesso for bloqueio do usuário (REGISTRO NA TABELA USER_DENIED) IF v_userblocker > 0 THEN v_messagedenied := 'Logon nao autorizado. Usuario com acesso bloqueado ao banco de dados(' || v_osuser || ',' || v_sessionuser || ',' || v_program || ',' || v_machine || '). Contate o suporte.';
-- Essa validação evita situações contrárias, quando um usuário com permissão apenas no PRIMÁRIO tentar acessar o STANDBY -- O database faz um registro da tentativa de login, se o database estiver READ ONLY, ocorrerá erro de gravação na tabela LOGIN_DENIED IF (v_openmode = 'READ WRITE') THEN INSERT INTO security_user.LOGIN_DENIED (os_user, session_user, program_user, ip_address, denied_messeage, denied_time ) VALUES (v_osuser, v_sessionuser, v_program, v_ip, v_messagedenied, SYSDATE );
COMMIT; END IF;
-- A exception será gerada independente do banco ser READ ONLY ou READ WRITE raise_application_error (-20001, v_messagedenied, TRUE); END IF;
-- Se a REDE DO USUÁRIO e o PROPRIO USUÁRIO tem acesso negado IF (v_networkrelease < 1) AND (v_userrelease < 1) THEN v_messagedenied := 'Logon nao autorizado. Usuario nao autorizado(' || v_osuser || ',' || v_sessionuser || ',' || v_program || ',' || v_machine || '). Contate o suporte.';
-- Essa validação evita situações contrárias, quando um usuário com permissão apenas no PRIMÁRIO tentar acessar o STANDBY -- O database faz um registro da tentativa de login, se o database estiver READ ONLY, ocorrerá erro de gravação na tabela LOGIN_DENIED IF (v_openmode = 'READ WRITE') THEN INSERT INTO security_user.LOGIN_DENIED (os_user, session_user, program_user, ip_address, denied_messeage, denied_time ) VALUES (v_osuser, v_sessionuser, v_program, v_ip, v_messagedenied, SYSDATE );
COMMIT; END IF;
-- A exception será gerada independente do banco ser READ ONLY ou READ WRITE raise_application_error (-20001, v_messagedenied, TRUE); END IF; END IF;
-- Ativar trace de logon para determinado usuario de banco -- IF (UPPER(v_machine) in ('4dados.com.br')) AND -- (INSTR(v_action,'user127', 1)) THEN -- EXECUTE IMMEDIATE 'alter session set tracefile_identifier='''||v_sessionuser||''''; -- EXECUTE IMMEDIATE 'alter session set sql_trace=true'; -- execute immediate 'ALTER SESSION SET TRACEFILE_IDENTIFIER=''TRC'''; -- execute immediate 'ALTER SESSION SET TIMED_STATISTICS = TRUE'; -- execute immediate 'ALTER SESSION SET EVENTS ''10046 trace name context forever, level 12'''; -- END IF;
-- IF (UPPER(v_sessionuser) = 'SGD') and ((UPPER(v_osuser) = 'USER169') or (UPPER(v_osuser) = 'USER0810')) THEN -- EXECUTE IMMEDIATE 'ALTER SESSION SET OPTIMIZER_DYNAMIC_SAMPLING=9'; -- EXECUTE IMMEDIATE 'ALTER SESSION SET "_gby_hash_aggregation_enabled" = FALSE'; -- EXECUTE IMMEDIATE 'ALTER SESSION SET "_UNNEST_SUBQUERY" = FALSE'; -- END IF; END;
|