Blog do Marcio Portes

Atualmente estou trabalhando juntamento com os desenvolvedores para minimizar problemas com auditoria e em recente reunião, onde discutíamos sobre grants, esclareci que: quando houver necessidade de criação de procedure/package, o grant deve ser direto ao usuário. Pois bem, isto está documentado no manual.

A seguir parte da explicação do manual.

"For the compilation of the procedure or package, the owner of the procedure or package must have been explicitly granted the necessary object privileges for all objects referenced within the body of the code. The owner cannot have obtained required privileges through roles."

Traduzindo rapidamente: Para a compilação, o dono da procedure ou package DEVE TER RECEBIDO EXPLICITAMENTE PERMISSÃO necessária dos privilégios em todos os objetos referenciados dentro do corpo do código. (Essa parte eu gosto!) O dono NÃO PODE ter obtido os privilégios requeridos através de roles.

Portanto, estou completamente embasado em minha informação... Isso era o que eu imaginava. Hoje descobri que através de DB Link, a afirmação acima não é verdadeira.

Fiz um pequeno estudo. Criei uma tabela "t" em meu esquema (ops$marcio), uma role "r_test", dei grant de select na tabela "t" à role "r_test", criei um usuário "a", dei permissão de conexão, criar código, sinônimo, dblink e a recém criada "r_test". E fiz o teste: tentei primeiro criar uma procedure com o sinônimo, não funcionou - comportamento esperado e documento, porém, quando eu criei um dblink de "a" para "a" mesmo e referenciei a tabela "t" através do dblink, a procedure compilou e rodou sem problema.

Veja o teste todo, lembrando que o mesmo foi executado na versão 10.2.0.2.0 (Linux).

ops$marcio:YODA10G> create table t as
  2  select username
  3    from all_users
  4   where rownum <= 10;

Table created.

ops$marcio:YODA10G>
ops$marcio:YODA10G> create role r_test;

Role created.

ops$marcio:YODA10G>
ops$marcio:YODA10G> grant select on t to r_test;

Grant succeeded.

ops$marcio:YODA10G>
ops$marcio:YODA10G> grant create session,
  2        create synonym,
  3        create procedure,
  4        create database link,
  5        r_test
  6     to a identified by a;

Grant succeeded.

ops$marcio:YODA10G>
ops$marcio:YODA10G> connect a/a
Connected.
a:YODA10G> show user
USER is "A"
a:YODA10G> create synonym t for ops$marcio.t;

Synonym created.

a:YODA10G>
a:YODA10G> create database link a_link
  2    connect to a identified by a
  3    using 'yoda10g';

Database link created.

a:YODA10G>
a:YODA10G> create or replace procedure read_t as
  2  begin
  3    for x in ( select username
  4                 from t )
  5    loop
  6       dbms_output.put_line('User: '|| x.username );
  7    end loop;
  8  end;
  9  /

Warning: Procedure created with compilation errors.

a:YODA10G>
a:YODA10G> create or replace procedure read_t as
  2  begin
  3    for x in ( select username
  4                 from t@a_link )
  5    loop
  6       dbms_output.put_line('User: '|| x.username );
  7    end loop;
  8  end;
  9  /

Procedure created.

a:YODA10G>
a:YODA10G> exec read_t
User: UTIL
User: SCH_IDBA
User: SCH_MARCIO
User: FLOWS_020200
User: APEX_PUBLIC_USER
User: FLOWS_FILES
User: OPS$MARCIO
User: SCOTT
User: MGMT_VIEW
User: MDDATA

PL/SQL procedure successfully completed.

a:YODA10G>

Intrigado, submeti uma dúvida na asktom e abri um chamado na Oracle, estou aguardando resposta. Quando houver novidades, posto aqui o resultado!

Adendo: Resposta da Oracle

ANSWER
=======
Hi Marcio,

This is not a bug, it's expected behavior. Please refer to the following bugs closed as not a bug:

Bug 3408559 - Abstract: CREATE VIEW ON OTHER SCHEMA IS POSSIBLE WITHOUT PRIVILEGES BY USING DBLINK
Bug 4460766 - Abstract: ABLE TO CREATE STORED PROCEDURE WHEN ACCESSING OBJECTS VIA ROLE THROUGH DB
LINK


Roles don't work from PL/SQL because roles exist in sessions only, therefore, in general they don't
work from a stored procedure, but with a dblink, you create a session. The dblink is making a new
connection, so the rules of PL/SQL in the local database don't apply.
When dblink is used, only SELECT part of the procedure is sent to remote database. Here, Oracle
doesn't know wheather this SELECT is part of procedure or view etc. It only applies privilege
required for SELECT and since user "a" is granted SELECT on table via role, SELECT completes and
procedure is created.

Labels: Security