Hi,

I have 2 schemas A and B.
Schema A has a some partitioned tables and schema B has been granted SELECT/UPDATE/INSERT privileges to tables in schema A.
I use synonyms in schema B for schema A tables.

When I execute the following SQL statement in Schema B:

select table_name, partition_name from sys.all_tab_partitions;

I get a list of partitions and tables that are owned by schema A.

Then create the following stored procedure in schema B:

create or replace procedure PARTITION_TEST 
IS
      cursor C_partitions is
        select table_name, partition_name from sys.all_tab_partitions;
      partitionList C_partitions%rowtype;
BEGIN
      open C_partitions;
      loop
         fetch C_partitions
            into partitionList;
         exit when C_partitions%notfound;
         DBMS_OUTPUT.put_line('Table name: ' || partitionList.TABLE_NAME || ' partition name: ' || partitionList.PARTITION_NAME);
      end loop;
      close C_partitions;
END;

When I try to execute this stored procedure as schema B, I no longer get a list of schema A's partitions.

Unfortunately, there is a requirement that I have to run the procedure with schema B, so I cannot use invoker rights (and run it with user A).
Is there a way to get a list of schema A's partitions, when running the procedure with schema B?

Thanks,
Kristian

##UPDATE:
I found that this issue exists, when the table privileges are granted to a role and not directly to the user.
Here is an example:

SQL> conn chris/chris@db Connected. 
SQL> 
SQL> create table t (x integer) partition by range (x) interval (10) ( partition p0 values less than (10) ); 
Table created. 
SQL> insert into t values (10); 
1 row created. 
SQL> insert into t values (20); 
1 row created. 
SQL> commit; 
Commit complete. 
SQL> 
SQL> create user app_user identified by "app_user"; 
User created. 
SQL> grant create session, create procedure to app_user; 
Grant succeeded. 
SQL> create role app_role;
role APP_ROLE created.
SQL> grant app_role to app_user;
Grant succeeded. 
SQL> grant select, update, insert on t to app_role;
Grant succeeded. 
SQL> 
SQL> conn app_user/app_user@db 
Connected. 
SQL> 
SQL> select * from chris.t; 
X ---------- 10 20 
SQL> 
SQL> select table_name, partition_name from all_tab_partitions 2 where table_owner = 'CHRIS'; 
TABLE_NAME PARTITION_NAME ------------------------------ ------------------------------ T P0 T SYS_P9025 T SYS_P9026 
SQL> 
SQL> create or replace procedure PARTITION_TEST
  2  IS
  3        cursor C_partitions is
  4          select table_name, partition_name from all_tab_partitions;
  5        partitionList C_partitions%rowtype;
  6  BEGIN
  7        open C_partitions;
  8        loop
  9           fetch C_partitions
 10              into partitionList;
 11           exit when C_partitions%notfound;
 12          DBMS_OUTPUT.put_line('Table name: ' || partitionList.TABLE_NAME || ' partition name: ' || partitionList.PARTITION_NAME);
 13        end loop;
 14        close C_partitions;
 15  END;
Procedure created. 
SQL> 
SQL> exec PARTITION_TEST; 

PL/SQL procedure successfully completed.

SQL>