How to bypass requirement of WITH GRANT OPTION on views accessing third-party tables

This post is also available in: Português

A long time back, Oracle introduced limitations on VIEWS to avoid that some user with access to a given object in the database to pass on its contents to another user, by encapsulating it on views.

As it's always better to show examples than writing theory, let's say we have 3 users on the database (USER_A, USER_B, USER_C).

Table T1 belongs to USER_A.

1. USER_A: GRANT SELECT ON T1 to USER_B (without 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: Performs SELECT * FROM USER_B.V1

Let's create a demo and check what happens...

Creating users and permissions:

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.

Creating objects and granting..

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'

The ORA-01720 error is raised, requiring USER_B to have GRANT OPTION on USER_A table T1 in order to grant his own view to USER_C.

So USER_C will not have access at all to USER_A object. Fair enough! It's part of the security we so badly need.

This limitation is well know and documented:

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

However, what a few people know is that this protection can be easily bypassed by using something called PIPELINED FUNCTIONS.

So how to bypass the Grant Option requirement?

If the USER_B account, who owns the view, also had the CREATE PROCEDURE privilege, he could bypass the ORA-01720 error by encapsulating the table T1 results on a pipelined function, and use this function inside his view. Let's see how this would work:

First, let's grant CREATE PROCEDURE to USER_B:

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

Grant succeeded.

Now is time to create the pipelined function:

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.

And now creating the view and granting to 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

Perfect. USER_B could give access to USER_C on USER_A table without any GRANT OPTION.

The view now is running a select * from table(pkg_bypass_go.run('select * from USER_A.t1')).

Please note the table now is being pre-processed by a pipelined function for each row, so don't expect the SELECT on it to perform even closer as a direct table access. So use it only for small tables or queries that don't need max performance. I do also recommend to improve the PACKAGE code, implementing some bulk collections and pipeline tunings in case you need to improve the performance of this approach.

+6