Restrição em PL/SQL

[JeanCarlos]

boa tarde galera,
estou com um problema aqui no meu serviço, meu supervisor pediu para eu fazer uma restrição de algumas palavras em pl/sql, onde o usuário não poderá escrever em seu script "INSERT, DROP, DELETE, etc". Já pesquisei em alguns sites, mas não encontrei nada. Essa é a primeira vez que mexo com pl/sql.

Desde já um obrigado à quem poder me ajudar !!

[stcoutinho]

Olá Jean,

Só esclarecendo:

- INSERT,DELETE,UPDATE são comandos DML (manipulação)
- CREATE,ALTER,DROP são comandos DDL (definição)

Pelo que pude entender, seu supervisor deseja que os usuários somente façam SELECT nas tabelas do sistema (um comando DML). Seria este o seu caso?

Se for este o caso, vamos fazer de conta que o schema onde se encontram os objetos de banco (tabelas, indices,etc) tem o nome "XPTO". Assim, você poderia executar os seguintes passos como SYS (ou algum usuário com perfil SYSDBA):

A) Criar uma ROLE de NOME R_XPTO;

B) Atribuir ao mesmo as permissões de SELECT nas tabelas do XPTO
Ex:

Selecionar tudo

          GRANT SELECT ON <TABELA_A> TO R_XPTO;
          GRANT SELECT ON <TABELA_B> TO R_XPTO;
          GRANT SELECT ON <TABELA_C> TO R_XPTO;
          ....
          GRANT SELECT ON <TABELA_Z> TO R_XPTO;

C) Criar um usuário de nome XPTO_AUDIT, que só terá permissão de SELECT nas tabelas;
Ex:

Selecionar tudo

            CREATE USER XPTO_AUDIT IDENTIFIED BY XPTO_AUDIT
            DEFAULT TABLESPACE <tablespace dados>
            TEMPORARY TABLESPACE <tablespace temporaria>
            PROFILE DEFAULT;

            ALTER USER XPTO_AUDIT QUOTA 0 ON  <tablespace dados>;

            GRANT  CREATE SESSION      TO XPTO_AUDIT;
            GRANT  ALTER SESSION       TO XPTO_AUDIT;
            GRANT R_XPTO TO XPTO_AUDIT;

Não sei se listei as permissões mínimas para este novo usuário. Você precisaria se conectar com ele e fazer testes com os comandos proibidos pelo seu supervisor.

Esta é uma das possibilidades. Acho que seria legal você detalhar mais a sua necessidade. Tenho certeza que os foristas vão poder lhe dar outras dicas.

Abraços,

Sergio Coutinho

[JeanCarlos]

Muito obrigado pelas dicas Sergio Coutinho, elas serão de grande utilidade para tentar resolver meu problema.

Mas só para tentar deixar mais claro, eu tenho uma tabela com com 11 campos(colunas), sendo que em um deles, é onde o usuário vai escrever seu scrip (através do oracle forms), e é onde eu tenho que fazer a restrição dessas palavras INSERT, DROP etc, para que o usuário não possa fazer manipulações em outras tabelas através desse código. E quando ele tentar salvar os dados da tabela aparecer um erro.

[Tinho]

Bom dia.

Talvez a situação seja outra. Pelo que me parece, vocês estão criando uma tela para fazer um "CRUD" e provavelmente o seu chefe esteja querendo se precaver ou receoso com a iminente possibilidade de "ataques" "SQL Injection". Seria esse o cenário?

Bom, é uma possibilidade. Se for, procure se atualizar quanto aos termos e conceitos para analisar qual melhor forma de você atender a essa necessidade.

Att.,

[Trevisolli]

Boa tarde brother, beleza?

Além das dicas acima, dos colegas, acharia interessante você testar também a função INSTR:

Selecionar tudo

declare
  w_texto varchar2(4000) := 'insert into tabela (cod,descr) values (1,''a'')';
begin
  if ( instr(upper(w_texto),'INSERT') > 0  or 
       instr(upper(w_texto),'UPDATE') > 0  or 
       instr(upper(w_texto),'DELETE') > 0 ) then
    dbms_output.put_line('Localizada instrução DML: INSERT/UPDATE/DELETE.');
  end if;
end;

Dá uma olhada, faz uns testes e adaptações necessárias no script.

Abraço,

Trevisolli

[JeanCarlos]

Valeu aí galera pela ajuda de todos, acho que consegui resolver esse problema. Criei uma trigger !!

Selecionar tudo

create or replace trigger t_nome
  before insert or update of nomeDoCampo on nomeTabela
  for each row

begin
  if ( instr (:new.nomeDoCampo,'INSERT') > 0 or
       instr (:new.nomeDoCampo,'UPDATE') > 0 or
       instr (:new.nomeDoCampo,'DELETE') > 0 or
       instr (:new.nomeDoCampo,'DROP') > 0 ) then

       raise_application_error(-2001, 'Localizado:
                               INSERT, UPDATE, DELETE ou DROP no campo SCRIPT');


  end if;

end t_nome;

[Renato Menezes Viana]

Jean:

Não querendo ser chato, mas como você não está adotando soluções "formais SQL", para garantir segurança através das observações do Coutinho, com o uso de ROLE x GRANT x USER, verifique na sua solução se é possível qualquer artimanha do usuário no :new.nomeDoCampo que conheça alguma coisa de PL/SQL e, por exemplo, monte os comandos com auxílio de aspas simples e || que resultem na sintaxe final "proibida" para o execute:

Selecionar tudo

execute immediate 'UP'||'DATE tabela set campo = ''ALTERADO'' where chave = 1';

Exagerada a preocupação, mas nem pensei no usuário executar uma procedure ou function do seu Banco de Dados, bastando o usuário digitar adequadamente.

Doidera minha?... segurança é segurança... ainda mais nos dias de hoje.

Abs, Renato Viana

[JeanCarlos]

Valeu pelas dicas Renato Viana, vou procurar melhorar.
Críticas são sempre bem vindas, para que eu possa melhorar !!

Abraço, Jean Carlos

[Trevisolli]

Concordo plenamente com as considerações do Renato, inclusive acredito ser o melhor cenário.
É que neste caso, em específico, o INSTR ainda funcionaria, se o comando fosse montado no texto do forms e, validado este texto (como testado anteriormente):

Selecionar tudo

declare 

  v varchar2(1000) := 'UP'||'DATE tabela set campo = ''ALTERADO'' where chave = 1';
  
begin
  if instr(v,'UPDATE') > 0 then
    dbms_output.put_line('Cláusula não permitida. DML: Insert/Update/Delete.');
  end if;
end;  

Mas, como podem exister "N" variações, enfim, várias outras maneiras de se tentar burlar a regra, o ideal seria tratar via GRANT's mesmo.

Valeu pessoal.
Abraço,

Trevisolli

[FernandesRE]

Fala galera, beleza?

Pelo que entendi, você quer possibilitar a execução de alguns scripts, mas eles não podem alterar dados.

Esses scripts seriam executados por um execute immediate?

Eu nunca usei, mas acho que se você criar uma função em uma package, para rodar esses scripts, e colocar nela um PRAGMA RESTRICT_REFERENCES impedindo as alterações em tabelas, você terá o resultado que procura.

Segue um link com esse assunto. (acho que você vai precisar da restrição WNDS)

http://docs.oracle.com/cd/B28359_01/app ... LNPLS01339

[Renato Menezes Viana]

Carnaval chegando...

Fernandes, mandou bem, no seu link recomendado está a solução.

Agora, você podia esclarecer se o SET TRANSACTION READ ONLY iria garantir que na execução dos comandos IMMEDIATE não aconteceria nada além de READ, seja qual fosse a tabela e na declaração de EXCEPTION poderíamos pegar o erro quando o comando não fosse READ?

Selecionar tudo

COMMIT; 

SET TRANSACTION READ ONLY NAME 'Comando Immediate'; 

EXECUTE IMMEDIATE ....
EXCEPTION WHEN OTHERS .... 



COMMIT;

[FernandesRE]

Fala Renato, beleza?

Eu nunca usei o set transaction, mas montei um exemplo para testarmos.

Selecionar tudo

declare
vqtd number(10);
BEGIN
  --finaliza qualquer transação aberta
  COMMIT;
  --inicia uma noca transação como somente leitura
  SET TRANSACTION READ ONLY NAME 'SoLeitura';
  --executa um select para ontar os empregados
  SELECT count(*)
  into vqtd
  FROM employees;
  --
  --tenta atualizar os empregados
  /*UPDATE EMPLOYEES
  set email = 'teste';*/
  --
  --tenta fazer um update via execute immediate
  --execute immediate ('UPDATE EMPLOYEES set email = ''teste''');
  --imprime a quantidade
  DBMS_OUTPUT.PUT_LINE(vqtd);
  --termina a transação iniciada no SET
  rollback;
END;

Pelo teste e o que eu pesquisei, vai resolver o seu problema.

Montei o exemplo no owner HR e deixei comentado os UPDATEs para executar o bloco sem erros, para testar com os UPDATEs, basta descomentar e rodar novamente, ele vai retornar um erro ORA-01456

[]s