| 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;
|
