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?
To use a view, the user must have the appropriate privileges but only for the view itself, not its underlying objects. However, if access privileges for the underlying objects of the view are removed, then the user no longer has access. This behavior occurs because the security domain that is used when a user queries the view is that of the definer of the view. If the privileges on the underlying objects are revoked from the view's definer, then the view becomes invalid, and no one can use the view. Therefore, even if a user has been granted access to the view, the user may not be able to use the view if the definer's rights have been revoked from the view's underlying objects.
Oracle Documentation http://docs.oracle.com/cd/B28359_01/network.111/b28531/authorization.htm#DBSEG98017
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.
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
If the view is accessed via a stored procedure, the execute grant is insufficient to access the view. You must grant select explicitly.
If the view is accessed via a stored procedure, the execute grant is insufficient to access the view. You must grant select explicitly.
simply type this
grant all on to public;
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
you may also create view with schema name for example create or replace view schema_name.view_name as select..
Source: Stackoverflow.com