Configurable database schema

[Brian G.]

Is there a reasonable way to set up an application such that the database
(specifically Oracle) schema owner used by all of the queries is configurable?
For example, in SQL*Plus I can log in as the REPORTER user, execute "ALTER
SESSION SET CURRENT_SCHEMA = APPOWNER_DEV", then all queries that I run from
that point will use the database object naming resolution as if I were the
APPOWNER_DEV user (meaning I can omit schema names entirely) but use the
permissions of the REPORTER user. Later when I need to run the script in the
production environment I set current schema to APPOWNER_PROD and no other SQL
statements need to be changed.

I was hoping there was some way to set up the DSN such that the "current
schema" was set as desired each time a new database session was opened.

Other options that are being considered:
1. Hardcode the schema name and do a project-wide search-and-replace with the
schema names if the target environment uses a different schema.
2. Log in directly to the database as the application owner.
3. Create scripts to create/recreate synonyms (private or public) to address
the "hiding" of the schema information.
4. Set up all SQL scripts such that the schema name is a variable.

All of these options have some downside. Of course I'm open to other options.
The goal is to make the move between environments require the fewest changes
possible.

Thanks,
Brian Gastineau

[Brian G.]

On re-read, option 4 should have been:

4. Set up all CF templates such that the schema name is a variable in the SQL statement.

[paross1]

We took a different approach, for security and modularity reasons, that may be
an alternative for you to consider. We put all of our SQL into PL/SQL stored
procedures within packages, rather than writing the queries directly within
ColdFusion. We use CFSTOREDPROC instead of CFQUERY tags, and can reuse code
very easily. The beauty of this was that we created the packages in the main
schema, and created public synonyms for them. Then, for the other users/schema
names, we only granted the EXECUTE privilege on the package or packages to
those user names that needed by that user. So, in the CFSTOREDPROC tag, the DSN
would be the same for all procedure calls, but you could use a "dynamic" user
ID and password, and that schema would be able to execute the procedure called
because it has been granted the execute privilege, without having to grant any
other object privileges on the "main" schema. (Just make sure that your package
has the appropriate full privileges on your main schema.)

This is a good way to restrict access to your database to only those functions
and procedures that you wish to grant access to, by means of making them
available in the package. By not allowing any other SQL from ColdFusion, you
can also divide up your development into presentation and application sections,
so that your database experts can concentrate their efforts there, and your
ColdFusion specialists can focus on the interface, etc. Develop an API and
specs, so that GUI and database developers stay on the same page. It may seem
to be a lot of overhead and overkill for some apps, but it works great if you
got lots of developers, especially if you want to keep your GUI coders out of
the database (DBAs really like this).

Phil