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?
Best Answer
You can use
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.