0

We are running Oracle database 19c and we have got a strange error for ORA-01031: insufficient privileges

There are two users/schemas, for short, I call them mgr and user here.

So on mgr, I have created this view:

  CREATE OR REPLACE FORCE EDITIONABLE VIEW "MGR"."EVENT" (...) AS 
  SELECT  ... FROM c_interfaces.c_event et
     LEFT JOIN event_module em ON (...)
     LEFT JOIN event_location cl ON (...)
     LEFT JOIN users u ON (...)
     WHERE ... ORDER BY ...;

  GRANT SELECT ON "MGR"."EVENT" TO "USER" WITH GRANT OPTION;

Using mgr, I can SELECT this view without problem.

Then when I used user to call:

select * from "MGR"."EVENT";

I got this error:

ORA-01031: insufficient privileges
01031. 00000 -  "insufficient privileges"
*Cause:    An attempt was made to perform a database operation without
           the necessary privileges.
*Action:   Ask your database administrator or designated security
           administrator to grant you the necessary privileges

I don't know what went wrong. When I used user for debugging and I tried to cut out the select part and run:

  SELECT  ... FROM c_interfaces.c_event et
     LEFT JOIN event_module em ON (...)
     LEFT JOIN event_location cl ON (...)
     LEFT JOIN users u ON (...)
     WHERE ... ORDER BY ...;

I got no error at all. Data is displayed correctly on my screen.

So what could be wrong?

user2526586
  • 972
  • 2
  • 12
  • 27
  • I would guess the view is invalid because mgr no longer has all the grants needed to select from all of the underlying tables. – kfinity Jul 18 '22 at 20:31
  • @kfinity I have just tested, I can do `select * from event;` with `mgr` without any problem. – user2526586 Jul 19 '22 at 07:41

0 Answers0