DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: When selecting from a view ORA-01031: insufficient privileges

  1. #1
    Join Date
    Feb 2007

    When selecting from a view ORA-01031: insufficient privileges

    As DBA I have granted select privileges on a view to a user, but whenever I login as that user and try to select from the view I get the message 'ORA-01031'. When I run the query in the view as the user I do not have problem and can see the results. This view joins tables from more than one schema and does a distinct on the resultset. The user has select permission on the view and a default role that has reference on all the objects on the other schemas. Schema owner of the view has SELECT privileges on all the tables from other schemas referenced in the view.

  2. #2
    Join Date
    Nov 2000
    Pittsburgh, PA
    You may need to do a direct grant instead of going through a role.

  3. #3
    Join Date
    Feb 2007
    I finally got it to work

    This is how I had done initially
    Created all the objects as sysdba although in diffrent schemas.
    I then gave permission on tables in schema in A to B, logged in as sysdba
    Then as sysdba gave permission on view in schema B to C
    But this did not work

    This is what I did today
    After all the objects were created I logged in as user A
    As user A I granted SELECT privilege with grant option to B on the tables used in the view
    After this I logged in as user B and granted SELECT privilege to user C
    Then I logged in as user C and did a SELECT on the view and it worked fine.

    I guess 'with grant option' here is very important when giving a SELECT privilege on the underlying table for a view

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Click Here to Expand Forum to Full Width