Suppose I have 2 schemas/users on a single database (s1 and s2) and s1 executes procedure prc1 in s2 which updates the table t1. Also imagine both s1 and s2 have the table t1 (same structure). Which table will be updated?
My vote is for table t1 in schema s2.
I would like to know what are the privileges that control this condition? i.e., what are the privileges to be given for executing the procedure and updating the tables in another schema?
A procedure sets its dependancies at compile time, not run time, so once a procedure is compiled, that doesn't change.
There is also an order of dependancy checking. Oracle first looks in the current schema for an object, then any private synonyms and finally any public synonyms.
So, if you compile a procedure and you have a table of name 'x' in your schema, it doesn't matter if anyone else has a table of that name. You procedure will always use the same 'x' table.
If you compile a procedure and you do not have a table named 'x' in your schema, Oracle will look to see if you have any private synonyms named 'x' and finally any public synonyms named 'x.' If it finds one it will always use that schema's 'x' even if you create a table in your schema afterwards.
The safest ways to ensure which schema's table you are using is to either prepend the schema owner to the table name, i.e. s2.t1, or to use "alter session set current_schema = s2;"
To use a table in another schema, you would need at least select permission on that table. To create a procedure using a table in another schema, you would need select granted directly to your user name and not to a role.
Dig Deeper on Oracle database design and architecture
Related Q&A from Lewis Cunningham
I need to somehow log the execution time for two procedures every time they are called and save that data (in a table?). Do you know of a way to ... Continue Reading
How can I tell if a column is a NUMBER or VARCHAR in PL/SQL? Is there a function to do this? Continue Reading
I need a step-by-step procedure for importing text to an Oracle database using a stored procedure. Continue Reading