Executando comandos do Sistema Operacional em SQL e PL/SQL

ATUALIZADO EM 27/07/2018

Olá pessoal,

     No artigo de hoje vou mostrar como executar comandos do Sistema Operacional dentro de um Banco de Dados Oracle, utilizando o package OS_COMMAND, a partir de instruções SQL ou PL/SQL.

     No decorrer dos meus últimos 8 anos atuando como DBA, já vi diversas vezes em fóruns e grupos de discussão, vários usuários de Banco de Dados Oracle questionando como executar comandos do Sistema Operacional (SO) dentro do Banco de Dados (BD). Dentre as opções disponíveis, as soluções mais comuns são: executar um job externo via package DBMS_SCHEDULER ou executar o package OS_COMMAND dentro de um bloco PL/SQL. A segunda é mais difícil de implementar, mas permite maior interação entre código PL/SQL e comandos do SO. Recentemente tive que usá-la para compactar automaticamente dumps do BD, logo após sua geração, via comando gzip do SO Linux (ver artigo Compressão de dumps do Oracle Data Pump). Foi aí que surgiu a ideia de publicar este artigo.

     OS_COMMAND é um pacote para uso no Oracle Database que fornece acesso ao shell do Sistema Operacional (SO) e ao sistema de arquivos, através de comandos SQL e PL/SQL. A grande vantagem que eu vejo no uso deste pacote, em relação a um job externo, é que ele permite executar um comando shell e acessar o STDIN, STDOUT e STDERR.  Agora vem a pergunta: Por que esse recurso é importante? Porque ele permite verificar o retorno de um comando executado no SO, e até mesmo o erro que ocorreu em sua execução (caso ele não tenha sido executado com sucesso), permitindo alta interatividade entre comandos SQL, PL/SQL e os comandos que são executados no SO, fora do BD.

     Outros recursos que se destacam no OS_COMMAND:

          - Permite ver o conteúdo de um diretório do SO através de um SELECT no BD;

          - Permite carregar arquivos de imagem ou documentos, existentes no Sistema de Arquivos do SO, em objetos BLOB,

     O pacote OS_COMMAND utiliza JVM que está embutido no Oracle Database desde a versão 8i, encapsulando as suas funcionalidades e tornando-as acessíveis à camada SQL do BD. Pelo fato do código Java ter um mecanismo de segurança próprio, para utilizar as funcionalidades do OS_COMMAND,  o usuário deve ter além do privilégio de execução deste pacote, privilégios para acessar ou executar comandos shell através de código Java. Estes privilégios são concedidos através da procedure DBMS_JAVA.GRANT_PERMISSION. Para informações adicionais sobre esta segurança adicional, consulte o link Security for Oracle Database Java Applications.

 
     Para efetuar download do pacote OS_COMMAND e fazer a sua instalação, acesse este link, faça o download do arquivo PLSQL_OSCOMMAND_1.0.zip, descompacte-o e leia o arquivo readme.txt, que aparecerá após a descompactação.
  

     Segue abaixo um roteiro de exemplo de uso da função os_command.exec_clob, que será utilizada pelo usuário FABIO, para retornar através do comando 'ls -la', uma listagem dos arquivos existentes no diretório /tmp de uma máquina com SO Linux:

ROTEIRO DE USO DA FUNÇÃO OS_COMMAND.EXEC_CLOB

1- Atribuindo os privilégios necessários:

      Antes de executar a função os_command.exec_clob, devemos nos conectar no BD (através do SQL Plus, SQL Developer ou ferramenta similar) com um usuário que tenha privilégios administrativos, para conceder ao usuário FABIO todos os privilégios necessários para que ele execute a sua tarefa com sucesso:

     a) Execute o script abaixo para conceder privilégios de leitura e escrita em STDIN/STDOUT + permissão para executar o comando shell do Linux "ls -la":

begin

  -- fornece privs de leitura em STDIN para o usuario FABIO
  dbms_java.grant_permission(
     grantee =>           'FABIO',  -- nome do usuario desejado
     permission_type =>   'SYS:java.lang.RuntimePermission',
     permission_name =>   'readFileDescriptor',
     permission_action => null
  );

  COMMIT;

 
  -- fornece privs de escrita em STDOUT para o usuario FABIO
  dbms_java.grant_permission(
     grantee =>           'FABIO', -- nome do usuario desejado
     permission_type =>   'SYS:java.lang.RuntimePermission',
     permission_name =>   'writeFileDescriptor',
     permission_action => null
  );

  COMMIT;

  -- fornece privs p/ o usuario FABIO executar o comando 'ls -la'
  dbms_java.grant_permission(
     grantee =>           'FABIO', -- nome do usuario desejado
     permission_type =>   'SYS:java.io.FilePermission',
     permission_name =>   '/bin/ls',
     permission_action => 'execute'

  );

  COMMIT;

  -- fornece privs p/ o usuario FABIO executar todos os comandos shell (CUIDADO)
    dbms_java.grant_permission(
    grantee =>           'FABIO',
    permission_type =>   'SYS:java.io.FilePermission',
    permission_name =>   '<<ALL FILES>>',
    permission_action => 'execute'
    );
  COMMIT;

end;

     b) Conceda privilégios de execução nos objetos + java source abaixo, para o usuário FABIO:

     grant execute on OS_COMMAND to FABIO; -- nome do usuario desejado

     grant execute on "ExternalCall" to FABIO;

     grant execute on java source "OS_HELPER" to FABIO;
     grant execute on java source "FILE_TYPE_JAVA" to FABIO;

     grant execute on "FileType" to FABIO;

     grant execute on lob_writer_plsql to FABIO;
     grant execute on FILE_PKG to FABIO;

     grant execute on FILE_TYPE to FABIO;
     grant execute on FILE_LIST_TYPE to FABIO;
     grant execute on file_security to FABIO;

2- Executando a função:

     Conecte-se no BD com o usuário FABIO e execute a instrução SQL abaixo para conferir o seu resultado (ver Imagem 01):
     select os_command.exec_clob('ls -la /tmp') directory_listing from dual;

Imagem 01 - Executando OS_COMMAND.EXEC_CLOB

Obs.: A última versão do OS_COMMAND que baixei e instalei em 05/2018, no Oracle 12c, tive que consultar o arquivo "os_command/src/12c/04-grant-public.sql" para montar os comandos de grants do item "1.b)". Se algo der errado sugiro consultá-lo também!
  

Bom pessoal, por hoje é só!
Espero que você tenha gostado e que o artigo lhe seja útil!
Se você gostou, deixe o seu comentário!

[]s


Referências:
     - PL/SQL operating system and filesystem integration
     - Executing operating system comands from PL/SQL