Restrição em PL/SQL

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
Responder
JeanCarlos
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Mensagens: 5
Registrado em: Qui, 27 Nov 2014 9:51 am

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 !!
Avatar do usuário
stcoutinho
Moderador
Moderador
Mensagens: 850
Registrado em: Qua, 11 Mai 2011 5:15 pm
Localização: são Paulo - SP

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
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Mensagens: 5
Registrado em: Qui, 27 Nov 2014 9:51 am

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.
restricao.jpg
Tinho
Rank: DBA Sênior
Rank: DBA Sênior
Mensagens: 317
Registrado em: Seg, 16 Nov 2009 4:50 pm
Localização: São Paulo - SP

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
Moderador
Moderador
Mensagens: 2016
Registrado em: Qua, 12 Jan 2005 3:25 pm
Localização: Araraquara - SP
Abraço,

Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP

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
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Mensagens: 5
Registrado em: Qui, 27 Nov 2014 9:51 am

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
Rank: Analista Pleno
Rank: Analista Pleno
Mensagens: 128
Registrado em: Sáb, 18 Nov 2006 11:51 am
Localização: Rio de Janeiro - RJ

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
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Mensagens: 5
Registrado em: Qui, 27 Nov 2014 9:51 am

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
Moderador
Moderador
Mensagens: 2016
Registrado em: Qua, 12 Jan 2005 3:25 pm
Localização: Araraquara - SP
Abraço,

Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP

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
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Mensagens: 11
Registrado em: Qui, 08 Jan 2015 9:22 pm

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
Rank: Analista Pleno
Rank: Analista Pleno
Mensagens: 128
Registrado em: Sáb, 18 Nov 2006 11:51 am
Localização: Rio de Janeiro - RJ

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
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Mensagens: 11
Registrado em: Qui, 08 Jan 2015 9:22 pm

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
Responder
  • Informação
  • Quem está online

    Usuários navegando neste fórum: Nenhum usuário registrado e 17 visitantes