Limitando conexão ao Oracle baseado no IP do usuário e outras informações

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:

  1. Profile de usuário de aplicação
  2. Tabela de controle
  3. 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 👍!

Deixe um comentário

Seu e-mail não será publicado.