"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, Aug 22 '18 at 15:14
here is what you are looking : https://stackoverflow.com/questions/20595701/grant-create-view-on-oracle-11g — Milaci, Aug 22 '18 at 15:18
@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, Aug 22 '18 at 15:20
Possible duplicate of [grant create view on Oracle 11g](https://stackoverflow.com/questions/20595701/grant-create-view-on-oracle-11g) — Milaci, Aug 22 '18 at 15:22
@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, Aug 22 '18 at 15:23
Although I may be overthinking it (for a change) - if the table and view are in the schema then you may just not have the `create view` privilege, and then it will be a duplicate as Milaci suggested. — Alex Poole, Aug 22 '18 at 15:32
@AlexPoole Sorry for the lack of clarity. I updated it to show what is returned by each of my queries. — Anthony Lynch, Aug 22 '18 at 15:48
@AnthonyLynch - the table is in another schema so my answer may apply; but can you check if you have the `create view` privilege anyway? Can you create a simple view like `create view view_1 as select * from dual`? (You are presumably including the 'force' keyword in your real statement, too, given the way the errors are reported; and may be doing `create or replace force ...`). — Alex Poole, Aug 22 '18 at 15:57

Alex Poole · Accepted Answer · 2018-08-22T16:15:47.933

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.

Unfortunately, this is not the issue. I clarified it after you posted but I do not get this ORA-01031: insufficient privileges when creating the view. I am able to create it. I am just not able to compile, or select from it (since the view won't compile.) — Anthony Lynch, Aug 22 '18 at 15:58
It is the same underlying issue (if you have `create view` privs anyway) - you just appear to just be doing `create force view view_1 force ...` which creates the view object as invalid; my demo doesn't force it so it isn't created and the error is reported immediately. — Alex Poole, Aug 22 '18 at 16:00
I copy/pasted the query structure as it executed, there is no force in the query. — Anthony Lynch, Aug 22 '18 at 16:45
@AnthonyLynch - then your client is adding it, I think. I've got a vague memory of reading that Toad does that, but I might be wrong... It doesn't really matter though. Can you create the simple view against dual? If so does granting the select privilege directly to the user fix your problem? — Alex Poole, Aug 22 '18 at 16:49

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

1 Answers1