ORA-01031: insufficient privileges for Oracle view

665 Views Asked by At

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?

0

There are 0 best solutions below