ORA-01031: insufficient privileges when selecting view

Question

When I try to execute a view that includes tables from different schemas an ORA-001031 Insufficient privileges is thrown. These tables have execute permission for the schema where the view was created. If I execute the view's SQL Statement it works. What am I missing?

Answer 1

Finally I got it to work. Steve's answer is right but not for all cases. It fails when that view is being executed from a third schema. For that to work you have to add the grant option:

GRANT SELECT ON [TABLE_NAME] TO [READ_USERNAME] WITH GRANT OPTION; 

That way, [READ_USERNAME] can also grant select privilege over the view to another schema

Answer 2

As the table owner you need to grant SELECT access on the underlying tables to the user you are running the SELECT statement as.

grant SELECT on TABLE_NAME to READ_USERNAME;