Insufficient Privileges on selecting Views

oracleoracle-12cpermissions

I am using Oracle 12.

Given 2 schemas, SCHEMA_01 and SCHEMA_09. I have a View_01 owns by SCHEMA_01 but it is made up of SCHEMA_01 and SCHEMA_09 tables.

Then I have a User_02 that could select both tables in SCHEMA_01 and SCHEMA_09. But I am prompted with insufficient privileges when selecting the VIEW_01.

Before that I have tried granting all SCHEMA_09 tables with grant option to SCHEMA_01. This error is also prompted when I tried granting select for view USER_02's role

grant option does not exist for SCHEMA_09.TABLE_01

Anything that I am missing or maybe somewhere I can check?

Best Answer

Does USER_02 have permission to see VIEW_01? The grants on the view have nothing to do with the grants on the base tables. Being granted SELECT on the tables is not sufficient to have SELECT access to the view.

On your last paragraph, it's true that SCHEMA_01 needs to have the SCHEMA_09 tables granted to create a view against them, including the WITH GRANT OPTION so that SCHEMA_01 can be allowed to provide access to the SCHEMA_09 tables to other users through the VIEW_01 view. However, it sounds like you already have this if the view was created without errors.

Related Question