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

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;

Answer 2

If the view is accessed via a stored procedure, the execute grant is insufficient to access the view. You must grant select explicitly.

Answer 3

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 4

Q. When is the "with grant option" required ?

A. when you have a view executed from a third schema.

Example: schema DSDSW has a view called view_name

a) that view selects from a table in another schema  (FDR.balance)
b) a third shema  X_WORK  tries to select  from that view

Typical grants: grant select on dsdw.view_name to dsdw_select_role; grant dsdw_select_role to fdr;

But: fdr gets select count(*) from dsdw.view_name; ERROR at line 1: ORA-01031: insufficient privileges

issue the grant:

grant select on fdr.balance to dsdw with grant option;

now fdr: select count(*) from dsdw.view_name; 5 rows

Answer 5

Let me make a recap.

When you build a view containing object of different owners, those other owners have to grant "with grant option" to the owner of the view. So, the view owner can grant to other users or schemas....

Example: User_a is the owner of a table called mine_a User_b is the owner of a table called yours_b

Let's say user_b wants to create a view with a join of mine_a and yours_b

For the view to work fine, user_a has to give "grant select on mine_a to user_b with grant option"

Then user_b can grant select on that view to everybody.

Answer 6

Roberto Monterrey gives a perfect recap.

The answer was right and helped me a lot.

Thanks.