Menu de navegação

[rwarstatParticipante]

Estou com a seguinte situação: tenho um usuário que pode fazer exclusivamente select em um outro schema. Preciso que esse usuário possa criar tabela nesse outro schema. Para isso criei uma procedure que cria a tabela com um prefixo e o usuário informa o restante do nome dela. Se eu executa esse procedure. Criei a procedure no schema do usuário onde a tabela deve ser criada e que possui todas as permissões. Para o usuário que deverá criar a tabela foi dada a permissão de execute na procedure.

Quando executo a procedure, com qualquer usuário, recebo a mensagem de privilégios insuficientes.

Alguma sugestão?

Procedure
CREATE OR REPLACE PROCEDURE usurio.cria_tabela (v_parametro VARCHAR2)
IS
v_comando VARCHAR2 (2000);
BEGIN
v_comando :=
'create table usuario.cliente_fin_'
|| v_parametro
|| ' as select cd_cliente, cd_grupo from cliente_fin';

EXECUTE IMMEDIATE v_comando;
END;

Abraço,
Roberto

[burgaParticipante]

já deu privilegio de create table no esquema “alvo” pro esquema que executará a procedure?

[rwarstatParticipante]

Justamente, com exceção do usuário proprietário da procedure, nenhum outro usuário terá privilégios de DDL nesse schema.
Pelo que li, uma procedure é executada com os direitos do owner dela, certo?

Abraço,
Roberto

[lobomaudiegoParticipante]

Nesse caso você não vai conseguir criar uma tabela em outro usuário a não ser que você logue com o usuário correspondente.

Att,

Diego Monteiro

[VitorLeandroParticipante]

Cara, existe o privilégio create any table..

GRANT CREATE ANY TABLE TO ‘usuário_que_executa_a_procedure’

Ele vai poder criar tabelas em qualquer schema… Vou pensar em outra solução mais segura e posto aqui!!!

Té mais!

[fsitjaParticipante]

Dar um grant create any table é muito perigoso na minha opinião, e não vai resolver o erro. Como já mencionado pelo rwarstat, procedures rodam com DEFINER’s Rights como padrão.

Para fazer o que você quer você teria que criar a procedure com INVOKER’s rights para que o chamador dela tenha seus privilégios verificados na hora de realizar o procedimento. Com esse tipo de operação ninguém quer um usuário correndo por aí executando uma procedure com super-privilégios.

Ainda assim é uma solução deselegante e vulnerável a SQL injection. Criar tabelas em tempo de execução é algo que vai de encontro às boas práticas. O ideal seria reavaliar a necessidade.
Existem global temporary tables que podem ser usadas ou external tables, nested tables em programa, etc.

http://download.oracle.com/docs/cd/E118 … ADMIN11633

Além disso, do ponto de vista de controle de transações, o DDL dinâmico vai disparar um commit implícito em qualquer coisa que o usuário tenha feito até aquele momento, podendo causar dores de cabeça razoáveis para você mais tarde. Ou você opta por dar um rollback preventivo para evitar bagunçar o BD ou você arca com as consequências de um commit indesejado…

Se você realmente quiser levar a cabo isso, mais tarde eu posto em detalhes em casa onde tenho um SYSDBA para brincar. Aqui não tenho esse poder de fogo todo.

Segue abaixo referência para um self-study:
http://www.oracle.com/pls/db112/to_URL? … LNPLS00809
http://download.oracle.com/docs/cd/E118 … m#BABFJEGJ

Quando der, informe sua versão do Oracle por gentileza, pois tem algumas particularidades que podem ser úteis (DBMS_ASSERT no 11g e por aí vai).

Segue abaixo também um excelente artigo sobre SQL injection:
http://www.oracle.com/technology/oramag … sktom.html

[]’s
Francisco.

[fsitjaParticipante]

Segue abaixo o exemplo de como fazer, mas sem querer dizer que deve ser feito isso. 😉


Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0
Connected as fsitja

SQL>
SQL> create user usuario identified by abc default tablespace users quota 10M on users;

User created

SQL> grant create session to usuario;

Grant succeeded

SQL> grant create table to usuario;

Grant succeeded

SQL> CREATE OR REPLACE PROCEDURE cria_tabela(v_parametro VARCHAR2) AUTHID CURRENT_USER IS
2 v_comando VARCHAR2(2000);
3 BEGIN
4 v_comando := 'create table usuario.cliente_fin_' || v_parametro ||
5 ' as select 1 cd_cliente, 1 cd_grupo from dual';
6 --' as select cd_cliente, cd_grupo from cliente_fin';
7 EXECUTE IMMEDIATE v_comando;
8 DBMS_OUTPUT.PUT_LINE('Criada tabela: ' || upper('usuario.cliente_fin_' || v_parametro));
9 END;
10 /

Procedure created

SQL> grant execute on cria_tabela to usuario;

Grant succeeded

SQL> conn usuario/abc@ORCL
Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0
Connected as usuario

SQL> set serveroutput on
SQL> exec fsitja.cria_tabela('MINHATAB')

Criada tabela: USUARIO.CLIENTE_FIN_MINHATAB

PL/SQL procedure successfully completed

SQL> select * from USUARIO.CLIENTE_FIN_MINHATAB;

CD_CLIENTE CD_GRUPO

---

     1          1

SQL> conn fsitja/xxxxxxxxx@ORCL as SYSDBA
Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0
Connected as SYS

SQL> drop user usuario cascade;

User dropped

SQL> drop procedure cria_tabela;

Procedure dropped

SQL>


[rwarstatParticipante]

Fsitja,
A questão é que os usuários não podem ter privilégio de criar ou dropar tabela nos outros schemas. O único usuário que pode fazer isso é onde estão os objetos do sistema. Por isso que havia pensado em fazer uma procedure que criasse uma tabela com um prefixo pré-determinado e o usuário informasse o complemento dela. Isso por que é um processo novo que ainda está sendo executado manualmente. Tão logo seja passado para o sistema, não terei mais necessidade de fazer essas gambiarras.

A solução encontrada foi dar o Grant de create any table para o usuário administrador, assim ele ficará responsável por criar as tabelas quando necessário. Quando não se fizer mais necessário, tiramos esse grant dele.

Muito obrigado pela ajuda e pelo material sugerido. Irei dar uma lida com calma neles depois.

Ah, o meu banco é um 10g R2 Enterprise.

Abraço,
Roberto

[PetersonParticipante]

Putz, tive uma aula com o Post do fsitja. Muito bacana! 😯

[rwarstatParticipante]

hehehe
Tu não foi o único Peterson. A explicação dele foi muito boa.
Me lembra as explicações do Chiappa na lista de discussão.

[burgaParticipante]

Fala Roberto,

O privilégio de create table/create any table que você deu ao owner da procedure foi via role ou você passou o privilégio diretamente ao usuário? Se tiver dado via role, tente dar direto ao usuário… Pois roles são desconsideradas em códigos em PL/SQL.

grant create table to usuario;
ou
grant create any table to usuario;

PS: Desconsidere minha primeira mensagem, falei besteira lá… rs 😆

[rwarstatParticipante]

Burga,

O owner da procedure é o mesmo schema onde será criada a tabela, ou seja, ele já tem esse grant. Para o usuário que irá executar a procedure, o grant foi dado diretamente para ele.

Abraço,
Roberto

[burgaParticipante]

[quote=”rwarstat”:2n4pj531]Burga,

O owner da procedure é o mesmo schema onde será criada a tabela, ou seja, ele já tem esse grant. Para o usuário que irá executar a procedure, o grant foi dado diretamente para ele.

Abraço,
Roberto[/quote]

Mesmo que o owner da procedure seja o mesmo esquema onde será criada a tabela, é necessário que se dê o privilégio diretamente, como eu disse, o privilégio de create table não irá funcionar se estiver sido dado através de uma role (ex.: RESOURCE). Experimente dar o grant e executar a procedure com o outro usuário…

Eu fiz o teste aqui no meu banco antes de postar e funcionou como esperado só depois que eu dei explicitamente o grant.

[Autor]

Posts