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

As vezes precisamos fazer um export/import e levar os usuários e senha (originais). Para isso, há um pequeno truque usando o arquivo de import, veja exemplo:

ops$marcio@YODA10G> !exp / file=users.dmp full=y

Export: Release 10.2.0.2.0 - Production on Fri Aug 18 20:04:48 2006

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)

About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
. exporting resource costs
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting directory aliases
. exporting context namespaces
. exporting foreign function library names
. exporting PUBLIC type synonyms           <<<<<-------- FIZ UM CTRL-c AQUI.
EXP-00008: ORACLE error 1013 encountered ORA-01013:
user requested cancel of current operation
EXP-00000: Export terminated unsuccessfully 

ops$marcio@YODA10G> !strings users.dmp | grep -i ops$marcio
CREATE USER "OPS$MARCIO" IDENTIFIED BY VALUES '780383AD56F94137' DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP"
GRANT UNLIMITED TABLESPACE TO "OPS$MARCIO"
GRANT "DBA" TO "OPS$MARCIO"
ALTER USER "OPS$MARCIO" DEFAULT ROLE ALL

Labels: how to