Como contornar a necessidade de WITH GRANT OPTION em views que acessam tabelas de outros usuários

This post is also available in: English

Há um bom tempo a Oracle introduziu limitações em VIEWS para evitar que algum usuário (com acesso à um determinado objeto no banco de dados) repassasse seu conteúdo para outro, encapsulando-o em views.

Como é sempre melhor mostrar exemplos do que escrever teoria, digamos então que temos 3 usuários no banco de dados ( USER_A, USER_B, USER_C ).

A tabela T1 pertence ao USER_A.

1. USER_A: GRANT SELECT ON T1 to USER_B (sem GRANT OPTION)
2. USER_B: CREATE VIEW V1 AS SELECT * FROM USER_A.T1
3. USER_B: GRANT SELECT ON V1 TO USER_C
4. USER_C: Executa um SELECT * FROM USER_B.V1

Vamos criar um demo e verificar o que acontece ...

Criando usuários e permissões:

SQL> conn / as sysdba
Connected.
SQL> create user USER_A identified by oracle quota unlimited on usertbs;

User created.

SQL> grant create session, create table to USER_A;

Grant succeeded.

SQL> create user USER_B identified by oracle;

User created.

SQL> grant create session, create view to USER_B;

Grant succeeded.

SQL> create user USER_C identified by oracle;

User created.

SQL> grant create session to USER_C;

Grant succeeded.

Criando objetos e concedendo os acessos..

SQL> conn USER_A/oracle
Connected.
SQL> create table USER_A.t1 tablespace usertbs as select * from all_objects;

Table created.

SQL> select count(*) from USER_A.t1;

  COUNT(*)
----------
     74052

SQL> grant select on USER_A.t1 to USER_B;

Grant succeeded.

SQL> conn USER_B/oracle
Connected.
SQL> create view USER_B.v1 as select * from USER_A.t1;

View created.

SQL> select count(*) from USER_B.v1;

  COUNT(*)
----------
     74052

SQL> grant select on USER_B.v1 to USER_C;
grant select on USER_B.v1 to USER_C
                      *
ERROR at line 1:
ORA-01720: grant option does not exist for 'USER_A.T1'

O erro ORA-01720 aparece, exigindo que o USER_B tenha GRANT OPTION na tabela T1 do USER_A para que possa conceder grant em sua própria view para o USER_C.

Portanto, USER_C não terá acesso ao objeto USER_A. Justo! Faz parte da segurança que tanto precisamos.

Essa limitação já é bem conhecida e documentada:

NOTE:

To grant SELECT on a view to another user, either you must own all of the objects underlying the view or you must have been granted the SELECT object privilege WITH GRANT OPTION on all of those underlying objects. This is true even if the grantee already has SELECT privileges on those underlying objects

No entanto, o que poucas pessoas sabem é que essa proteção pode ser facilmente contornada usando algo chamado PIPELINED FUNCTIONS.

Como contornar então a exigência de GRANT OPTION?

Se o usuário USER_B, proprietário da view, também tivesse o privilégio CREATE PROCEDURE, ele poderia contornar o erro ORA-01720 encapsulando os resultados da tabela T1 em uma função com pipeline, e usar essa função dentro de sua view. Vamos ver como isso funcionaria:

Primeiro, vamos conceder CREATE PROCEDURE para o USER_B:

SQL> conn / as sysdba
Connected. 
SQL> grant create procedure to USER_B;

Grant succeeded.

Chegou a hora de criar a função com pipeline:

SQL> conn USER_B/oracle
Connected.
SQL> CREATE OR REPLACE PACKAGE pkg_bypass_go AS
  2  
  3    TYPE t_tab IS TABLE OF USER_A.T1%ROWTYPE;
  4  
  5    FUNCTION run (p_sql  IN  CLOB)
  6      RETURN t_tab PIPELINED;
  7  
  8  END pkg_bypass_go;
  9  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY pkg_bypass_go AS
  2  
  3    FUNCTION run (p_sql  IN  CLOB)
  4      RETURN t_tab PIPELINED
  5    IS
  6      l_cursor SYS_REFCURSOR;
  7      l_row    USER_A.T1%ROWTYPE;
  8    BEGIN
  9      OPEN l_cursor FOR p_sql;
 10      LOOP
 11        FETCH l_cursor
 12        INTO  l_row;
 13        EXIT WHEN l_cursor%NOTFOUND;
 14        PIPE ROW (l_row);
 15      END LOOP;
 16      CLOSE l_cursor;
 17      RETURN;
 18    END run;
 19  
 20  END pkg_bypass_go;
 21  /

Package body created.

E agora, criando a view e concedendo permissão de leitura ao USER_C:

SQL> conn USER_B/oracle
SQL> create or replace view USER_B.v1 as select * from table(pkg_bypass_go.run('select * from USER_A.t1'));

View created.

SQL> select count(*) from USER_B.v1;

  COUNT(*)
----------
     74052

SQL> grant select on USER_B.v1 to USER_C;

Grant succeeded.

SQL> conn USER_C/oracle
Connected.
SQL> select count(*) from USER_B.v1;

  COUNT(*)
----------
     74052

Perfeito. O USER_B pôde dar acesso ao USER_Cna tabela do USER_A sem qualquer GRANT OPTION.

A view está agora executando um select * from table(pkg_bypass_go.run('select * from USER_A.t1')).

Observe que a tabela agora está sendo pré-processada por uma função com pipeline para cada linha, por isso não espere nem de perto que o SELECT execute com a mesma performance que um acesso direto à tabela. Portanto, use-o apenas para pequenas tabelas ou consultas que não precisem de desempenho máximo. Também recomendo melhorar o código da PACKAGE, implementando alguns bulk collections e ajustes de pipeline, caso você precise melhorar o desempenho dessa abordagem.

+6