title: Oracle: Change Schema based on role
body: <p>There is <a href="https://stackoverflow.com/questions/283589/oracle-setting-per-user-default-scheme-not-altering-a-session">this post</a> that describes setting up a trigger to change the schema on login. Is there a way to check the role of the user to see if it matches a specific role instead of the username itself?</p>
  
  <p>I tried to do a subquery to user_role_privs in the when clause, but it doesn't allow that. Ideas?</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>This is what I'm using per Yahia's suggested solution. It does not seem to be working though. When I login with a user with the role, it still does not recognize the table without the schema name before it.</p>
  
  <pre><code>CREATE OR REPLACE TRIGGER db_logon
  AFTER logon ON DATABASE
  DECLARE
      v_count NUMBER;
  BEGIN
      SELECT COUNT(*) INTO v_count FROM user_role_privs WHERE granted_role = 'USER_ROLE' and username &lt;&gt; 'DEFAULT_SCHEMA';
      IF v_count &gt; 0 THEN
          execute immediate 'ALTER SESSION SET CURRENT_SCHEMA = DEFAULT_SCHEMA';
      END IF;
  END;
  </code></pre>