Oracle

My colleague is supporting a typical database application which is configured to use the user/schema that was created for its database. Many queries were written for this application that use that schema owner, but my colleague would like to run those queries with his own account instead - either because he doesn't want to log in as schema owner, or can't.

To make the queries work for his account, he'd have to go through every one and add the schema name. That's tedious! What he'd like to do is change the default schema for his user account. What are his options?

Best Practices

First of all, I'm glad he's not simply using the schema owner account. The schema owner has virtually unlimited privileges, and the more people or applications that use that account, the greater the risk of accidentally making a big mistake. In fact, I prefer even configuring the applications to use their own account, rather than the schema owner.

Secondly, this is hardly a new question, in fact it was probably explained best by Tim Hall at Oracle Base years ago:

"Allowing applications to make direct connections to the schema owner is a bad idea because it gives those applications far (too) many privileges, which can easily result in damage to your data and the objects themselves. Instead, it is better to define application users and grant those users the necessary privileges on the schema owners objects."

Alter Session

When a user connects, it connects to its own schema, the one that was created when the user was created. It can certainly change to another, using alter session. Dan Morgan has a reference on altering sessions, or you check your Oracle documentation.

You can do this in at least three places:
1. When you first connect, either manually or as the first line of your SQL script.
2. As a log-in trigger (as Tim demonstrates)
3. In your glogin.sql file, which executes when you first log in.

Use Views

If we're not talking about a lot of tables, another option is to create views in your own schema of the same name as the tables. As I've written before, this is just one of the many useful purposes of views.

You can restrict access, and also include only those columns in the view as you need. The only trick is that you may need to update those views if the underlying tables ever change.

Synonyms

And again, if you're only selecting from a few tables, your other option is to use synonyms, which in a sense dictates which schema the statements are working against.

You'll have to do this on a table-by-table basis, and the big drawback is that it could confuse things if you deal with other schemas with the same table names. Oracle will use a local object first, then look for any private synonyms before using the public one. Here's Dan Morgan's reference on synonyms.

My Recommendation

As for me, my favourite option is to alter session at the top of my script. All these other options are things that are hidden from the user, and I've always disliked it when my database is set up to do things in the background of which I might not be aware. If I select from a table I don't have, I'd like to know that, rather than have it silently grab it from somewhere else instead.

This is an old problem, so there may be other solutions, too. If you've got one, please leave them in my comments, along with any opinions you have on these approaches.

Also, one other special request. Please scroll down to my stat counter, and if you're my one millionth reader (or very close to it), please leave me a comment! Thanks.