SQL> create procedure get_cursor ( p_schema varchar2,
   2                                 result out sys_refcursor ) is
   3   begin
   4     open result for
   5       select object_type, count(*) cnt
   6       from dba_objects
   7       where owner = p_schema
   8       group by object_type ;
   9   end ;
  10   /

SQL> exec get_cursor ( 'SYSTEM', :result ) ;

PL/SQL procedure successfully completed.

OBJECT_TYPE                CNT
------------------- ----------
FUNCTION                      1 
INDEX                       171 
INDEX PARTITION              31 
LOB                          24 
PACKAGE                       1 
PACKAGE BODY                  1 
PROCEDURE                     1 
QUEUE                         4 
SEQUENCE                     20 
SYNONYM                       8 
TABLE                       137 
TABLE PARTITION              27 
TRIGGER                       2 
TYPE                          1 
VIEW                         10 

 15  rows selected.

SQL> conn sys@svintus as sysdba

Connected.

SQL> create user t_app identified by t_app default tablespace users ;

User created.

SQL> create user t_user identified by t_user ;

User created.

SQL> grant connect to t_user ;

Grant succeeded.

SQL> grant connect, resource, unlimited tablespace to t_app ;

Grant succeeded.

SQL> conn t_app/t_app@svintus

Connected.

SQL> create table my_objects as
   2     select object_id, object_name, object_type from user_objects 
   3     where rownum <  1  ;

Table created.

SQL> create procedure get_objects ( result out sys_refcursor,
   2                                  filter varchar2 default null ) as
   3   begin
   4     open result for
   5       'select * from my_objects where rownum <= 5 ' ||
   6       case when filter is null then null else 'and ' || filter end ;
   7   end ;
   8   /

Procedure created.

SQL> create view my_objects_view as select * from my_objects ;

View created.

SQL> insert into my_objects
   2     select object_id, object_name, object_type from user_objects ;

 3  rows created.

SQL> grant execute on get_objects to t_user ;

Grant succeeded.

SQL> conn t_user/t_user@svintus

Connected.

SQL> exec t_app.get_objects ( :result ) ;

PL/SQL procedure successfully completed.

 OBJECT_ID OBJECT_NAME                    OBJECT_TYPE                           
---------- ------------------------------ -------------------                   
      68451  MY_OBJECTS_VIEW                VIEW                                  
      68448  GET_OBJECTS                    PROCEDURE                             
      68449  MY_OBJECTS                     TABLE                                 

SQL> exec t_app.get_objects ( :result, 'object_type = ''VIEW''' ) ;

PL/SQL procedure successfully completed.

 OBJECT_ID OBJECT_NAME                    OBJECT_TYPE                           
---------- ------------------------------ -------------------                   
      68451  MY_OBJECTS_VIEW                VIEW                                  

SQL> select * from t_app.my_objects ;
select * from t_app.my_objects 
                    *
ERROR at line  1 :
ORA- 00942 : table or view does not exist 

-- пользователь t_user через процедуру читает таблицу t_app.my_objects

SQL> exec t_app.get_objects ( :result, 'object_type = ''VIEW''' ) ;

PL/SQL procedure successfully completed.

 OBJECT_ID OBJECT_NAME                    OBJECT_TYPE                           
---------- ------------------------------ -------------------                   
      68451  MY_OBJECTS_VIEW                VIEW                                  

-- пользователь t_user обламывается на попытке читать таблицу напрямую

SQL> select * from t_app.my_objects ;
select * from t_app.my_objects 
                    *
ERROR at line  1 :
ORA- 00942 : table or view does not exist