This post is also available in:
English
Quando a senha de um usuário expira em uma das bases que administro, alguns usuários reclamam que se trata de um usuário de aplicação e a senha não pode expirar nunca. Desta forma, se o schema realmente deve ser usado apenas pela aplicação, eu criei um processo que irá controlar esse acesso baseado nas informações da sessão do usuario, que pode ser um ou mais dos seguintes itens:
- Hostname do usuário
- Usuário de SO
- Terminal do usuário
- Módulo da Aplicação do usuário
- Endereço IP do usuário
Neste artigo mostrarei o funcionamento deste script. Vamos então começar:
Será necessário criar os seguintes objetos:
- Profile de usuário de aplicação
- Tabela de controle
- Trigger de acesso
Primeiramente, eu crie um profile que a senha não expira e cujos usuários pertencentes a ele passarão a ser controlados pela trigger de login:
CREATE PROFILE "DEFAULT_APP_USER"
LIMIT
COMPOSITE_LIMIT DEFAULT
SESSIONS_PER_USER DEFAULT
CPU_PER_SESSION DEFAULT
CPU_PER_CALL DEFAULT
LOGICAL_READS_PER_SESSION DEFAULT
LOGICAL_READS_PER_CALL DEFAULT
IDLE_TIME DEFAULT
CONNECT_TIME DEFAULT
PRIVATE_SGA DEFAULT
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION DEFAULT
PASSWORD_LOCK_TIME 30/1440
PASSWORD_GRACE_TIME UNLIMITED;
OBS: Note que neste PROFILE, a maior parte dos parâmetros são herdados do profile DEFAULT, exceto a senha que não expira e a tentativa de logins com erro, que são 10 a cada 30 minutos.
Em seguida, devemos criar a tabela (e um índice) onde você fará o controle dos acessos:
CREATE TABLE TB_FILTER_LOGON ( SESSION_USER VARCHAR2(50) NOT NULL, HOST VARCHAR2(50), OS_USER VARCHAR2(50), TERMINAL VARCHAR2(50), MODULE VARCHAR2(50), IP_ADDRESS VARCHAR2(50) ) TABLESPACE USERS; CREATE INDEX I1_FILTER_LOGON ON TB_FILTER_LOGON(SESSION_USER) TABLESPACE USERS;
Nesta tabela, o único campo obrigatório é o "SESSION_USER", que representa o usuário que está logando no BD. Os outros campos podem ser preenchidos ou não de acordo com os filtros que deseja ser feito.
A coluna "SESSION_USER" também não é PK, o que permite que uma combinação de filtros diferentes possam ser aplicados.
Por fim, crie a trigger que será usada para controlar o acesso:
CREATE OR REPLACE TRIGGER TRG_FILTER_LOGON
AFTER LOGON ON DATABASE
DECLARE
V_GLOBAL_NAME VARCHAR2(50);
V_PROFILE VARCHAR2(50);
V_CHECK NUMBER;
---
V_HOST VARCHAR2(50);
V_OS_USER VARCHAR2(50);
V_TERMINAL VARCHAR2(50);
V_MODULE VARCHAR2(50);
V_IP_ADDRESS VARCHAR2(50);
V_SESSION_USER VARCHAR2(50);
BEGIN
-- Created by DBA RJ - www.dbarj.com.br - 2015/01
-- Get Connection Info
V_HOST := SUBSTR(TRIM(UPPER(SYS_CONTEXT('USERENV', 'HOST'))), 1, 50);
V_OS_USER := SUBSTR(TRIM(UPPER(SYS_CONTEXT('USERENV', 'OS_USER'))), 1, 50);
V_TERMINAL := SUBSTR(TRIM(UPPER(SYS_CONTEXT('USERENV', 'TERMINAL'))), 1, 50);
V_MODULE := SUBSTR(TRIM(UPPER(SYS_CONTEXT('USERENV', 'MODULE'))), 1, 50);
V_IP_ADDRESS := SUBSTR(TRIM(UPPER(SYS_CONTEXT('USERENV', 'IP_ADDRESS'))), 1, 50);
V_SESSION_USER := SUBSTR(TRIM(UPPER(SYS_CONTEXT('USERENV', 'SESSION_USER'))), 1, 50);
-- Get Database Name
SELECT GLOBAL_NAME
INTO V_GLOBAL_NAME
FROM GLOBAL_NAME;
-- Get Profile Name
SELECT PROFILE
INTO V_PROFILE
FROM DBA_USERS
WHERE USERNAME = V_SESSION_USER;
IF V_PROFILE = 'DEFAULT_APP_USER'
THEN
-- Check
CASE V_GLOBAL_NAME
WHEN 'DBFAIRUS' THEN
SELECT TOTAL1 + TOTAL2
INTO V_CHECK
FROM (SELECT COUNT(*) TOTAL1
FROM TB_FILTER_LOGON
WHERE TRIM(UPPER(SESSION_USER)) = V_SESSION_USER
AND NVL(TRIM(UPPER(HOST)), NVL(V_HOST, 1)) = NVL(V_HOST, 1)
AND NVL(TRIM(UPPER(OS_USER)), NVL(V_OS_USER, 1)) = NVL(V_OS_USER, 1)
AND NVL(TRIM(UPPER(TERMINAL)), NVL(V_TERMINAL, 1)) = NVL(V_TERMINAL, 1)
AND NVL(TRIM(UPPER(MODULE)), NVL(V_MODULE, 1)) = NVL(V_MODULE, 1)
AND NVL(TRIM(UPPER(IP_ADDRESS)), NVL(V_IP_ADDRESS, 1)) = NVL(V_IP_ADDRESS, 1)),
(SELECT DECODE(COUNT(*), 0, 1, 0) TOTAL2 -- If the entry does not exist, will return 1 to unlock the user
FROM TB_FILTER_LOGON
WHERE TRIM(UPPER(SESSION_USER)) = V_SESSION_USER);
IF (V_CHECK <> 0)
THEN
NULL;
--OK
ELSE
--NULL;
RAISE_APPLICATION_ERROR(-20000, 'YOU ARE NOT AUTHORIZED TO LOGIN WITH THIS USERNAME. PLEASE CONTACT YOUR DBA.');
END IF;
ELSE
NULL;
END CASE; END IF;
EXCEPTION
-- Evita erros quando não consegue obter o GLOBAL_NAME ou o PROFILE (Data Guard)
WHEN NO_DATA_FOUND THEN
NULL;
END;
/
Exemplo de cenários:
1) O usuário SCOTT pode se conectar apenas:
- Do IP 10.192.12.43 com usuário de SO "scripts" OU
- Do IP 10.192.12.40 com usuário de SO "root"
SQL> select t.* from tb_filter_logon t
SESSION_USER HOST OS_USER TERMINAL MODULE IP_ADDRESS
1 SCOTT scripts 10.192.12.43
2 SCOTT root 10.192.12.40
2) O usuário EXPUSER pode se conectar apenas:
- Da máquina cujo hostname é "syslogfubddsne001" e utilizando o "SQL Developer" OU
- Do IP 10.192.12.40 utilizando JDBC e com o usuário de SO "javacode"
SQL> select t.* from tb_filter_logon t
SESSION_USER HOST OS_USER TERMINAL MODULE IP_ADDRESS
1 EXPUSER syslogfubddsne001 SQL Developer
2 EXPUSER javacode JDBC Thin Client 10.192.12.40
OBS: Se não existir qualquer entrada do usuário (SESSION_USER) na tabela, o usuário não será bloqueado.
Por fim, lembre-se: se o usuário possuir o GRANT de "Administer Database Trigger", direta ou indiretamente (como o caso da role DBA), ele nunca será bloqueado por triggers de logon!
Gostou? Não deixe de comentar ou deixar um 👍!
