Changing User's Default Schema In Oracle Database

Last week I got a query about modifying a user's default schema.

My co-worker is sustaining a normal database request for paid job which is configured to use the user/schema that was brought ahead for its database. Many queries were typed for this request for paid job that use that schema holder, but my co-worker would like to run those queries with his own account alternatively - either because he doesn't like to log in as schema holder, or can't.

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

Best Practices

First of all, I'm happy he's not basically employing the schema holder account. The schema holder has practically unlimited privileges, and the more population or requests for paid job that use that account, the bigger the risk of mistakenly establishing a very large mistake. In item, I have a favourite even configuring the requests for paid job to use their own account, as an alternative the schema owner.

Secondly, this is scarcely a new query, in item it was possibly described best by Tim Hall at Oracle Base years ago:

"Allowing requests for paid job to make direct bindings to the schema holder is a horrid thought because it renders those requests for paid job far (too) more privileges, which can without difficulty effect in severe impairment to your written knowledge and the item themselves. Instead, it is better to delineate request for paid job users and give those users the needed privileges on the schema holders objects."

Alter Session

When a customer connects, it connects to its own schema, the one that was brought ahead when the customer was created. It can surely change to another, employing amend session. Dan Morgan has a source on altering sessions, or you review your Oracle documentation.

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

Use Views

If we're not chatting about many of desks, another pick is to bring ahead scenery in your own schema of the matching label as the tables. As I've typed before, this is just one of the more practical intents of views.

You can impede entry to, and in addition embrace only those posts in the scenery as you need. The only ploy is that you may want to renew those scenery if the implicit desks ever change.

Synonyms

And over, if you're only picking from a small number desks, your other pick is to use synonyms, which in a sense dictates which schema the assertions are toiling against.

You'll have to perform this on a table-by-table foundation, and the very large drawback is that it could baffle things if you deal with other schemas with the matching table names. Oracle will use a restricted object first, then view for any confidential synonyms before employing the public one. Here's Dan Morgan's source on synonyms.

My Recommendation

As for me, my highly appraised pick is to amend assembly at the apex of my script. All these other picks are things that are obscured from the customer, and I've perpetually disfavoured it when my database is set up to perform things in the setting of which I might not be aware. If I pick from a table I don't have, I'd like to recognise that, as an alternative have it silently snatch it from somewhere other instead.

This is an aged obstacle, so there may be other results, too. If you've got one, satisfy move out them in my observations, along with any views you have on these approaches.