14

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?

20

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
1

After starting SQLPlus, there is a glogin.sql, and a login.sql script that is run.
You can put:

ALTER SESSION SET CURRENT_SCHEMA=YOUR_SCHEMA;

in either, and they will be run.

The login.sql needs to be in a directory set by an environment variable: SQLPATH, and the glogin.sql is in the same directory as sqlplus.exe is installed into.
I couldn't get the login.sql part working, but the glogin.sql script executed fine.

-1

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