I'm working with two schemas on the same database, SCHEMA_A and SCHEMA_B. I'm logged in as SCHEMA_A and can query SCHEMA_B.TABLE_NAME from the SQL worksheet in SQL Developer. I then tried to create a package in SCHEMA_A containing a procedure that queries SCHEMA_B.TABLE_NAME and get an error that SCHEMA_B.TABLE_NAME doesn't exist. Why would I be able to query it as SCHEMA_A but not create a package in SCHEMA_A with a procedure that queries it?

CodePudding user response：

What you described sounds like this:

• in order to be able to access table owned by another user, you have to get privileges do to so
• these privileges can be granted
  • directly from SCHEMA_B to SCHEMA_A, or
  • via role: first SCHEMA_B granted access on TABLE_NAME to that role, and then role was granted to SCHEMA_A
• doing so, you are allowed to access SCHEMA_B.TABLE_NAME from SQL layer or anonymous PL/SQL blocks, but not from named PL/SQL procedures (i.e. stored procedures, functions, packages, triggers)
• it means that SCHEMA_B has to directly grant privileges to SCHEMA_A, not via role

Is that your case?