Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I created a few new users in Oracle. However, when running sqlplus, they all need to fully qualified the table names in query. What's the best way to set a default schema for these new users?

share|improve this question
    
You could use ALTER SESSION SET CURRENT_SCHEMA="some_schema"; –  NullUserException Oct 29 '12 at 22:08
add comment

migrated from serverfault.com Oct 30 '12 at 6:27

This question came from our site for professional system and network administrators.

2 Answers

I wouldnt think there is a way to set one. The user is the schema. AFAIK you can only set default tablespace.

share|improve this answer
add comment

There is nothing like PostgreSQL's set search_path in Oracle.

The closest thing I can think of would be a logon trigger for the user that run's an ALTER SESSION SET CURRENT_SCHEMA ...

CREATE OR REPLACE TRIGGER LOGON_TRG 
  AFTER LOGON ON SCHEMA
BEGIN
     EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA = foobar';
EXCEPTION 
  when others 
    then null; -- prevent a login failure due to an exception
END;
/  

If the list of users isn't too long, you can create a database logon trigger so you don't have to create that trigger for each user:

CREATE OR REPLACE TRIGGER LOGON_TRG 
  AFTER LOGON ON DATABASE
BEGIN
    if (user in ('TOM', 'DICK', 'HARRY')) then
      EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA = foobar';
    end if;
exception 
  when others 
    then null; -- prevent a login failure due to an exception
END logon_trg;
/  

Of course the list of users where you want to change the default schema, can also be taken from a table. In that case you only need to insert or delete rows from there in order to "activate" this feature (rather than re-creating the trigger each time).

Another option would be to create synonyms each time you create user that point to the real tables. You could automate that using a stored procedure that loops through all tables in one schema and creates the synonyms for them in the other schema.

Unless all your Oracle users work on the same tables I would strongly advise against using public synonyms which you would have to create only once - they can cause a lot of trouble if different application users exist in your installation.

Edit:

Following Alex's suggestion, here is a logon trigger that checks the role rather than a username:

CREATE OR REPLACE TRIGGER LOGON_TRG
  AFTER LOGON ON DATABASE
declare
  has_role boolean;
BEGIN

    has_role := dbms_session.is_role_enabled('FOOBAR_ROLE');

    if (has_role) then
      EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA = foobar';
    end if;
exception 
   when others 
      then null; -- prevent a login failure due to an exception    
END logon_trg;
/
share|improve this answer
    
You can make the database logon trigger role-based as well; though I guess that is technically taken from a table at some level. –  Alex Poole Oct 30 '12 at 8:09
    
@AlexPoole: do you mean to check for a specific role of the user instead of the user's name? That's a nice idea as well. –  a_horse_with_no_name Oct 30 '12 at 8:19
    
Yes - e.g. with dbms_session.is_role_enabled. If applications have different roles defined, this makes it easy to change the 'default' schema for each within the same trigger, without having to maintain a separate look-up mechanism which might have to span the applications. I prefer this over synonyms, especially public ones. –  Alex Poole Oct 30 '12 at 9:22
    
@Alex: I have added an example for that. Thanks for the suggestion. I share your dislike for synonyms - especially public ones. –  a_horse_with_no_name Oct 30 '12 at 9:49
    
Great idea but since when others then null; is a catch-all, it will complicate troubleshooting since it makes any error invisible. Maybe remove the exception handling entirely or log the error on the server in an AUTONOMOUS transaction and then re-RAISE it? –  George3 Oct 30 '12 at 15:59
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.