11

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?

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

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

18

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;
/
  • 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
  • @George3: but if the exception is actually thrown, the user cannot log in - I just wanted to prevent someone being not able to log in just because of a stupid mistaked in the trigger. But anyone is free to adapt this to whatever he/she wants. – a_horse_with_no_name Oct 30 '12 at 20:40
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy