how to resolve potential permission problem in oracle

Question

I've got a synonym pointing to a table:

-- Create the synonym 
create or replace synonym my_schema.COLORS_ALL
  for OTHER_SCHEMA.MV_CAR_COLORS;

problem is that I can query this synonym fine:

select * from my_schema.COLORS_ALL;

however, when I try to use this synonym in a Stored Proc I get an error that table or view does not exist.

what could causing this issue?? Recently the target table (MV) MV_CAR_COLORS was refreshed and indices on it were refreshed as well.

Answer 1

You need direct grants (not through a role) on the table to the owner of the stored procedure. If by "refreshed" you mean the table was dropped and recreated, you probably didn't re-grant this direct grant as part of the refresh process.