Explicit usernames in references to objects Much of our code -- both in external programs and in stored procedures -- uses explicit usernames, when referring to various DB-objects:
SELECT * FROM prod.object

or
prod.StoredProcedure(.....)

This causes obvious problem, when a copy of the database is loaded into a schema with a different name -- the same object becomes qa.object now...
I proposed, we drop such explicit references completely, referring simply to the object and StoredProcedure instead.
However, this would cause problems when another account (such as prod_ro) logs in and tries to execute prod's stored procedures or browse prod's tables.
How do people handle such situations?
You can use
alter session set current_schema=qa;

Then, all references to objects that aren’t qualified with a schema will be treated as if you wrote qa.object. You will still need the necessary privileges to work with the relevant object.