ACESSO COM TEMPO LIMITADO

Olá.


Para análise de erros em aplicações, desenvolvedores, muitas vezes, necessitam pelo menos de acesso leitura aos bancos de dados de produção. A boa prática para esses casos é o DBA criar um usuário específico para essa atividade atribuindo-lhe somente os privilégios CREATE SESSION, SELECT sobre as tabelas da aplicação. Em seguida, liberar este usuário para os desenvolvedores.


Melhor prática ainda, é manter a conta deste usuário sempre bloqueada e liberá-la somente quando houver uma solicitação/autorização formal (e-mail ou documento) para acesso aos dados de produção. Parece burocrático, mas mantém documentado e registrado todo acesso ao banco de dados.


Pensando nisto, implementei um processo que libera o acesso por um determinado tempo (1h por exemplo) para que sejam feitas consultas aos dados do banco de dados de produção. Após certo tempo, o acesso é bloqueado e todas as sessões do usuário são desconectadas.


Chega de explicações e vamos ao processo:

Passo 1: Partindo do pressuposto de que o usuário de acesso leitura já tenha sido criado, a primeira etapa é desenvolver uma procedure que vai executar as tarefas de desbloquear o usuário com acesso somente leitura e agendar um job no banco de dados. Este job vai executar uma outra procedure que faz o caminho inverso, ou seja, bloqueia novamente o usuário de leitura e executa os KILL SESSIONs de sessões. Isso mesmo, utilizei um simples job para auxiliar neste processo.

Abaixo, o código da procedure que libera o acesso e agenda o job:

CREATE OR REPLACE PROCEDURE prcLiberaAcessoAProducao(parUSUARIO VARCHAR2)
IS


intCURSOR INTEGER;
intPROCESSED INTEGER;
numJOB NUMBER;
numJOBNO NUMBER;
varWHAT VARCHAR2(100);
varUSUARIO VARCHAR2(100);

BEGIN

   varUSUARIO := UPPER(parUSUARIO);
   varWHAT := 'prcBloqueiaAcessoAProducao(' || CHR(39) || varUSUARIO || CHR(39) || ');';

   --desbloqueia o usuario
   intCURSOR := DBMS_SQL.OPEN_CURSOR;
   DBMS_SQL.PARSE(intCURSOR, 'ALTER USER ' || UPPER(parUSUARIO) || ' ACCOUNT UNLOCK', DBMS_SQL.NATIVE);
   intPROCESSED := DBMS_SQL.EXECUTE(intCURSOR);
   DBMS_SQL.CLOSE_CURSOR(intCURSOR);

   -- agenda job para bloquear usuario em 1 hora
   BEGIN

      SELECT job INTO numJOB
      FROM DBA_JOBS
      WHERE what LIKE '%' || varWHAT || '%';

      -- atualiza job
      DBMS_JOB.NEXT_DATE(job=>numJOB, next_date=>SYSDATE + 1/24);
      DBMS_OUTPUT.PUT_LINE('*** JOB de bloqueio do usuario ' || varUSUARIO || ' alterado com sucesso! ***');
      DBMS_OUTPUT.PUT_LINE('*** Tempo disponivel para acesso ao usuario ' || UPPER(parUSUARIO) || ' expira    em 1h! ***');

   EXCEPTION
      -- insere job
      WHEN no_data_found THEN
      DBMS_JOB.SUBMIT(job=>numJOBNO, what=>varWHAT, next_date=>SYSDATE + 1/24);
      DBMS_OUTPUT.PUT_LINE('*** JOB de bloqueio do usuario ' || varUSUARIO || ' inserido com sucesso! ***');
      DBMS_OUTPUT.PUT_LINE('*** Tempo disponivel para acesso ao usuario ' || varUSUARIO || ' expira em 1h! ***');


   END;

END;
/

A procedure recebe o parâmetro usuário. Portanto, pode ser executada para qualquer usuário do banco de dados.

Passo 2:. A etapa seguinte é desenvolver uma outra procedure que, após 1h, vai ser executada via job bloqueando a conta do usuário leitura e desconectando as sessões deste usuário.

Abaixo, o código:

CREATE OR REPLACE PROCEDURE prcBloqueiaAcessoAProducao(parUSUARIO VARCHAR2)
IS

intCURSOR INTEGER;
intPROCESSED INTEGER;
varSID VARCHAR2(100);
varSERIAL VARCHAR2(100);
varUSUARIO VARCHAR2(100);

BEGIN

    varUSUARIO := UPPER(parUSUARIO);   
    -- bloqueia o usuario
    intCURSOR := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(intCURSOR, 'ALTER USER ' || varUSUARIO || ' ACCOUNT LOCK', DBMS_SQL.NATIVE);
    intPROCESSED := DBMS_SQL.EXECUTE(intCURSOR);
    DBMS_SQL.CLOSE_CURSOR(intCURSOR);
   
    -- desconecta sessoes do usuario no banco de dados de producao
    FOR reg_cursor_killsession IN (SELECT sid, serial#
                                   FROM v$session
                                   WHERE username = varUSUARIO)
    LOOP
        varSID := TO_CHAR(reg_cursor_killsession.sid);
        varSERIAL := TO_CHAR(reg_cursor_killsession.serial#);
        intCURSOR := DBMS_SQL.OPEN_CURSOR;
        DBMS_SQL.PARSE(intCURSOR, 'ALTER SYSTEM KILL SESSION ''' || varSID || ',' || varSERIAL || ''' IMMEDIATE', DBMS_SQL.NATIVE);
        intPROCESSED := DBMS_SQL.EXECUTE(intCURSOR);
        DBMS_SQL.CLOSE_CURSOR(intCURSOR);
    END LOOP;

EXCEPTION
    WHEN OTHERS THEN
        DBMS_SQL.CLOSE_CURSOR(intCURSOR);
END;
/

Com as procedures criadas, agora basta apenas executar a procedure prcLiberaAcessoAProducao toda vez que for solicitado acesso aos dados da produção.

Até a próxima.