0

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?

Improve this question
1
  • please post the exact error message (including the ORA- errorcode)
    – miracle173
    Sep 29, 2016 at 16:35

1 Answer 1

Reset to default
0

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.

Improve this answer
1
  • 1
    I have fixed it!. USER_02 could not view the VIEW_01 back then. I missed out the part that I need to use SCHEMA_09 to grant with option to SCHEMA_01's role too. Thanks for confirming the grant with option was the fix!
    – Ren Fa Kay
    Oct 2, 2016 at 14:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.