SQL Error: ORA-01031: insufficient privileges 01031. 00000 - "insufficient privileges"

Question

When I run the cross-schema update query I get the error message:

ORA-01031: insufficient privileges 01031. 00000 - "insufficient privileges".

Details are as follows:

I am trying to populate table (my_prod_price_tracking) in prod schema from the view (my_vw_product_price_info) which is there in CAT schema.

     my_prod_price_tracking (table)Prod Schema.
     my_vw_product_price_info (view)- CAT schema.

I have given grant select on my_vw_product_price_info to prod. So the inner Select query works fine. But when I run the update script I get the "ORA-01031: insufficient privileges" error.

Please let me know where am I going wrong.

Below is the query:

update ( 
       select a.price_range_low       current_lowest_price,
              a.price_range_low       lowest_price,
              a.last_price_range_low  last_lowest_price,
              a.price_range_low_update_time, 
       b.lowest_price new_lowest_price 
       from my_prod_price_tracking a,
            CATA.my_vw_product_price_info b
       where a.product_id = b.product_id and
             a.price_list_id = b.price_list_id and
             a.price_range_low <> b.lowest_price 
       )up
      set up.lowest_price = up.new_lowest_price,
          up.last_lowest_price = up.current_lowest_price,
          up.lowest_price_update_dt = sysdate

Isn't is kind of obvious from the error? You don't have update rights on the Prod.my_prod_price_tracking ? — Burhan Khalid
The error message is pretty self-explanatory. You don't have appropriate privileges on one or both databases. Talk to your DBA or someone with admin privileges. — Gordon Linoff
Gordan, Thanks for the quick response. We dont have DBA in our project and it needs to be solved myself. Here table "my_prod_price_tracking" is created in Prod schema and it as all CRUD privileges. My goal is to update the "my_prod_price_tracking" table from the view which is present in CAT schema. For the view I have granted Select to Prod schema. Inner select query works fine but the update query fails. I am running this query in prod schema. Here i am updating my_prod_price_tracking table which is present in prod schema and not the view. — user3903230
You might try GRANT SELECT, INSERT, UPDATE, DELETE ON MY_PROD_PRICE_TRACKING TO PUBLIC and GRANT SELECT, INSERT, UPDATE, DELETE ON MY_VW_PRODUCT_PRICE_INFO TO PUBLIC. You may need to log in as the owning user to do this. Best of luck. — Bob Jarvis - Reinstate Monica

Wayne Schroder Wayne Schroder · Accepted Answer · 2015-02-02T14:47:24

This may not be your issue, but worth noting: We just spent 48 hours chasing our tails trying to solve this one. One of our modules using NHibernate/Oracle started raising the following exception: ORA-01031: insufficient privileges. Without going into all the gory details, we verified it was random, sporadic, and not a permissions problem. What finally worked was running the following Oracle command:

alter system flush shared_pool

SQL Error: ORA-01031: insufficient privileges 01031. 00000 - "insufficient privileges"

1 Answers