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

User · Answer

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

User · Answer

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

User · Answer

you may also create view with schema name for example  create or replace view schema name view name as select

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[oracle] ORA-01031: insufficient privileges when selecting view

Examples related to oracle

Examples related to view