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 👍!