Scripts Privilégios de Acesso

Olá Pessoal,

     Nesta semana, estou compartilhando scripts que podem ser utilizados para recuperar privilégios de acesso de usuários ou objetos em um Banco de Dados Oracle.
 

      Os scripts geram comandos dinâmicos que podem executados posteriormente para efetuar o que eles se propõem. São eles:
 

         1- recuperar_grants_usuario.sql:

             Permite recuperar todos os privilégios de objeto, sistema e roles de um usuário e, se desejado, atribuir os privilégios para outro usuário.

             Útil para clonar privilégios de usuários.

             Possui os parâmetros USUARIO_ORIGEM e USUARIO_DESTINO. Para somente recuperar os privilégios de um determinado usuário, forneça o mesmo nome de usuário para os 2 parâmetros.
 

         2- recuperar_grants_objeto.sql:

              Permite recuperar todos os privilégios que os usuários possuem sobre ou mais objetos de banco de dados.

              Possui os parâmetros SCHEMA_NAME e TABLE_NAME, que permitem filtrar por nome do schema e/ou nome do objeto. Se não desejar utilizar algum dos parâmetros de filtro, não forneça valor para eles quando for solicitado, ao executar o script.
 

         3- gerar_script_privilegios_todos_objetos_schema.sql:

             Permite gerar um conjunto de comandos para atribuir todos os privilégios possíveis em todos os objetos de um determinado schema.
 

             Possui os parâmetros OWNER e USER_NAME. Se desejar apenas recuperar todos os privilégios possíveis dos objetos de um schema forneça o mesmo valor para os 2 parâmetros. Se além de recuperar for desejado atribuir os privilégios para outro usuário, forneça o nome do usuário correspondente para o parâmetro USER_NAME.           
 

      Para fazer download dos scripts (arquivo scripts_privilegios.zip), acessem a pasta Oracle/Scripts, em Pastas Públicas, no MEU SKY DRIVE.


Abraços!

Recuperando dados da sessão de usuário

Pessoal,
 

     No artigo de hoje irei demonstrar o uso da função SYS_CONTEXT, uma função muito simples e útil do Oracle Database, que pode ser utilizada, por exemplo, para recuperar dados de uma sessão de usuário e e permitir auditoria de acesso. Como parte das minhas atividades de administração e segurança de BD, eu já utilizei essa função várias vezes dentro de triggers de logon para permitir verificar quem está se conectando no BD, horário, aplicação etc.

    A função SYS_CONTEXT pode ser utilizada tanto em instruções SQL quanto em blocos PL/SQL. No Oracle 10g/11g ela retorna um valor do tipo VARCHAR2 e aceita 2 parâmetros de entrada, também do tipo VARCHAR2: namespace e parâmetro. Esta função permite retornar dados de diversos tipos de contexto (conjunto de atributos ou variáveis predefinidas que são utilizadas por uma aplicação), dentre eles, o contexto USERENV, que é criado automaticamente pelo Oracle quando um usuário inicializa uma sessão de Banco de Dados, e que contém dados que descrevem a atual sessão do usuário.

     Segue abaixo um exemplo de instrução SQL utilizada para retornar dados do contexto USERENV relacionados à sessão de usuário atual (sessão que irá executar a instrução SQL):

         SELECT SYS_CONTEXT('USERENV', 'SERVER_HOST')   "Nome SERVIDOR",
                        SYS_CONTEXT('USERENV', 'INSTANCE_NAME') "Instância",
                        SYS_CONTEXT('USERENV', 'HOST')          "CLIENTE",
                        SYS_CONTEXT('USERENV', 'IP_ADDRESS')    "IP Cliente",
                        SYS_CONTEXT('USERENV', 'OS_USER')       "Usuário SO",
                        SYS_CONTEXT('USERENV', 'SESSION_USER')  "Usuário BD"
          FROM    DUAL;

Obs.: Se a instrução acima fosse executada em uma máquina cliente com o nome ws01 e IP 10.1.1.2, com o usuário de nome administrador logado no SO, conectado no BD bd1 do servidor server01, com o usuário de BD fabio, o resultado seria:

          Nome SERVIDOR  | Instância | CLIENTE | IP Cliente |  Usuário SO    | Usuário BD
          server01                     bd1          ws01          10.1.1.2      administrador   fabio 

   

     Para mais informações, consulte os links das referências.


Referências:
     http://download.oracle.com/docs/cd/B14117_01/server.101/b10759/functions150.htm#g1513460
     http://psoug.org/reference/sys_context.html

Scripts Manutenção de Índices

Olá Pessoal,

     Nesta semana, estou compartilhando scripts que podem ser utilizados para efetuar manutenções em  índices de um Banco de Dados Oracle. Os scripts permitem:

         - Reconstruir índices de um determinado schema (exceto LOB e IOT);
         - Reconstruir índices de um determinado schema em um novo tablespace;
         - Mover índices LOB de um schema para um novo tablespace;
         - Alterar a configuração dos índices para não gerar log.

      Para fazer download dos scripts (arquivo scripts_indices_manut.zip), acessem a pasta Oracle/Scripts, em Pastas Públicas, no MEU SKY DRIVE.


Até semana que vem!

Funções recursivas no Oracle Database

Pessoal,
   

     Este artigo é simples e curto e tem como objetivo apenas demonstrar o conceito de recursividade e como utilizar uma função recursiva em um Banco de Dados Oracle.


     Em Ciências da Computação, o termo recursividade é utilizado para a definição de uma subrotina (função ou método) que pode invocar a si mesma. Este conceito é simples na teoria, mas na prática (até o primeiro contato) é um pouco mais complexo. Com o objetivo de facilitar o entendimento de recursividade, iremos criar e utilizar uma função recursiva em um Banco de Dados Oracle.
   

     O código abaixo cria uma função recursiva com o nome fc_recursiva_exemplo, que retorna uma string contendo todos os números entre um valor de início (intContInicio) e um valor fim (intContFim), recebidos como  parâmetros de entrada pela função. Esta função é executada internamente, N vezes (conforme valores de início e fim) e utiliza o parâmetro strVazia, que é preenchido cumulativamente e que deve ser chamado com um valor vazio para 'montar' a string de retorno.
   

CREATE or replace FUNCTION fc_recursiva_exemplo(intContInicio IN NUMBER, intContFim IN NUMBER, strVazia IN VARCHAR2)
RETURN VARCHAR2
is
    intContInicioTemp NUMBER;
    strTemp VARCHAR2(4000);
BEGIN   
    intContInicioTemp:= (intContInicio+1);    
    --dbms_output.put_line('linha: ' || i);
    strTemp:=strVazia || intContInicio || ', ';


    if (intContInicioTemp <= intContFim) then       
        select  fc_recursiva_exemplo(intContInicioTemp, intContFim, strTemp) into strTemp from dual;
    else
        strTemp:=SUBSTR(strTemp,1,LENGTH(strTemp)-2);
    end if;
   
    Return strTemp;
END;
   

   Obs.: Reparem que no código acima, dentro do bloco IF, há uma chamada para a própria função (fc_recursiva_exemplo) que é executada N vezes, até que o valor fim seja alcançado. Isso é feito sem executar uma rotina de loop, utilizando apenas o conceito de recursividade.


     Segue abaixo um exemplo para testar a função:

         select fc_recursiva_exemplo(1,10,'') from dual;
  
    que deverá retornar a seguinte string:
          1, 2, 3, 4, 5, 6, 7, 8, 9, 10
        

Scripts Índices

Olá Pessoal,

     Nesta semana, estou compartilhando scripts para consultar dados sobre índices de um Banco de Dados Oracle. Os scripts permitem:

         - Verificar se existe mais de um índice na mesma coluna de uma tabela;
         - Verificar estatísticas do índice, tais como: tamanho, qtde. linhas etc.;
         - Consultar qtde. de índices por tabela ou schema;
         - Verificar índices monitoradas e sua utilização;
         - Verificar índices não analisados por coletas de estatísticas.


      Para fazer download dos scripts (arquivo scripts_indices.zip), acessem a pasta Oracle/Scripts, em Pastas Públicas, no MEU SKY DRIVE.


Até semana que vem!