"ORA-01031: insufficient privileges " when selecting view but not underlying table

Question

I have a view that is defined as this:

SELECT 
        pi.Role, 
        pi.created_date, 
        pi.last_upd_date,
        pi.person_id
    FROM 
        other_schema.table_a pi      
    WHERE 
        ...;

But when I compile it I get an error: "ORA-01031: insufficient privileges"

If I try to run the sql that i am using to define the view (outside of creating the view) it executes correctly. Am I missing a permission to execute this as a view?

EDIT for Clarity:

CREATE VIEW VIEW_1 AS
SELECT 
        pi.Role, 
        pi.created_date, 
        pi.last_upd_date,
        pi.person_id
    FROM 
        other_schema.table_a pi

returns: View "VIEW_1 " created.

SELECT * FROM VIEW_1

returns: ORA-04063: view "VIEW_1" has errors

SELECT 
        pi.Role, 
        pi.created_date, 
        pi.last_upd_date,
        pi.person_id
    FROM 
        other_schema.table_a pi

returns: The data from table_a

When looking at the errors in the view I see: "ORA-01031: insufficient privileges "

Answer 1

Assuming you have been granted the create view privilege to allow you to create a view in your own schema, so that this works:

create or replace view view_1 as
select * from dual;

View VIEW_1 created.

select * from view_1;

D
-
X

... then this looks like an issue with how the privileges on the table in another schema were granted to the user. As a demo, as user_1:

create table table_a (person_id number);
grant select on table_a to some_role;
insert into table_a (person_id) values (42);
commit;

Then as user_2:

select * from session_roles;

ROLE                          
------------------------------
SOME_ROLE
...

select * from user_1.table_a;

 PERSON_ID
----------
        42

I can see the table, via the privileges granted to the role I have. But if I try to create a view:

create or replace view view_1 as
select * from user_1.table_a;

ORA-01031: insufficient privileges

or to match what you seem to actually be doing, though exactly how the compilation is reported depends on which client you are using:

create or replace force view view_1 as
select * from user_1.table_a;

Warning: View created with compilation errors.

select * from view_1;

SQL Error: ORA-04063: view "USER_2.VIEW_1" has errors

show errors view view_1;

LINE/COL ERROR
-------- ------------------------------------------------
0/0      ORA-01031: insufficient privileges

The select privilege has to be granted directly to the user creating the view; as user_1 again:

grant select on table_a to user_2;

then as user_2:

create or replace force view view_1 as
select * from user_1.table_a;

View VIEW_1 created.

select * from view_1;

 PERSON_ID
----------
        42

or if previously created with force it should just recompile automatically and work when you query it again, without having to explicitly recreate or recompile it.

---

There is a further wrinkle, which may or may not matter for your scenario. At this point I can't let other users see the view:

grant select on view_1 to user_3;

ORA-01720: grant option does not exist for USER_1.TABLE_A'

To be able to do that I have to have the ability to extend visibility of the underlying table to other users. I don't really want to do that, but it is effectively what I am doing - at least, for the data rather than the actual tables. To allow that to happen, user_1 has to do:

grant select on table_a to user_2 with grant option;

and then as user_2 I can now do:

grant select on view_1 to user_3;

Grant succeeded.

Now user_3 can query the view; but cannot query the underlying tables directly.