"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 "

Is the table in a different schema - you haven't shown a schema prefix but I'm guessing it is - and your select privileges on it are granted via a role, rather than directly? — Alex Poole
here is what you are looking : stackoverflow.com/questions/20595701/… — Milaci
@AlexPoole Are you suggesting that role-based privileges are disabled selecting from a view the way they are disabled in procedures? I don't think I've ever read that. But you are probably right about the view being in another schema. The owner of the view needs to GRANT SELECT ON thisview TO anthonylynch WITH GRANT OPTION. — Matthew McPeak
@MatthewMcPeak - the title is a little confusing but the question body says the error is when compiling/defining (i.e. creating), not when then querying it. Clarification would be helpful though. — Alex Poole

Alex Poole Alex Poole · Accepted Answer · 2018-08-22T15:22:07

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.

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

1 Answers