Trigger de Logon para Alter Session
(com correção … pois não funcionou para Schema!)

Como funciona a nossa aplicação: o usuário de banco da aplicação não é o Owner, utilizamos roles com privilégios necessários, com isso dentro da aplicação é obrigatório usar SCHEMA.TABELA, SCHEMA.PACKAGE …;

Para testar:
SQL> conn schema_owner;
SQL> Create table tabela_teste(campo1 varchar2(10), dt_registro date);
SQL> conn schema1;
SQL> Select * from schema_owner.tabela_teste;
SQL> conn schema2;
SQL> Select * from schema_owner.tabela_teste;

Problema:
Estavamos precisando fazer uma alteração de schema no nosso projeto, pois essa configuração pode variar em produção, em homologação e em testes, surgiu a idéia de usar Trigger de Logon. Com isso todo login do usuário (schema1 e schema2) no SEU SCHEMA a sessão passaria a ser do SCHEMA Owner (schema_owner). Assim, não precisamos fazer nenhuma alteração de configuração do Oracle, na aplicação, nas configurações da aplicação, simplesmente criamos a trigger.

Abaixo como foi realizado:

SQL> conn system;
–Criamos a Trigger para os dois schemas que precisam acessar o Database owner:

Create Or Replace Trigger Alter_Session
After Logon On Database

Declare

Username Varchar2(30);
Cmmd1 Varchar2(64);
Cmmd2 Varchar2(64);

Begin

Cmmd1 := ‘ALTER SESSION SET Current_Schema=SCHEMA_OWNER_1’;
Cmmd2 := ‘ALTER SESSION SET Current_Schema=SCHEMA_OWNER_3’;
Username := User;

If (Username = ‘SCHEMA1’ Or Username = ‘SCHEMA2’) Then
Execute Immediate Cmmd1;
Elsif Username = ‘SCHEMA3’ Then
Execute Immediate Cmmd2;
End If;

Exception
When Others Then
Null;
End;

Para testar:
Conectar como system e dar permissão:
SQL> grant select on schema_owner.tabela_teste to schema1;
SQL> grant select on schema_owner.tabela_teste to schema2;

Logar com os usuarios acima (schema1 e schema2);
SQL> selec * from tabela_teste;

Ainda existe uma outra opção que é para o Schemae não para o Database, muda somente o tipo do Logon, só que está não consegui fazer funcionar! Fica como aprendizado.

(Após perceber o erro:)
Obs.: Não vou apagar o post, pois vou analisar para ver se resolvo, não funcionou para o Schema. Somente funcionou porque criei uma de Database que passou despercebida.
Então vou realizar novos testes, mas a principio o alter session somente funciona com logon em Database e não Schema.

Att,
capin