Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Please ask technical questions in the appropriate category. Thank you!

Schema Owner couldn't access own objects with schema prefix but works fine without schema.

user4985500Oct 12 2016 — edited Oct 12 2016

Hi All,

Today I found any DB object in my schema couldn't be accessed  from schema owner's session with the schema prefix  but works fine when schema prefix is removed or logged as a different user who has required grants.

This has been worked well before. So, I suspect some sort of security change might have caused this.

Here is a test case.

Schema owner = A

  • Log as A and create  a simple procedure TEST1_1().
  • Execute new procedure  BEGIN  A.TEST1_1() ; END; . This fails with the following error.

          ORA-06550: line 2, column 10:

          PLS-00302: component 'TEST1_1' must be declared

          ORA-06550: line 2, column 3:

  • Execute new procedure  BEGIN  TEST1_1() ; END; . works fine.

  Some experts says EXECUTE_CATALOG_ROLE should resolve the issue. but we had never used this before and everything worked as normally expected.

Hope you could tell me the solution.

Thank you very much,

This post has been answered by RogerT on Oct 12 2016
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 9 2016
Added on Oct 12 2016
19 comments
1,013 views